SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


loading multiple file types into one table


loading multiple file types into one table

Author
Message
Abhijit More
Abhijit More
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1979 Visits: 767
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62452 Visits: 13298
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).


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Abhijit More
Abhijit More
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1979 Visits: 767
that pretty awesome Koen, but do you have any sample for reference.

Abhijit - http://abhijitmore.wordpress.com
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62452 Visits: 13298
Abhijit More (4/3/2014)
that pretty awesome Koen, but do you have any sample for reference.


No, sorry.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Abhijit More
Abhijit More
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1979 Visits: 767
We are saving mapping in below format. The other approach is saving it in vertical format.

File Name Col1 Col2 Col3 Col4
File 1.xls Field1 Field2 Field3 Field4
File 2.csv Field1 Field3 Field2 Field4
File 3.xls Field4 Field3 Field2 Field1
File 4.tab Field2 Field4 Field3 Field1

The only constraint we faced here is using mapping how to achieve it through SSIS?

Abhijit - http://abhijitmore.wordpress.com
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62452 Visits: 13298
Abhijit More (4/3/2014)
We are saving mapping in below format. The other approach is saving it in vertical format.

File Name Col1 Col2 Col3 Col4
File 1.xls Field1 Field2 Field3 Field4
File 2.csv Field1 Field3 Field2 Field4
File 3.xls Field4 Field3 Field2 Field1
File 4.tab Field2 Field4 Field3 Field1

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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7479 Visits: 2250
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."
Abhijit More
Abhijit More
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1979 Visits: 767
FileName VARCHAR(255)

Abhijit - http://abhijitmore.wordpress.com
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7479 Visits: 2250
Are you using FILESTREAM?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62452 Visits: 13298
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search