SO definitely only could be done using a script task, but that wasn't really my favorite part, I'm the worst when it comes to that.
Anyway because I had some past experience as developing ASP pages using vb.net, so quickly came to my mind to build aDataGrid using a SQL query, then export the grid to a text file, which worked fine, The I came across the DataReader which is a neater and easier way.
So here I'm going to demonstrate how to Iterate over a list of views which has different structure to flat file using Script Task.
I'll be using the famous "AdventureWorksDW2008R2" DB for SQL 2008 and "AdventureWorks" for SQL 2005 which could be downloaded for free here, The demo is being done on a windows Vista 64BIT machine, with SQL and VS 2008 installed. but there shouldn't be any version different if you're using SQL 2005.
Alright enough talking....
For SQL server 2008 Users......
1-The first step is to identify what are the views that we be exporting.
2-Check the columns count for each of them, just to illustrate the different in structure.
3-I'll create 4 Variables, I love to always to the proper naming convention to tell the data type of the variable.
A-Variable "ObjAllViews" will hold back the result set which has all the views' Names.
B-Variable "StrDelimiter" will hold the delimiter that you want to use between columns, I love to use the Vertical Line "|" because most of our data has commas in it, you can get it by pressing (SHIFT + \ )
C-Variable "StrViewName" will hold each individual view.
D-Variable "StrFilePath" will hold the desired path to save the files.
4-Create a connection to your server, in my case it's 'localhost' as I've my testing SQL server on the same machine, and I named the connection 'Localhost', take note of naming the connection because it's mentioned later in the script task.
oh...so now you get the idea? we will use the "Execute SQL Task" to get us the list of the views that exists in that table into "ObjAllViews" variable, then we will use "Foreach Loop Container" to shred resultset from "ObjAllViews" variable to each individual view name into "StrViewName" variable .
Then inside the "Foreach Loop Container" we will place a "Script Task" that will do the trick for us.
Now let's go over each tasks and how it's configured....
1-The control flow and the connection
2-Execute SQL Task
3-Foreach Loop Container
4-Script Task , Thanks for SIVA for helping me with the counter code.
5- add This code
And The results .........
For SQL 2005 users...
We need to change the script task, also the SQL query too as follow:
1- change the SQL query to
2- and the script task as shown here
1-You're using SQL 2008 or SQL 2005.
2-Server name is "Localhost" hosted locally, otherwise, please change that.
3-You have write access to your D:\ Drive.
4-For SQL 2008 you have installed "AdventureWorksDW2008R2" DB and "AdventureWorks" for SQL 2005.
That's it...I hope my article helps someone to be more dynamic and flexible in SSIS and over come some of the Data Flow limitations.
Also I'm open to discussions and comments if you have a better way to do it.
The final package for SQL 2008 can be FOUND HERE and for SQL 2005 FROM HERE