SQL Server Integration Services Data Import

SQL 2005: SQL Server Integration Services
Data Import

By: Zack Turnbow

 

Introduction

In this article, I will be introducing some of the basics of SQL Server Integration Services (SSIS). SSIS, formerly known as Data Transformation Services (DTS), is a powerful tool to import/export data from one data source to another with one end being SQL server. Since this is a broad topic, I will not get too deep with the example below. What you will find below is a simple import of a file into a SQL Server database. In later articles I will build upon the example below to include importing data, exporting data, sorting data, deriving a new column, multicasting data, using a script component, simple error handling, using the FTP task, transfer a database task, transfer login task, SSIS package deployment, and finally calling a SSIS package from C# code.

We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.

Implementation

For this series of articles you need to either enable Integration Services on SQL Developer Edition and above or install the Microsoft SQL Server 2005 Express Edition Toolkit for SQL Server 2005 Express Edition. This first article will cover the import of data from a CSV file into a database with one simple table. The scenario used will be importing credit card transactions for a third party processing company. Let’s get started. First, we will need to start a new project that will contain all of the SSIS packages. On the Visual Studio start screen, click Create New Project. Look under Business Intelligence Projects and select Integration Services Project. See the visual below for an example.

 

Click OK. Once the project loads, it starts you on the Control Flow tab working environment. If you don’t see any tools in the toolbox then you will need to add them in from Tools menu. The first and only control that is needed on the Control Flow will be a Data Flow Task, which is where most of the work is done when working with data. Just drag one into the work area.

 

Either double click the Data Flow Task or click the Data Flow tab at the top of the work area. Once in the Data Flow Task area, your toolbox should have a different set of tools. Look in the list and find the Flat File Source component and drag it into the work area.

We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!

You will notice that there is a red circle with an X in it. This indicates that there is a configuration problem with that component. The particular reason for this problem is that all source and destination components need a connection to pull/push information to/from. So the next thing to do is configure a connection for our CSV flat file.

The CSV File

For the purpose of this article, the CSV file contains the essential information for a credit card transaction. For simplicity, I have included the column names in the file and each column represents a column in the database table. To create a connection to this file, right click the area under the Connection Manager tab and select New Flat File Connection. The Flat File Connection window will appear. You will need to provide the path to where the flat file exists which can be a local folder or network server. You will need to select the type of flat file it is. For this example, the file is a delimited file. Since I have the column names on the first row, I clicked the check box indicating as such.

 

You will notice that there is a warning at the bottom of the box indicating there is still information that needs to be configured. So go ahead and click the columns tab on the left to bring up that information. When you click on this tab, the file is parsed to the format specifications on the General tab. If the format specifications are correct the data in the file should be easily read.

 

Click OK to close the File Connection Manager.

The Rest of the Package

Now that we have a valid file connection, we can configure the Flat File Source component to use this connection, so double click the Flat File Source component. The first bit of information that needs to be configured is which file connection to use. Since we only have one, select the file connection that we created.

We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.

You can click Preview to see another rendition of how the data looks after it gets parsed. Next we need to identify and select which columns we want to use in our import so click the Columns tab to view this information.

 

Since this is a simple example, all columns will be selected. At this point, if you like to keep column names the same in your package as then are in your database, you can change the name of the column here under the Output Column. Once you are satisfied clock OK. Now that we have the data inside the package we are now able to do what we need to with it before it gets imported to the database. Since we are just concerned about importing the data at this point we will go ahead and throw it right into the database. Find the component call OLE DB Destination and drag it into the work area. Again you will see the warning indicating that something needs to be configured. Since this is the destination for the data we will need another connection. Right click under the connection manager area again and select OLE DB Connection. You will get a dialog box that will be blank so go ahead and click the New button. Another dialog box will show. Select the appropriate database provider, then find the server where your database is and select it. Configure the appropriate authentication method used to access the database server. Then select the database where the data is going to be imported. You can click the Test Connection button to make sure access is granted.

 

Click OK and the first dialog box will now be filled out.

 

Now that there is a connection, the OLE DB Destination component can now be configured. Double click the component and another dialog box will come up with the information that needs to be filled out. First, select the connection that was just created. Next select the access mode that needs to be used. For the purpose of this example, table fast load is being used. Since the data has an identity in it, the check box to keep the identity will be selected.

Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!

As with Flat File Source, the data will need to be mapped to the exact column in the table that it needs to go to. So click the mappings tab. If all of the column names are the same, lines will be automatically connecting the columns in the package to the columns in the database table. Since there is one column that is not the same, just left click and drag the column from the package to the column in the table where the data needs to go.

 

Once all of the columns are matched, click OK.

Testing the Package

Now that everything is configured from beginning to end, it’s time to see it work. If there are no configuration warnings, run the project. The end result should look like the following.

What have we learned?

I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.

• How to configure connections for a SSIS package.
• How to configure a Flat File Source component.
• How to configure an OLE DB Destination component.
• How to run a package to make sure it passes before deploying the package.

Attachments

Attached is a compressed file with the above example for your testing purposes.