Sql-server – SSIS – Data Import & export wizard – existing tables

importsql server 2014ssis

For a project I'm attempting to move data from a view in source to a table in destination. And this will need to be done periodically.
The quickest way to do this seems to be a SSIS package (as this also grants me additional control for possible data transformations in a following phase.

Now, the first time this works excellent, I go through the wizard and the destination table(s) are created and the data imjected.
However, when I run the same package a 2nd time SSIS complains that the tables already exist (which is right).

The only way I see how to rectify this is to change the source queries and add an ifexists clause. This though will become a lot of work as I will need to do this for 144 destination tables.

Hence my question, is the if exists clause the only way to capture this or is there an option within the wizard/SSIS to have the package check this for me and only create the table if it doesn't exist yet?

Best Answer

The Import/Export wizard generally makes pretty basic 2-step packages. For instance, I quickly made one that migrates two tables from databaseA over to databaseB, and the Control Flow of the generated package looks as follows:

enter image description here

The CREATE TABLE statements are all performed together in the Preparation SQL Task 1 which means it should be the only place you need to make a change. In support of the advice provided by @RobertCarnegie in the comments, I also recommend putting your table creation/truncation logic within a stored procedure which will be called from the Preparation SQL Task 1 step in the package.

Basically, after this change the Execute Script Task will look as follows:

enter image description here

Taking this approach allows the Data Flow tasks to remain unchanged, and now you should be able to reuse the SSIS package without much more work.

If you're finding it annoying to create a stored procedure that controls the creation/truncation of 144 separate tables, there are various tricks you can employ. If you're not opposed to using the UI, you can multi-click items in the Object Explorer Details window and script out actions, so for instance if I want to generate out a bunch of drop/create scripts for a lot of tables, it's as easy as clicking on the table folder for a given database, multi-selecting the tables I want to drop/recreate in the Object Explorer Details window, and then right-click on them and select the option I want:

enter image description here

You will need to remove the GO statements if you want to nest this into a SP, but you can hopefully see that it's not nearly as difficult to bulk-generate scripts with this approach.

If you want to generate TRUNCATE statements (which will be easier to do), script out DROP table statements and then do a search/replace on DROP to replace it with TRUNCATE.

Related Question