Understanding code created by SSIS import/export Wizard

fcb


I built a packaage in SSIS with the import/export utility. It created a Package.dtsx and Package1.dtsx. Both of these files seem to be XML files. I want to understand how these files work. For example, in the package I built I had about 80 tables exporting and importing data. Some of them I want to allow the identiy insert and delete the rows first. Others I want to append the data. How can I find the code or settings that does this Or where can I find the options on the gui interface to change these settings. When I search the code I can't even find a some of the tables that are being transferred.

Jamie Thomson

You'll need to open the package in Business Intelligence Dev Studio.

-Jamie

fcb

I actually built the package in Business Intelligence Development Studio through the Project - SSIS Import/Export Wizard. The resulting 2 packages seem to have 2 xml files behind the gui code when I use the view code on the .dtsx files. These files though don't contain any information on the tables that I am exporting though. I'm trying to find out where I can find more details on the format of these files and where I can change the settings such as allow identify insert and delete rows or append data.

Jamie Thomson

Why can you not change these things in SSIS Designer (i.e. BIDS)

-Jamie

fcb

I can't seem to find these options in any of the designer areas otherwise I would have done that. I assume the logic is in the transfer tasks or somewhere in the foreach loop but there aren't setting for these specific options. I can't tell where it's getting its list of tables to transfer for the export/import either.From what I can tell the settings for deleting or appending rows during the import and allowing identity insert are stored somewhere else.

Michael Entin SSIS

The first package is created when you create a new Project. So it is probably empty and does not contain anything. You may probably delete it.

The second package should contain a data flow task that contains most of the package logic. Don't try to edit XML - it is somewhat useful for experienced user or for advanced troubleshooting, but most of the editing should be done in the GUI designer.

Bob Bojanic

It would be easier for you to set these options in the wizard. On the page where you selected tables to transfer, click "Edit..." for the ones you want to change the options. You will see appropriate radio-buttons/checkboxes, for the options you mantioned, in the window launched by the "Edit..." button.

HTH.

fcb

Thanks. I actually know you can set these in the wizard. Unfortunately I have 80 tables I'm trying to transfer and I would like to be smarter than clicking on the edit button 80 times for each table. If I could actually get to the code I assume I could do a quick find and replace or at least know what is going on.

Or if I could actually find a setting to have the wizard default to the settings I want too. That would work as well even though I'd rather understand what the wizard actually created.

Bob Bojanic

In the SP1 version of the wizard you will be able to bulk-edit these options without multiple clicks on the Edit button.

The package generated by the wizard is kind of complicated (if "Optimize for many tables" is specified). It generates and executes a small packages that transfer single tables. There is an XML file that feeds this package generator, but it would not be easy to modify that one because it contains actual drop or truncate statements.

For 80 tables you might try to uncheck the "Optimize for many tables" checkbox. That will create the package with 80 parallel data flows. It will make the things more clear, but you would still need to set you drop table or truncate table statements as appropriate in the Execute SQL Tasks before the Data Flow.

fcb

Thanks. This was actually very helpful. At least once I turn off the"optimize for many tables" I can see something. When that option is turned on it seems that the settings are somehow stored in a temporary XML file called tableSCHEMA.xml that I couldn't access.

Deleting the rows or truncating the tables I guess does require another step which is more painful.

At least with the identity setting this can be controlled by code from what I can tell. It appears you can just edit this line for each table.

<property id="5006" name="FastLoadKeepIdentity" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">true</property>

If you change the true to false then this solves this problem for what I was looking to do.

I'll look forward to SP1 too. Is there a date set for that release yet

Bob Bojanic

Yes, you found the right property for the Keep Identity setting.

I do not know the official dates for SP1, but you should not have to wait too long.

Mike C

How do you go back and edit a package created by Import and Export Wizard assuming I'm in the Designer. How do I find the SQL Script that was generated so I can edit it

Jamie Thomson

Mike C wrote:

How do you go back and edit a package created by Import and Export Wizard assuming I'm in the Designer. How do I find the SQL Script that was generated so I can edit it

There's an option at the end of the wizard to save as a package. Just do that and edit it using BIDS.

-Jamie

Mike C

Could someboday please offer some advice how I can edit the package that the import export wizard creates.

Specifically how to add or remove a table that was not included originally while running the wizard

It looked like it was possible to edit TableSchema.xml but that is unavialable.

It SSIS seems like a great tool, and I wish I had more time to learn how to use it.. But at the moment I just need to be able to get a population of tables copied from production to test, those tables may change, or they may throw vaildation errors which I need to fix but I dont time to select the 200 some tables we need each time I run a test copy, as Import Export wizard provides no back button after an export attempt.

If it cant be done please let me know that too, so I don't waste any more time looking for a solution thats not there. If I have to do a database copy or create a BCP script then I need to get started on it right away.

Thank you

Bob Bojanic

Hi Mike,

unfortunately there is no way to restart the wizard from the package it produces.

You only have options of manually tweaking the produced package or regenerating it again. By looking at the package it should be relatively simple to add/drop a few tables, if it is a small number.

Thanks.