Home > SSIS > Import Data from files -Import column Task – SSIS

Import Data from files -Import column Task – SSIS


Data may be scattered in difference format under few categories. They are Structured (database), Semi Structured (XML) and Unstructured (music, picture, video). Database system has the provision to store the data from the files.

In the next version of data warehouse, unstructured also plays a very major role. Loading data file is using application is the typical process. But, if we need to upload a set of files with the existing data flow during ETL process as a data to columns than we can use SSIS to design package with Import Column transformation.

Import Column Transformation

Import column reads data from the file name given in the column of the input dataset. It reads the file content from the given file and add the data along with the data flow as a column data for each row.

Import column transfers file content to the destination column with the data type (DT_TEXT, DT_NTEXT, or DT_IMAGE).

Read more about Import Column Transformation.

Scenario

I wish to upload text files to SQL Server. This is a very simple example that i can use to show how to implement Import Column task in SSIS.

Implementation:

Step 1: Create a table Books. This table is the source table.

CREATE TABLE [dbo].[Books](
	[FilePath] [varchar](50) NULL
) ON [PRIMARY]

GO

Step 2: Input few rows to the source table.

Step 3: Create a table BookStore. This table is the destination table.

CREATE TABLE [dbo].[BookStore](
	[Books] [text] NULL,
	[BookName] [varchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Step 4: Create three text file with random data for this example as shown in the path given in the source table. In this example I used the following.

Step 5: Create a package and add a Data flow task.

Step 6: Add a OLE DB Data source task and connect the database with books table.

Step 7: Add Import Column Transformation task and connect the data source task with it.

Step 8: Open Import Column transformation editor.

Go to Input columns and select the Filepath from the input columns.

Go to Input and Output properties and Add an output column named Book with text stream data type. Note the output Column ID for input data file column mapping.

Now,  Select FilePath column in the input columns node and enter output column id in the DataFileColumnId property.

Step 9: Add Derived Column task and conned Input column task with it. Add a derived column to get the file name from the Filepath input column.

Step 10: Add OLE DB destination and connect Derived column task with it. Now, Open the OLE DB Destination editor and map the input columns with destination table columns.

Step 11: Click OK and Execute the package.

View data in the BookStore table.

Thanks for reading.

About these ads
  1. priya
    July 13, 2011 at 3:26 pm

    I tried this same example. only the value for the file path is different. but am getting an error message when i execute this package. error message is “Import column:Error opening the file “file path ” for reading failed.the file was not found. i have given the correct file path.
    can you help me in resolving this issue.?
    thanks
    priya

  2. July 13, 2011 at 4:24 pm

    Hi Priya,

    As per the error message, I assume that the file in not present in the given location. please check the file path in the package. Also, check the file path stored in the table. String should not contain any blank space between characters.

    Thanks
    Ayyappan

  1. July 12, 2011 at 7:19 pm

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

Follow

Get every new post delivered to your Inbox.

Join 337 other followers

%d bloggers like this: