- C# Scripts
- DWH INTERVIEW QUESTIONS
- MS Dynamics AX 2012 R2 Video Tutorial
- Project / Work Support
- SQL SERVER DBA INTERVIEW QUESTIONS
- SQL SERVER DBA Video Tutorial
- SQL Server / TSQL Tutorial
- SQL Server 2016
- SQL Server Scripts
- SSIS INTERVIEW QUESTIONS
- SSIS Video Tutorial
- SSRS INTERVIEW QUESTIONS
- SSRS Video Tutorial
- TSQL INTERVIEW QUESTIONS
- Team Foundation Server 2013 Video Tutorial
- Team Foundation Server 2015 Video Tutorial
- Windows 10
- Windows Server 2012 R2 Installation Videos
SSIS -How to Import Files (Text, Pdf, Excel, Image etc) To Sql Server Table [ How to Use Import Column In SSIS ]
First of all, let's create a table that can store these files. FileName column will be used to store the location from which the file is imported and File itself will be stored in File column whose data type is Varbinary.
We will use For-each loop container to loop through the files and Import Column Transformation to load these files into table.
CREATE TABLE dbo.ImportFiles
ID INT IDENTITY,
Create a variable of string type in SSIS Package with name "VarSourceFolder" and value = Folder Path.
Bring the For-each loop container to Control Flow Pane and configure as shown below to loop through all the files. We will be using VarSourceFolder as Directory in For-each loop.
Go to Variable Mapping and then create a new variable VarFileName to save File name with extension.
Create a variable VarSQLQuery. We will write expressions on this variable to build TSQL Statement. The statement will contain complete path to file that we will pass to Import Column.
"Select '"+ @[User::VarSourceFolder]+"\\"+ @[User::VarFileName] +"' AS FileName"
Set EvaluateAsExpression: True
Bring Data Flow Task inside For-each Loop Container. Double Click on Data Flow Task and then Bring OLE DB Source and in SQL Statement provide the VarSQLQuery
Drag Import Column Transformation and connection OLE DB Source to it. After connecting, Double Click on Import Column and go to Input Columns Tab and Choose input column (FileName)
Go to Input and Output Properties, Then Output Columns and Add a new column "File" and note down the LienageID. The LineageID for File column in our case is equal to 85.
Click on FileName under Input Columns and then under Customer Properties. Set FileDataColumnId=85(LineageID for File Column that we created above).
Bring OLE DB Destination and map input columns to destination table. FileName is going to contain File name with source path and File column will contain file data itself.
Execute SSIS Package and query the table to see if all information is loaded.
All the four files are successfully loaded into our destination table.