There's no need for custom coding - this is all out of the box functionality with SSIS.
Your package is going to look approximately like this
Variables
Create variables to handle what we need. These will all be string data types except rsObject
- ColumnList This will contain the column list, populated via the FELC
- CurrentFileName This will be built on an expression
@[User::PathOutput] + "\\" + @[User::SchemaName] + "_" + @[User::TableName] + ".csv"
- PathOutput This is the place where we want the export files generated. I use C:\ssisdata but you will need to make this work with your environment
- rsObject This will hold the result set of the Execute SQL Task
- SchemaName This will be the schema name and is populated from the FELC
- TableName This is the table name and filled in via the FELC
Connection Manager
There are two connection managers: Source and ExportFile.
Source is an OLE DB Connection manager pointed at my source database (localhost\dev2014 msdb).
ExportFile is a flat file connection manager defined with a single column ColumnList, DT_STR data type, 8000 column width and uses a column delimiter of CRLF. The other thing to note is that I have an Expression defined on the Connection Manager (right click on the Connection Manager and select Properties). Here we'll assign ConnectionString to the variable @[User::CurrentFileName]
Execute SQL Task
The purpose of this task is generate the set of data we're using. I'm going to use the STUFF/FOR XML PATH "trick" to concatenate multiple rows into a single row. We're going to collapse all the columns into a single row which will have SchemaName, TableName and a comma delimited list of column names. Run the following query to see what data it is generating.
SELECT
S.name AS SchemaName
, T.name AS TableName
, (
SELECT
STUFF
(
(
SELECT
',' + C.name
FROM
sys.columns AS C
WHERE
C.object_id = T.object_id
ORDER BY
C.column_id
FOR XML PATH('')
)
, 1
, 1
, ''
)
) AS ColumnList
FROM
sys.schemas AS S
INNER JOIN
sys.tables AS T
ON S.schema_id = T.schema_id
ORDER BY
1,2;
You can modify the above to filter where the row count is zero or just generate all the tables and then don't send the client the files you don't want. That solution is left to you.
I specify that the Execute SQL Task returns a Full ResultSet. I push the results into our variable, @[User::rsObject].
FELC Shred Results
I used a ForEach Loop Container set to shred ADO.NET results. The source table is our @[User::rsObject]. In each loop around our data set, we assign into our SSIS variables @[User::SchemaName], @[User::TableName], @[User::ColumnList]
DFT Generate Export
This data flow task is designed to select the column list variable via an OLE DB Source, with a parameter, and route to a Flat File Destination.
The OLE DB Source uses the following query
SELECT CAST(? AS varchar(8000)) AS ColumnList
I map @[User::ColumnList] to ordinal position 0. This will allow the source query to "import" the value of the variable.
The Flat File Destination uses our ExportFile connection manager and maps the column ColumnList to the only column in the file.
Biml
Business Intelligence Markup Language, or Biml, is a XML dialect that describes an SSIS package. It makes creating SSIS package much easier since I can give you this code which creates the exact package I've described above.
How do you use it?
- Download BimlExpress
- Register it. It's free but does require an email address that can receive the license key.
- Open an SSIS project and right click on the project and Add New Biml File
- Replace the contents of BimlScript.biml with the below Biml.
- Update line 3 to change the Data Source to point to your server and Initial Catalog to match your database
- Update line 4 to point to where you'd like your CSVs generated
- Update line 26 to match the folder location (same as in step 6)
- Right click on BimlScript and it should generate a package named SO_51195870.dtsx
Code
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection ConnectionString="Data Source=localhost\dev2014;Initial Catalog=msdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;Packet Size=32767;" Name="Source" />
<FlatFileConnection Name="ExportFile" FileFormat="FFF Header File" FilePath="C:\ssisdata\Demo.csv" DelayValidation="true" />
</Connections>
<FileFormats>
<FlatFileFormat Name="FFF Header File" CodePage="1252" IsUnicode="false" >
<Columns>
<Column Name="ColumnList" DataType="AnsiString" Length="8000" Delimiter="CRLF" />
</Columns>
</FlatFileFormat>
</FileFormats>
<Packages>
<Package Name="SO_51195870" ConstraintMode="Linear">
<Connections>
<Connection ConnectionName="ExportFile">
<Expressions>
<Expression ExternalProperty="ConnectionString">@[User::CurrentFileName]</Expression>
</Expressions>
</Connection>
</Connections>
<Variables>
<Variable Name="SchemaName" DataType="String"></Variable>
<Variable Name="TableName" DataType="String"></Variable>
<Variable Name="ColumnList" DataType="String"></Variable>
<Variable Name="PathOutput" DataType="String">C:\ssisdata</Variable>
<Variable Name="CurrentFileName" DataType="String" EvaluateAsExpression="true">@[User::PathOutput] + "\\" + @[User::SchemaName] + "_" + @[User::TableName] + ".csv"</Variable>
<Variable Name="rsObject" DataType="Object" />
</Variables>
<Tasks>
<ExecuteSQL Name="SQL GetData" ConnectionName="Source" ResultSet="Full">
<Results>
<Result Name="0" VariableName="User.rsObject"/>
</Results>
<DirectInput>SELECT
S.name AS SchemaName
, T.name AS TableName
, (
SELECT
STUFF
(
(
SELECT
',' + C.name
FROM
sys.columns AS C
WHERE
C.object_id = T.object_id
ORDER BY
C.column_id
FOR XML PATH('')
)
, 1
, 1
, ''
)
) AS ColumnList
FROM
sys.schemas AS S
INNER JOIN
sys.tables AS T
ON S.schema_id = T.schema_id
ORDER BY
1,2</DirectInput>
</ExecuteSQL>
<ForEachAdoLoop Name="FELC Shred Results" SourceVariableName="User.rsObject">
<VariableMappings>
<VariableMapping Name="0" VariableName="User.SchemaName"/>
<VariableMapping Name="1" VariableName="User.TableName"/>
<VariableMapping Name="2" VariableName="User.ColumnList"/>
</VariableMappings>
<Tasks>
<Dataflow Name="DFT Generate Export" DelayValidation="true">
<Transformations>
<OleDbSource Name="OLESRC Query" ConnectionName="Source">
<DirectInput>SELECT CAST(? AS varchar(8000)) AS ColumnList</DirectInput>
<Parameters>
<Parameter Name="0" VariableName="User.ColumnList" />
</Parameters>
</OleDbSource>
<FlatFileDestination Name="DST File" ConnectionName="ExportFile"></FlatFileDestination>
</Transformations>
</Dataflow>
</Tasks>
</ForEachAdoLoop>
</Tasks>
</Package>
</Packages>
</Biml>
Why does the referenced solution fail
The referenced solution doesn't expect to export an empty recordset. It has explicit logic to test for the empty case If reader.HasRows Then ...which is also the source of the supplied error. writer.Close() in the finally block is Nothing so the Close operation cannot be invoked against it.
A possible rewrite to avoid the exception would be
Finally
connection.Close()
If writer IsNot Nothing Then
writer.Close()
End If
End Try
That said, even if you take out the has rows check, you will still get an empty file.
Fin
I sincerely recommend the approach I outlined. I use the system tables to generate the set of schema, tables, and columns. That data set is used to build out the external files. This is script free SSIS approach that solves the stated problem. The referenced VB approach does not do that and it will be a complete rewrite of that code to make it so.
