Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

loading multiple file types into one table Expand / Collapse
Author
Message
Posted Thursday, April 3, 2014 12:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 920, Visits: 702
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
Post #1557827
Posted Thursday, April 3, 2014 12:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 13,359, Visits: 10,224
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1557831
Posted Thursday, April 3, 2014 1:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 920, Visits: 702
that pretty awesome Koen, but do you have any sample for reference.

Abhijit - http://abhijitmore.wordpress.com
Post #1557852
Posted Thursday, April 3, 2014 1:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 13,359, Visits: 10,224
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1557854
Posted Thursday, April 3, 2014 3:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 920, Visits: 702
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
Post #1557886
Posted Thursday, April 3, 2014 3:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 13,359, Visits: 10,224
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1557888
Posted Thursday, April 3, 2014 4:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1557893
Posted Thursday, April 3, 2014 4:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 920, Visits: 702
FileName VARCHAR(255)

Abhijit - http://abhijitmore.wordpress.com
Post #1557897
Posted Thursday, April 3, 2014 5:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Are you using FILESTREAM?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1557918
Posted Thursday, April 3, 2014 9:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 13,359, Visits: 10,224
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558067
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse