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.
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.
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.