How to Import Files To Sql Server Table
What is SSIS?
Microsoft SSIS (SQL Server Integration Services) is an enterprise data integration, data transformation and data migration tool that is built into Microsoft’s SQL Server database. It can be used for a variety of integration-related tasks, such as analyzing and cleansing data and running extract, transform and load processes to update data warehouses.
We have different types of files such as Text, Pdf, Image, Excel etc and we want to load them into Sql Server Table. 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 Variable in SSIS
Create a variable of string type in SSIS Package with name “VarSourceFolder” and value = Folder Path.
Configure Contrl Flow Pane
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 VarSQLQuery Variable
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
Double Click on Data Flow Task
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
Import Column Transformation
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)
Input and Output Properties
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.
Set Customer Properties
Click on FileName under Input Columns and then under Customer Properties. Set FileDataColumnId=85.
Map Input Columns
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.
BEST SQL 2016 HOSTING RECOMMENDATION
ASPHostPortal.com provides its customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server. ASPHostPortal.com offers SQL 2016 hosting starts from $5. ASPHostPortal also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL 2014 Hosting, ASPHostPortal should be your best choice.