SSIS Script to populate SQL from Flat File

Question

I have been asked to migrate a DTS package to SSIS and I am having a hard time wrapping my brain around how the task is done. Currently, the DTS package grabs all the rows from the flat file, performs some logic on the data, and migrates them to the sql table using an active x transformation. I am very new to this, but I think once I get the source and destination configured and callable from the C# code I will be fine. Can someone point me at a good tutorial on this or tell me the basic steps to set this up? Thanks

Answer 1

The way SSIS works is that it is control flow centric and data is moved through memory buffers within the data flow. The high level steps that you need to take are as follows, I can't be more specific without further detail in your question:

In BIDS, create a new project, based on the SSIS template.

It will create an empty package called Package1 - rename this to something sensible by clicking on the name and typing a new one. It will prompt you to change the name in the file system as well. Accept this change.

You should see the Control Flow design surface in front of you. If you don't, double click on the package you have just renamed to open it.

From the Control Toolbox, drag over a Data Flow task onto the surface.

Double click the Data Flow atask to open the Data Flow design surface.

Drag a Flat file source onto the design surface and double click it to open the properties and choose New to create a new flat file connection. Give the connection manager a name and then browse to the location of the file in the file system.

Set the other properties as appropriate for your file and then move to the columns tab.

On the columns tab, set the data types for the file as appropriate and then OK to exit the properties screen.

Drag transformations as appropriate to the design surface. If it needs to be a c# script, then this would be a Script Transformation but SSIS has a rich set of transformations so custom code may not be required. Join each transformation with a data path by dragging the green tail of the path from the preceding transformation to the succeeding one.

Drag an OLEDB Destination to the surface and connect a green data flow path. Double click it to edit the properties.

Click New and then new again to create a connection manager and then enter your DB Server details. Click OK and then select the table in the database you want to insert the data into.

Now click the map pings tab. Columns will map automatically by name, but make any modifications required. Ok out of the properties, click the plan button to run the package in debug mode and start debugging and fix any issues