Loading data into different text files from a single table

Question

Loading data into different text files from a single table

BIsweet

Old Hand

Points: 343
More actions
August 24, 2009 at 5:01 am

#137521

HI, I have a table in sql server 2005 which has a flag column.
Now, i need to create a SSIS package when needs to load
the data from this table into MULTIPLE Text files
based on the flag numbers in my source table.
I have attached my package which is loading the data from
my table to a single text file along with my source schema. Can any body please modify
it so that the data will be loaded into multiple text files ?
Many Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

Instead of doing it for you, I'll walk you through it so you'll be able to do it yourself next time 🙂

The component you want to use is the Conditional Split transformation. Within your data flow, you can branch out the rows to multiple output based on multiple criteria. In your case, you'll split this on the flag column, sending the output for each flag to the appropriate output text file. You'll need to create one flat file destination for each output file.

Here's a good blog by Devin Knight that gives an overview of the Conditional Split:

http://www.sqlservercentral.com/blogs/dknight/archive/2009/07/13/better-know-a-ssis-transform-conditional-split.aspx

Feel free to post again if you run into problems.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

BIsweet Old Hand Points: 343 More actions · Answer 2

Thank You Very Much for the suggestion Tim, but is it not possible to create the package without defining multiple flat file destinations as I have 28 different flags.

I was just wondering if i could do that in a more simpler way, as i have developed a package last month which creates multiple destination tables in sql server from multiple text files with same schema, where my package has only one final OLEDB destination...

Can you please help me on this ?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Does each of the text files have the same schema, but just differ by name?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Tim Mitchell SSCoach Points: 15681 More actions · Answer 4

You could use a ForEach loop, and create an expression to dynamically set the filename for each iteration of the loop. Set it up using the For Each Item as the control set, and use the flag as part of the filename to make it distinct.

Let me know if you need further guidance setting this up.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

🙂 exactly where I was going - hence my question about schemas ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BIsweet Old Hand Points: 343 More actions · Answer 6

HI guyz, can you please do those changes to my package which

i have attached, if possible ?

Thanks

P Jones SSChampion Points: 12361 More actions · Answer 7

HI guyz, can you please do those changes to my package which
i have attached, if possible ?

Don't be so lazy. These people aren't paid to do your job. They've given you the pointers so now it's up to you.

You can also use google and books online for more information on their suggestions but you need to learn how to do it yourself.

BIsweet Old Hand Points: 343 More actions · Answer 8

Ok. That is fine SSC. I will try on my own first..

Michael Ross-468051 Say Hey Kid Points: 689 More actions · Answer 9

Would the for each solution mean 28 different queries against the table? Is this a problem?

Tim Mitchell SSCoach Points: 15681 More actions · Answer 10

Yes, and It Depends 🙂

Using a ForEach loop with a parameterized SQL query would require multiple trips to the database. It depends on the size of the data and the number of trips to determine if it's a problem. In this case, if the 28 distinct queries each do not consume a lot of server or network resources, it may be a more efficient solution to use the loop rather than statically-defined outputs and a single query with a conditional split.

The bottom line is that you'll pay one way or the other, either at design time or run time.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Michael Ross-468051 Say Hey Kid Points: 689 More actions · Answer 11

Tim Mitchell (8/26/2009)
Yes, and It Depends 🙂

Sorry, my question was more directed at the original poster. If we're talking about a large table, then we invariably could be in a situation where rows are added while the package is running. Would this be a problem? I don't know. It's a neat problem, and I was hoping to get more information on the ask.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 12

You're right, this is an interesting scenario to think about. After reading the OP's description and subsequent clarification, I've started writing an article to address a situation such as this.

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

BIsweet Old Hand Points: 343 More actions · Answer 13

Well, i mean there are 28 different flags in my source table..i dont mind how many times the package hits that table..

BIsweet Old Hand Points: 343 More actions · Answer 14

BIsweet

Old Hand

Points: 343

August 26, 2009 at 3:29 pm

#1045201

HI SSC, that should not be a problem...