July 22, 2008 at 7:47 am
Here is what I currently have:
I have a Data Transform Task that moves data from a flat file into a SQL table. I have the TransactionOption set to Required, so if the import fails, it rollsback. Originally, on the "Failed" path, it would write the bad data to a text file. However, since I set the TransactionOption to Required, it rolls back writing to that file as well.
Now, what I would like to do is take the data where it is failing, and have it write out to the error file. I have been trying to do this on the OnError event for the task, but have not had much success. Does anyone have any ideas or links to information that might help me out?
Thanks!
Michael
July 22, 2008 at 7:54 am
For simple situations, you can control transactions manually.
1) Add two Connection Managers for connecting to your database. I am going to refer to them as "CMData" and "CMError"
2) On CMData, set the RetainSameConnection property to True (this will make it only connect once and retain the connection for the package duration)
3) Before your Data Flow task, add an Execute SQL task
4) On the new Execute SQL task, set the connection manager to CMData and set the SQL Command to "BEGIN TRAN" (without the quotes)
5) After the Data Flow Task, add another Execute SQL task
6) On this Execute SQL Task, set the connection manager to CMData and set the SQL Command to "COMMIT TRAN"
7) In your data flow task, use the CMData connection manager for the data flow destination
8) In your data flow task, use the CMError connection manager for your error data flow destination
This will use a SQL Server transaction rather than a DTS transaction to manage this transaction. This will get more complex if you use lookups or other components that use the CMData connection manager, but hopefully, you see what this is getting at.
July 22, 2008 at 8:31 am
Michael Earl (7/22/2008)
For simple situations, you can control transactions manually.1) Add two Connection Managers for connecting to your database. I am going to refer to them as "CMData" and "CMError"
2) On CMData, set the RetainSameConnection property to True (this will make it only connect once and retain the connection for the package duration)
3) Before your Data Flow task, add an Execute SQL task
4) On the new Execute SQL task, set the connection manager to CMData and set the SQL Command to "BEGIN TRAN" (without the quotes)
5) After the Data Flow Task, add another Execute SQL task
6) On this Execute SQL Task, set the connection manager to CMData and set the SQL Command to "COMMIT TRAN"
7) In your data flow task, use the CMData connection manager for the data flow destination
8) In your data flow task, use the CMError connection manager for your error data flow destination
This will use a SQL Server transaction rather than a DTS transaction to manage this transaction. This will get more complex if you use lookups or other components that use the CMData connection manager, but hopefully, you see what this is getting at.
I see completely where you are going with this, but, I have the problem of the connection being used for several flow tasks that are running simultaneously. What do you suggest I do, since this is the situation?
Also, how can I channel out the errored data from the data flow task into the script task (which I will use to write the data out to the error file)?
Maybe what I want to do is not possible, but it seems like it should be.
Thanks,
Michael
July 25, 2008 at 8:31 am
OK ladies and gentlemen....a new directive from above.
What I need to do is, if there is an errored line, I need to write it out to a text file, but continue processing the file. Any ideas on that?
Thanks!
Michael
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply