August 3, 2010 at 10:22 pm
Suppose "Contact" table has 10 columns and city is one of them..
I want to use SSIS to get the output in a flat File from this table but I want a new flat file for every city..
eg there will be 1 flat file for NYC, then 1 flat file for Chicago, 1 for Miami and so on..
So from the original table which has data for all the cities in one table has to be split into separate flat files for every city.
contact table
city name last name
NYC A B
NYC C D
Miami E F
miami G H
Chicago I J
chicago K L
output will have diff files for diff cities
file 1
City Name LastName
NYC A B
NYC C D
File 2
City Name LastName
Miami E F
miami G H
file 3
City Name LastName
Chicago I J
chicago K L
and so on...there can be hundreds of cities.. say 800 cities...
How can this be done in SSIS??
August 3, 2010 at 10:54 pm
This can be done fairly easily. The steps are :
- create query that returns the names of the cities (e.g. SELECT City, 'C:\' + City + '.txt' As CityFIleName from Yourtable) or if you wish, return a list of file names that include the city name ( I will use this as it is a little simpler for now)
- Use an Execute SQL task to run this query. Store the result in a SSIS object variable
- next run a "ForEach Loop Container" using the SSIS variable from the previous step
- in the definition of the loop container, assign the City and CityFileName columns to SSIS string variables (e.g. City, CityFileName) an
- in the "Variables" for the flat file connection, assign the SSIS variable CityFileName to the connectionstring property
- inside the loop container, run a dataflow task that outputs to the flat file connection the data for a single city (it would use the ssis variable City as a parameter)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply