Now, the load is incremental.
I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this?
Iam using 4 Oledb Destinations, 1 multicast, 1 source component to do this.
Also can it happen like a transaction? because if it deletes the existing data and couldnt load new data there will be a problem!.how to avoid this?
anils wrote:
I have a package which loads data from a flat file (csv) to 4 tables in a database.
Now, the load is incremental.I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this?
In the control flow, use an ExecuteSQL Task to call TRUNCATE (or DELETE FROM) each of the tables you want to purge, before calling the Data Flow task.
|||The load doesn't sound incremental. It sounds like you are doing "drop and replace". If you want to do incremental, you should use lookups to check the destination to see if the row already exists, and only insert the ones that don't.
If you do want to drop and replace and allow rollback if the replace fails, then you need to enable transactions. First create an Execute SQL task(s) to delete your data before the Data Flow that loads the new data. Determine or create a container that will have the same scope as the transaction you want to create. If there are no other tasks in your control flow, then the package can be the container. Otherwise you can add a Sequence Container to hold the tasks that will be part of the transaction. On the container change the TransactionOption to "Required". Make sure it remains the default "Supported" for each task. Now if the Data Flow fails for some reason, the deletes will be rolled back.
|||Thanks JayH for the reply.
"If you want to do incremental, you should use lookups to check the destination to see if the row already exists, and only insert the ones that don't"
Could You please explain in detail on how to do this?
|||
Thanks JayH for the reply. "If you want to do incremental, you should use lookups to check the destination to see if the row already exists, and only insert the ones that don't"
Could You please explain in detail on how to do this?
You can use the Lookup component to find rows that don't exist in your destination table by redirecting the row on a lookup failure. See Method #2 of this article: http://www.sqlis.com/311.aspx
If you need to detect changes and not just new rows, then it becomes trickier and you may find this helpful: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
