DTS package execution time

Hi - I currently have a DTS package that takes raw data from a SQL
table and inserts records into several tables via a custom ActiveX
transformation. The package uses logic to determine which table to
insert into and then calls DTSlookups to perform the inserts. The #
of records I'm working with is small (3,000-10,000). If I perform a
"test, the rows are actually inserted, and rather quickly. If I
execute the package through normal methods, the execution takes several
hours and often times out?
Any ideas'Running a DTS package within Enterprise Manager executes it locally on
the machine where EM is running. Running it using a schedule runs it
on the server where the agent service is running. Different machines
have different resources and are under different loads, and have
different distances from the data. Look for data traveling over the
network vs remaining local.
As you describe it, both the source data and the final destination of
the data are in SQL Server. Are they on the same SQL Server? If so,
did you consider using stored procedures? Keeping all the work
withing SQL Server itself has some performance advantages.
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 10:37:26 -0700, clawdaddy@.gmail.com wrote:

>Hi - I currently have a DTS package that takes raw data from a SQL
>table and inserts records into several tables via a custom ActiveX
>transformation. The package uses logic to determine which table to
>insert into and then calls DTSlookups to perform the inserts. The #
>of records I'm working with is small (3,000-10,000). If I perform a
>"test, the rows are actually inserted, and rather quickly. If I
>execute the package through normal methods, the execution takes several
>hours and often times out?
>Any ideas'|||clawdaddy@.gmail.com wrote:
> Hi - I currently have a DTS package that takes raw data from a SQL
> table and inserts records into several tables via a custom ActiveX
> transformation. The package uses logic to determine which table to
> insert into and then calls DTSlookups to perform the inserts. The #
> of records I'm working with is small (3,000-10,000). If I perform a
> "test, the rows are actually inserted, and rather quickly. If I
> execute the package through normal methods, the execution takes several
> hours and often times out?
> Any ideas'
>
There's really not enough info to come up with a cause, but if this is a
SQL-to-SQL process (reading from SQL/writing to SQL), I'd question why
you used DTS to do this. I think you'd get better performance, not to
mention easier debugging, by doing this a series of INSERT INTO/SELECT
statements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DTS package execution time

Hi - I currently have a DTS package that takes raw data from a SQL
table and inserts records into several tables via a custom ActiveX
transformation. The package uses logic to determine which table to
insert into and then calls DTSlookups to perform the inserts. The #
of records I'm working with is small (3,000-10,000). If I perform a
"test, the rows are actually inserted, and rather quickly. If I
execute the package through normal methods, the execution takes several
hours and often times out?
Any ideas'Running a DTS package within Enterprise Manager executes it locally on
the machine where EM is running. Running it using a schedule runs it
on the server where the agent service is running. Different machines
have different resources and are under different loads, and have
different distances from the data. Look for data traveling over the
network vs remaining local.
As you describe it, both the source data and the final destination of
the data are in SQL Server. Are they on the same SQL Server? If so,
did you consider using stored procedures? Keeping all the work
withing SQL Server itself has some performance advantages.
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 10:37:26 -0700, clawdaddy@.gmail.com wrote:
>Hi - I currently have a DTS package that takes raw data from a SQL
>table and inserts records into several tables via a custom ActiveX
>transformation. The package uses logic to determine which table to
>insert into and then calls DTSlookups to perform the inserts. The #
>of records I'm working with is small (3,000-10,000). If I perform a
>"test, the rows are actually inserted, and rather quickly. If I
>execute the package through normal methods, the execution takes several
>hours and often times out?
>Any ideas'|||clawdaddy@.gmail.com wrote:
> Hi - I currently have a DTS package that takes raw data from a SQL
> table and inserts records into several tables via a custom ActiveX
> transformation. The package uses logic to determine which table to
> insert into and then calls DTSlookups to perform the inserts. The #
> of records I'm working with is small (3,000-10,000). If I perform a
> "test, the rows are actually inserted, and rather quickly. If I
> execute the package through normal methods, the execution takes several
> hours and often times out?
> Any ideas'
>
There's really not enough info to come up with a cause, but if this is a
SQL-to-SQL process (reading from SQL/writing to SQL), I'd question why
you used DTS to do this. I think you'd get better performance, not to
mention easier debugging, by doing this a series of INSERT INTO/SELECT
statements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DTS importing only new records

