Ssis package to import data from flat file

Clicking the Browse button, Excel file can be connected with the task like shown in the image below. Otherwise, proper server name must be typed. If the server name is correct, the user will be able to choose one of the databases from the server, where the data from the Excel worksheet will be imported, like shown in the image below. When the connections with the source and the destination is created, the Data Flow Tasks can be defined and configured in the Data Flow window.

Selecting the Excel connection from the Connection manager list allows user to choose the Name of the Excel sheet from the list, like shown in the image below. In the Column tab , the user can choose what columns from the excel file will be imported by checking the boxes for desired columns, and rename output columns, as shown in the image below.

In the OLE DB connection manager list, previously defined connection must be selected, in this case, connection is established with the AdventureWorks database on the local server. From the table list, desired destination table can be chose, or new table can be created clicking the New button and entering the SQL syntax. Click Close then OK. Select Delimited if you want to specify the separator, text qualifier, end of line etc.

Click OK. Even though we have not selected any file we have just entered the file name the file will get created. Now select the Code Page, Text Qualifier etc. With the Package Level option, we need to create a connection manager in every package within a project and when a connection manager is created at the Project Level then the connection is available for all packages in the project.

I prefer to use Project Level connections. Right click on Connection Managers and click on New Connection Manager and a list of connection manager types will pop up.

I choose ADO. The following screen, Configure ADO. NET Connection Manager , opens where you can configure the connection. In order to get the files from the directory, I needed the source folder path. For the purpose of setting a dynamic path, I am going to use a variable to do this. Right click in the Control Flow and select the Variables tab. I put a Try-Catch block for exception handling in the Script Task. In addition, I used an error raising event FireError in the catch block.

To ignore the file header when reading the input files, I am using variable ctr. For setting the input variables values, I used the local variables for the source directory and destination table. To prepare the query for importing the data to the destination table I used a local variable query.

In this blog series I am going to explore some of these simple tasks and how I go about implementing them. Importing data from flat text files into a data store definitely qualifies as simple task using SSIS. A database table called FileDetails is needed, which matches the data that we are going to import from the pipe delimited flat text files. The File Details table is the staging table for all records imported. Each staging table will be representative of the data that is being imported.

Being that the data being imported us user specific, the following columns are relevant; name, email, phone, etc….