SSIS – Starter kit for the uninitiated Data Flow Task

Step

Screenshot

Double Click and open the DFT we had added in the previous post.

Drag and Drop an XML Source , Sort , Data Conversion and SQL Server Destination into the Data Flow Tab.

Configure the XML Source as shown in the adjacent screen.

We already have the filepath for the XML file coming to use from the foreachfie enumerator.

The path is in the variable “xmlpath”.

XML files require an XSD, in most cases you can use the generate XSD button or there are a number of websites where you can copy past the XML and the site will generate the XSD for you. Save the XSD into a file and then click Browse and Navigate to the File.

Next Click Columns

After parsing the XML this windows will display a list of tables available within the XML. Choose the appropriate table and verify the column lists.

Press OK

Next Double Click the sort operator.

In the results for this XML we have duplicate values for each airport. So select all the columns in the top “Available Input Columns” screen.

Then check the box “Remove rows with Duplicate sort values”.

Press OK.

Next Double click and Open the Data Conversion Task. Select the columns CountryCode, GMTOffset, RunwayLengthFeet, RunwayElevationFeet

And convert them into appropriate database data types.

This step might not be required if your XSD already identifies the correct datatypes already.

Common issues that you might encounter are ASCII to UNICODE Conversion

DECIMAL to INT Conversion etc.

PRESS OK

Next Double click the SQL Server Destination.

You should already have a connection manager to the database server defined from the previous posts if not click New and define one.

To create the table the first time Click the New button next to the empty Drop Down list.

You will see a popup with the table create script. Rename the table to Aiports

Press OK

Click Mappings on the Left hand side and verify that the input columns are mapped to the correct columns in the destination table.

Pay close attention to datatypes else you will get an error when running the package and will need to make changes to the Data Conversion task to rectify the data type mismatch.

Keep in mind that SSIS using .Net datatypes by default and requires explicit conversion for certain SQL datatypes.

Press OK