Using SSIS to truncate a table?

[FlaBusInt]

I have an SSIS package that I use to perform incremental inserts into a table. I want to modify the package so that it will do a historical load (i.e., will truncate the table and re-load the historical data). Is there a way to truncate the table within the Data Flow task just before I write to the destination?

I'm using a SQL Server destination.

Thanks!

 

[katbear]

just use an "execute sql task" to run your delete stmt before the data flow component

 

[FlaBusInt]

I wanted to do it within the dataflow. That way, if there is an error in the dataflow, the table will not be truncated.

 

[katbear]

Just set your dataflow task precedence constraints.

If success, then truncate the table --> execute sql task

If failure, do not truncate (or do something else).

Right-click on the arrow to set the precedence constraints to success or failure.

However, you will have to test your specific failure condition(s) to see if it actually triggers the failure in the dataflow task.

 

[FlaBusInt]

Thanks, katbear. I fear I'm either mis-understanding your answer or I am not being clear in what I want to do.

I have a Data Flow task. In the step just before my final write to the OLE DB Destination within the Data Flow, I want to truncate the table that I am writing to. I've tried playing with the OLE DB Command transform in the Data Flow, but it does not seem to be designed for this type of command.

I have been able to truncate the table using the Execute SQL Task in the Control Flow, but I really want to do it in the Data Flow.

 

[katbear]

If you really want to do this, and there is no data flow object that does this, I suggest you use a "script component" on the data flow page (insert where needed) to connect to your database and issue the truncate command from the script.

 

[FlaBusInt]

Thanks - I was hoping there was an easier way, but I guess I have to bite the bullet.

 

[katbear]

Yes, I've had to "bite the bullet" in several ssis packages as well

Just try parsing a flat file with varying numbers of columns and you'll know what I mean

 

[MDXer]

The OLE DB Command Object is Designed to perform the comand for each row in the dataflow that goes through that transform. This means that if you truncate here you will Truncate out every record prior to the next record being inserted.

Also a Note on the OLEDB Command object. Use it sparingly as again it executes for every record that passes through it. Conditional splits can be used to ensure that it executes only on needed records.

You options are

1) As Katbear has stated truncate the table in the control flow prior to your data flow taks.
2) Run your dataflow to a Staging area be it a raw file or stage table. After you have accomplished this in your current data flow you would then use the Execute SQL task to truncate your table then a second data flow to extract the records from your stage area and insert them into the destination table.

your Control Flow would look like this:

DF 1 ---On Success-->Execute SQL ---On Success---> DF2


Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck