April 3, 2014 at 12:13 am
We have scenario where we want to load different types of files(csv, xls, txt) into one table using SSIS.
How can we implement it dynamically?
The challenge is each file has different layout, but we have mapping available for each file. (apporx # of file 100+)
Abhijit - http://abhijitmore.wordpress.com
April 3, 2014 at 12:20 am
The book Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution describes such a scenario.
Basically you do everything in a .NET script task.
You check the file type, choose the corresponding OLE DB adaptor, read the data into a file table and use the SQLBulkCopy class to bulk load it into the SQL database (which is feasible since you already have the mapping).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2014 at 1:32 am
that pretty awesome Koen, but do you have any sample for reference.
Abhijit - http://abhijitmore.wordpress.com
April 3, 2014 at 1:36 am
Abhijit More (4/3/2014)
that pretty awesome Koen, but do you have any sample for reference.
No, sorry.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2014 at 3:57 am
We are saving mapping in below format. The other approach is saving it in vertical format.
File NameCol1Col2Col3Col4
File 1.xlsField1Field2Field3Field4
File 2.csvField1Field3Field2Field4
File 3.xlsField4Field3Field2Field1
File 4.tabField2Field4Field3Field1
The only constraint we faced here is using mapping how to achieve it through SSIS?
Abhijit - http://abhijitmore.wordpress.com
April 3, 2014 at 3:59 am
Abhijit More (4/3/2014)
We are saving mapping in below format. The other approach is saving it in vertical format.File NameCol1Col2Col3Col4
File 1.xlsField1Field2Field3Field4
File 2.csvField1Field3Field2Field4
File 3.xlsField4Field3Field2Field1
File 4.tabField2Field4Field3Field1
The only constraint we faced here is using mapping how to achieve it through SSIS?
To use this natively in SSIS you'd have to generate your SSIS packages (either programatically or by BIML).
You could use the mapping if you use .NET to transfer the data, using the SQLBulkCopy class.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2014 at 4:19 am
What datatype are you using for 'File Name'?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 3, 2014 at 4:32 am
FileName VARCHAR(255)
Abhijit - http://abhijitmore.wordpress.com
April 3, 2014 at 5:45 am
Are you using FILESTREAM?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 3, 2014 at 9:50 am
free_mascot (4/3/2014)
Are you using FILESTREAM?
FILESTREAM is for storing BLOB data, such as images or PDFs, not for storing the data in individual columns.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 15, 2014 at 9:57 am
Why not use a staging area to load 4 tables and later use a union to create one table
Raunak J
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply