Need to process different file types in SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Need to process different file types in SSIS

Need to process different file types in SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

apache626

Posted Wednesday, September 26, 2012 7:14 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, November 23, 2012 7:03 AM
Points: 15, Visits: 41

I have what I think will be a simple question. I need to have SSIS loop through a folder for only xls or txt files. I then need to base the processing on what type of file it is. I am trying to think of an approach. Should I move the files to separate folder based on their type and then process? Or should I have two different foreach loops to process the file types? Any help is appreciated!

Post #1364647

anthony.green

Posted Wednesday, September 26, 2012 7:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Swings and roundabouts on that one, two folders one for xls the other for txt or two loops one for xls the other for txt will provide exactly the same methodoligy.

But then you have to look at if there is a process between them, say for example the xls is header information and the txt is the data and they need to be imported xls, txt, txt, txt, txt etc if so then two loops wont cut it as they will always insert in parallel or serial depending how you setup the precendence constraints etc.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1364654

vyas

Posted Wednesday, September 26, 2012 7:19 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:02 AM
Points: 3,131, Visits: 1,056

You better maintain two for each loop so the process will be faster

Post #1364655

apache626

Posted Wednesday, September 26, 2012 7:34 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, November 23, 2012 7:03 AM
Points: 15, Visits: 41

The file actually contains the same information. The excel file is manual entries. So the information will go into the same table.

Post #1364670

apache626

Posted Wednesday, September 26, 2012 7:35 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, November 23, 2012 7:03 AM
Points: 15, Visits: 41

What makes the 2 foreach loop faster in processing that the other way?

Post #1364672

pchirags

Posted Tuesday, October 02, 2012 3:29 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:16 AM
Points: 1,216, Visits: 234

you shoud use two for each loop to get each type of file from the same folder.
it makes the system fast, since you do not have to define the logic to get the files from the folder in single for each loop.

Post #1367309

CGSJohnson

Posted Friday, October 05, 2012 6:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 1,385, Visits: 1,086

You can keep them in the same folder. Within your foreach loop, however, inspect the file extension and have two branches off of the inspection object and do the separate processing in those branches.

Hope that this helps. Thanks.

- Chris

Post #1368977

apache626

Posted Saturday, October 06, 2012 11:43 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, November 23, 2012 7:03 AM
Points: 15, Visits: 41

Thanks Chris and everyone for your responses. I have implemented a for each loop with a dummy script task to be able to set a constraint to check for file type and branch processing based on that. Thanks for all your help.

Post #1369452

« Prev Topic | Next Topic »

Permissions