Hi All,

I am trying to make an Access Project using SQL Server 2000 DTS
functionalities.

I have to import selective columns from a table of dbase5 database into SQL
Server table. Every day, the dbase5 table will have some new rows. For the
first time, I want to import all rows (abt 50K rows) and subsequently, I want
the ActiveX Script in DTS to check for only new rows and import them only,
not the whole recordset.

I am using Transform Data Task and then using "ActiveX Script" in
Transformation options.

What code will get this done. Any assistance or pointing to right links is
highly appreciated.

Thanks.

You do not need to use an Active Script transform on the face of things
unless you really want to. Can you identify new rows? In the
SourceSQLStatement of the DataPump task your query would identify the
new rows

The Row Has a Date Entered
The Row has a Date Updated.


You could also create a Linked Server of the dBase database and query it
as though it was a SQL Server and by comparing it to your SQL Server you
could import only the rows you need.

Allan


"Vishal Sood" <Vishal Sood (AT) discussions (DOT) microsoft.com> wrote

Allan,

Hi. Thanks.

In the dbase5 database, there is no field which uniquely identifies new
rows. The only option there is to use a combination of the following 2 fields
: Date and Time. This combination of 2 fields can make a unique key and I can
try to retreive records which are newer than a particular date & time.

I do not know how to write the code to filter on this unique key in
"SourceSQLStatement of the DataPump task". Any hints are welcome.

Thanks.

--
Vishal Sood


"Allan Mitchell" wrote:

You can either import the whole dBase tabe to a staging table in SQL Server
and then use TSQL to do the manipulation or if the incoming rowcount from
dBase is not large you can use Lookups.

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277)

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Vishal" <Vishal (AT) discussions (DOT) microsoft.com> wrote

Allan,

Thanks. Great responses and at lightening speed!

Can I request you to help me out with my queries stage by stage with this
database I am triyng to make. I will write one issue at a time and if you
could tell me if it is right or not, and if not, why?

Really appreciate your directions till now, even if you are not able to
guide step by step.

If the answer is yes, here is the first question :-

The incoming new rowcount is going to a few hundred lines at a time. Hence,
I am planning to use Lookups. Have already seen the link you sent and have
got some good idea on this.
The source data has only about 12 columns, whereas I want the destination
table to have about 30 columns, which will be a combination of
1. default values
2. some blank as well
3. Lookup columns
4. One Primary Key column having a 3 digit Alpha code and then an
incremental number of 8 digits, which will be stored in a master table.

I want to know how to insert a blank column for the destination table for
which there is no column in the source and also the primary key column.

Trust the way forward in this is to use ActiveX Script Transformations only?

--
Vishal Sood

"Allan Mitchell" wrote:

Whilst you can use an Active Script transform, if the provider for your
source allows it I would use the following

SELECT .........................,'' as
MyBlankValue.............FROM...............

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Vishal" <Vishal (AT) discussions (DOT) microsoft.com> wrote