Introduction to SSIS

SQL Server Integration Services (SSIS) “is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data.”*  Packages are the term used to describe these transformation solutions.  This post will show you how to get started.

The first step in converting any data will be to add a ‘‘Data Flow Task’’

To add a ‘Data Flow Task’, simply mouse over the ‘toolbar’ section on the left side of the screen and drag and drop the ‘Data Flow Task’ onto the control flow work area.001

After you have added your ‘Data Flow Task’, double click on the icon ‘Data Flow Task’ that was created in the control flow work area to open the data flow work area.

From there, mouse over the ‘Toolbar’ section on the left side of the screen and drag and drop ‘Flat File Source’ onto the work area.002

Once you have created a ‘Flat File Source’ in the data flow work area, double click on the icon and choose ‘New’ Flat File Connection Manager. 

Once the flat file connection manager editor is open, you can configure it to properly read your flat file.  In this example, there is a simple ASCII file. 

Code page:  1252 ANSI Latin I.  Format: Delimited  Header row delimiter: {CR}{LF}003

 After you have set up the General section of the editor, click on the Columns section.

With an ASCII delimited file, everything should already be configured on this page. Here you will want to preview your data and ensure its integrity.004

Once the ‘Flat File Connection Manager’ and ‘Flat File Source’ have been configured, add an ‘OLE DB Destination’ from the data flow toolbox.005

Click ‘New’ to create a new connection to a resident SQL Native Client.

Select the Server and Database destination for to load the flat file to.  Choose ‘Test Connection’ to ensure you have properly configured your connection manager.

NOTE: If you already know the name of your server, you may want to manually type it in to the server name box to avoid the wait time associated with pulling back the full list of servers.006

Once you have properly configured your connection manager, Click the ‘New’ button next to the ‘Create Table or View” drop down.

Here you will see a simple create table statement.

Change the create table statement to reflect the correct name of the table you will be loading and also change the field names to the correct field names for that table.

007

This will bring you back to your OLE DB destination editor.  From here, configure the ‘Mappings’ for this table; to do so, click on the ‘Mappings’ section on the left side of this editor window.

 

008

Once in the ‘Mappings’ section, link up your Input columns with Destination columns by dragging and dropping the Input over to the desired output creating a link.

009

Once you have properly set up your mapping, return to your data flow work area and click Debug (or F5).010

* Microsoft

Advertisements

Tags: , , ,

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


%d bloggers like this: