Has anyone tried...

I currently have a DTS package that takes a text file source and transforms the data into a table. The transformation does a lookup on a product_code column (char(8)) to transform it to the correct product_id (int) for our system.

I've recently setup a view that matches the file layout and has an instead of trigger that inserts into the target table. It does this by joining the inserted table to a translation table to get the correct product_id for the insert. Instead of a DTS package I created a proc that BULK INSERTS the file into the view.

The view approach is running over twice as fast as the DTS package. Is this approach common or has anyone else tried it. Any feedback would be appreciated.I don't think it is uncommon to see that kind of performance gain. DTS is very good at doing complex things, but it can't compete with BULK INSERT or BCP for doing simple things. The trick is to figure out which tool is best for a given job.

-PatP|||I forgot to mention the file contains 9.9 million records with 7 columns. The DTS package runs in 20 minutes vs. 8 for the view. BCP'ing the file directly into a table with no translation takes 3 minutes.|||DTS is very good at doing complex things...
-PatP

I'm still waiting for my burger and fries....

There's is NOTHING that will beat bcp load to staging table and set process t-sql to fix whatever it is you need to fix...especially if the file to be loaded is in native format...|||Try loading a Notes log file into SQL Server doing LDAP lookups for the VPN derived data. DTS can do it nicely using the API, BCP can't get there from here without something that can at least produce a file first, and nothing I've seen will do that very well.

The jobs that BCP can do, it does VERY well. I don't think that anything can be faster than BCP. The jobs that BCP can't do... it can't do.

-PatP

Handling errors when using a Lookup Task

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself), I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

Any help would be appreciated

Thanks

Darrell

Darrell,

You need to configure the error output of the lookup component to redirect the rows that fail the lookup. you can then route the error flow to insert the records.

Frank

|||

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

|||

DarrellMerryweather wrote:

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

Darrell,

I've used this technique on many occasions and trust me - its not affected by MaximumErrorCount. I've diverted millions of rows down the error output of a LOOKUP component when MaximumErrorCount=1 and the data-flow succeeds.

Are you sure there isn't another error occurring somewhere?

-Jamie

P.S. For nomenclature clarity, the toolbox items that appear inside a data-flow ar called components, not tasks!

|||

DarrellMerryweather wrote:

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself),

Why is that stupid? The objective here is to achieve a business requirement - does the specifics of how it is achieved really matter?

DarrellMerryweather wrote:

I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

I promise you this CAN be achieved. Persevere - you'll find the problem eventually

Perhaps check the ForceExecutionResult property.

-Jamie

|||

Guys

I was actually getting an error on the input of the derived field, where it was truncating the value coming in.

Apologies and thanks for the help, the package is now running sucessfully

Thanks again

D