Dropping and recreating the destination table in an SSIS package

As a follow up to my previous post on how one goes about importing data from a flat file data source into a SQL table and being able to have some control over the name of target table it is worth noting that there’s no option available via the BDIS design surface (that I can see) to allow you to drop the table if it exists and create it again (if the data source is a flat file data source and the target is an ADO.NET data source, this option would appear to be available in the case of copying SQL tables).

In this case what you have to do is switch to the “Control Flow” “tab”, drag and drop an “Execute SQL Task” control flow item onto the design surface and link it to your data flow control as the “parent” or preceding control (fig. #1).

Add "Execute SQL Task" to the BDIS Design Surface

You then specify the necessary T-SQL to create the destination table, dropping the existing table if it exists first, via the Execute SQL Task control’s properties (fig. #2).

Enter T-SQL command(s) to "Execute T-SQL Statement" Properties

Advertisements

About Paul Williams

IT consultant working for Microsoft specialising in Identity Management and Directory Services.
This entry was posted in SQL Server and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s