September 21, 2012 at 12:15 pm
Hi Experts,
A manager asked me this question in an interview:
You have a flat file as input to SSIS package.
This flat file contains data for many customers. And it also contains a column called 'customer_city' displaying the residing city of the customer.
And the output must be a separate flat file (containing details of customers) per city.
(that is, dividing a flat file to multiple flat files based on city)
So, how can this be achieved ?
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
September 21, 2012 at 12:30 pm
1. Load the full file to a staging table.
2. Then use a SQL task to populate a recordset variable using a "SELECT DISTINCT CITY_NAME FROM StageTable".
3. Shred the variable in a For each loop and put the city name in a variable.
4. Inside the For each Loop use a SQL statement with the variable as a parameter in the where clause 5. Use the city variable as an expression on the file name for the output file.
That's pretty standard SSIS work. There probably is some way to not use a staging table too, just not as sure on that.
MWise
September 21, 2012 at 12:36 pm
I followed the exact same steps to achieve the required output.
But i was trying to do the same thing as you mentioned of achieving it 'without using a staging table'.
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
September 21, 2012 at 1:53 pm
I would have more questions. Here are a couple options based on the answers to the questions.
- Can the entire file fit comfortably in memory? If so then in Data Flow read with Flat File Source, aplpy Sort Transformation on customer city and store in Recordset Destination. Then in Control Flow use Foreach Loop to iterate over recordset in variable and write to files based on city by having an output Flat File Connection Manager take its settings using an expression on ConnectionString that uses the value from Foreach loop variable set by the customer_city column in the resordset.
- Do you know the list of cities? If so then a simple Conditional Split would be fine with static number of output streams and a corresponding set of Flat File Destinations.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply