The requirement was to create a package which would enable exporting results of sql query to a flat file. The catch here was that query cant be determined beforehand.
Exporting to flat file is straightforward in SSIS using a data flow task but for this the metadata (column structure) has to be fixed. So that was not a viable option here. So I used a different approach and quickly put together a sample package for him. I’m sharing the package here for others benefit.
The package was as simple as below
The first task is a simple Execute SQL Task which will have properties set as follows
1. ConnectionType : OLEDB
Connection: Connection manager created pointing to your server and database from which data is to be exported
2 SQLSourceType: Variable
SourceVariable : This is pointed to a user variable created in the package which holds the query as the value. This can be added to configurations within package if you need to pass the value from outside.
3. Result Set: Full result set
4. Result Set tab: the resultset 0 mapped to your Object variable created in SSIS
This will make sure the object variable is populated with the results of your query passed. Once this is done next part is to get this result written to the flat file. You can create a variable to set the path of the file and also add it to the configurations. The Script task can be given as below
Public Sub Main()
‘
‘ Add your code here
‘
Dim x As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
Dim str As String = vbNullString
Dim i As Int16
x.Fill(dt, Dts.Variables(“ADOres”).Value)
i = dt.Columns.Count
For j As Int16 = 0 To i – 1
str = str & dt.Columns.Item(j).ColumnName & IIf(j = i – 1, vbCrLf, “,”)
Next
i = dt.Rows.Count
For j As Int16 = 0 To i – 1
str = str & Join(dt.Rows.Item(j).ItemArray(), “,”) & vbCrLf
Next
System.IO.File.WriteAllText(Dts.Variables(“FileName”).Value.ToString(), str)
Dts.TaskResult = ScriptResults.Success
End Sub
The code will create a datatable and fill it with the contents of the ADO recordset created in previous step using OLEDB Data Adapter. Once this is done then then it just takes two loops, first one to get column names from Recordset and second one to get the row values from the datatable. We take row values as a delimited string array using Join() function and appends to a string variable along with the column names. At the end of the looping logic string variables will have entire contents to be written to file.
An object of System.IO.File is created and WriteAllText method is invoked to write contents of string variable to the flat file. Once this is done flat file gets populated with the required data as per the given query. This is much flexible compared to data flow method as we dont need to fix the metadata here at runtime.
The sample package and config can be accessed from the below link
Package link: https://drive.google.com/file/d/0B4ZDNhljf8tQTVhTd3dvc2JWN2c/edit?usp=sharing
Config link: https://drive.google.com/file/d/0B4ZDNhljf8tQWjFoY3hUaVc5dTQ/edit?usp=sharing
Make sure you change config properties to your server database and file path values before you start running the package.
In case of any issues or should you need any more clarification on any of the above, feel free to revert with comments.
