Hi All,
I am struck at one point. I am trying to this operation and not able to go further.
1. I have got the dataset to a variable in the control flow.
2. I am looping through the dataset based on a coloumn.
3. Now inside my For each loop i have a dataflow task.
4. In the data flow task i am trying to build a dynamic query using the OLEDB Source and i have selected SQL Command from variable. And the variable build the Query as select * from @.othervariable.
Now my question is
Can i send the data of each of the Query resultset to an out put text file using Flat File Connection? If yes pls guide me how? I have tried to create a flat file connection but i am failing how to map the data comming from step 4 dynamically for every query, since every query gives you a different resultset with different coloumns.
Thanks in advance..
Regards,
Dev.
Metadata cannot change. Will it change with your dynamic query?|||hi phil,
yes every query produces a different result. pls. let me know the best way to handle this kind of scenario. I have total 30 different result sets and need to generate 30 different flat files based on the different out out.
Thanx in advance..
Dev
|||Dev,
If the datasets has the same structure (column names, data types); then it is possible to do it using SSIS.
I have 2 posts in my blog that explains how to loop through a a data set and how write into different Excel files. You could modified to write into different flat files if you need.
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html
|||Rafeal,
Unfortunately the datasets are not same. they are different in each case. I am not bale to figure out a best solution for this. Pls let me know if u have any ideas onthis...
thanx in advance..
dev
|||
dev15`4534345677 wrote:
Rafeal,
Unfortunately the datasets are not same. they are different in each case. I am not bale to figure out a best solution for this. Pls let me know if u have any ideas onthis...
thanx in advance..
dev
You're going to have to setup a data flow for each format.|||
I completly agree with your approch as that was my last option. Do u think this is the only one i have.....
Can't i do any thing dynamically as u can imagine doing 30 different data flow for 30 times !!!!
Thanx in advance..
Regards,
Dev
|||
I completly agree with your approch as that was my last option. Do u think this is the only one i have.....
Can't i do any thing dynamically as u can imagine doing 30 different data flow for 30 times !!!!
Thanx in advance..
Regards,
Dev
No, that is your option. SSIS needs to know the metadata up front. Sorry. How would you map the columns in the destination anyway, if you want it to be dynamic?
You might be able to write a program that programatically builds a data flow for you, but then you aren't really using SSIS at that point. 
|||
Phil,
what would be your approach if there is a scenario like this for you.....
Regards,
Dev
|||
Phil,
what would be your approach if there is a scenario like this for you.....
Regards,
Dev
Well, the only other option that comes to mind is to read everything in as one big string, and then use a conditional split to send the data to 30 different connection managers. (That's an overly-simplified explanation, but I hope you get the idea...)
The connection managers are going to have to be setup for each format. I'm not sure there's a way around that.|||
Hi All,
I would appreciate If any body has a different solution for this pls. post it here...
regds,
dev
|||
Hi All,
I would appreciate If any body has a different solution for this pls. post it here...
regds,
dev
Also please search this forum. You are not the first to desire this and you'll not be the last.
The current version is very strict on metadata rules. Perhaps SSIS isn't the best option? That is, maybe you should write views and then use another SQL utility to output the results to a file. BCP, perhaps.|||Building package programatically may be another option...more complex perhaps. BOL it a godd point ot start|||
I apologize in advance if I misinterpreted something but wouldn't a script task be an option? Theoretically you could replace your data flow task with a script task and pass in the variable containing the data flow then within the script task you could loop through the dataset columns and rows and output it to a flat file of your chosing. Again, I apologize if a script task is not an option but I do not see it being overly difficult to implement what you are asking using one. Have you looked into this option? Are you familar with VB.NET?
|||
I apologize in advance if I misinterpreted something but wouldn't a script task be an option? Theoretically you could replace your data flow task with a script task and pass in the variable containing the data flow then within the script task you could loop through the dataset columns and rows and output it to a flat file of your chosing. Again, I apologize if a script task is not an option but I do not see it being overly difficult to implement what you are asking using one. Have you looked into this option? Are you familar with VB.NET?
I don't think it's an option as you still need to know the outputs in advance to setup the script task's outputs.