Hi!
How can I set up a DTS that reads a flat file database and inserts only the new records to an existing table? This DTS will be ran everyday against a flat file that accumulates records.Import data into temp table and compare with ur original table, delete duplicate records from temp table and insert records from temp table to ur original table|||Alternatively, look at the Data Driven Query Task.

1. On the Source tab, write your Query to Pull data from your source (in this case a flat file).
2. On the bindings tab, specify the destination table.
3. On the Transformation tab, a default transform will be created for you in VB Script. Keep it, but it is most likely that you will have to make some changes.
4. On the Queries tab, you will need to specify a query for inserting data and a query for updating data. If there are many columns, this can be a real pain.
5. On your lookups tab, you will need to specify a Query that will return an integer value (Such as SELECT Count(*) FROM MyTable WHERE PK = ?). Leave the ? exactly as it is written above; that is a parameter that you will pass in from the transform statement.
6. Now go back to the Transform tab; add an If...Then...Else statement around the line where it says Main = DTSTransformstat_UpdateQuery

If DTSLookups("MyLookupName").Execute(DTSSource("MyParameter")) > 0 Then
Main = DTSTransformstat_UpdateQuery
Else
Main = DTSTransformstat_InsertQuery
End If

This block basically says If there exists a record in the destination table with the same Primary Key, then run the Update Query, otherwise run the Insert Query.

7. Now go back to the Queries tab and add your INSERT and UPDATE queries. They should look something like:

INSERT:
INSERT INTO MyTable VALUES (?,?,?,?,?)

UPDATE:
UPDATE MyTable SET Value1 = ?, Value2 = ?, Value3 = ?, Value4 = ?
WHERE PK = ?

Finally, on the queries tab, make sure you align the incoming parameters with the place holders (question marks) using the mapping tool at the bottom of the window. It is, no doubt about it, a pain in the a$$.

Another alternative might be to write a single stored procedure that would accept the incoming values, test for the existence of the primary key and perform an INSERT or UPDATE as required.

Regards,

hmscott|||Hi!

Thanks for the detailed suggestion. I'm new to this stuff and I really want to know more about utilizing DTS to maintain and manipulate my tables.

Anyhow, I was able to setup 2 connections (Flat file connection that points to the flat ascii file and an SQL server connection to my destination table) and a Data Driven Query Task. There's no workflow between them, is this correct? When I execute he package, it failed and gave me an error saying: "statement could not be prepared, Line 2: incorrect syntax near ".".

Here's my transformation code:

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DTSDestination("MID") = DTSSource("Col001")
DTSDestination("IIN") = DTSSource("Col003")
DTSDestination("ITN2") = DTSSource("Col004")
DTSDestination("ITN") = DTSSource("Col005")
DTSDestination("ITN3") = DTSSource("Col006")
DTSDestination("GENDER") = DTSSource("Col007")
DTSDestination("LANG") = DTSSource("Col008")
DTSDestination("TITLE") = DTSSource("Col009")
DTSDestination("NAME") = DTSSource("Col010")
DTSDestination("ADDRESS") = DTSSource("Col011")
DTSDestination("APT") = DTSSource("Col012")
DTSDestination("CITY") = DTSSource("Col013")
DTSDestination("STREAM") = DTSSource("Col014")
DTSDestination("PROV") = DTSSource("Col015")
DTSDestination("POSTCODE") = DTSSource("Col016")
If DTSLookups("Count_ITN2").Execute (DTSSource("Col004")) > 0 Then
Else
Main = DTSTransformstat_InsertQuery
End If
End Function