Extract a Stored Procedure Resultset to CSV or Excel
Generates an SSIS package that runs a configurable stored procedure and writes the output to either a CSV file or an Excel workbook. At compile time, the script opens a 'SqlConnection' against the target server, runs 'SET FMTONLY ON; EXEC ...; SET FMTONLY OFF;' to read the procedure's result schema with 'SqlDataReader.GetSchemaTable', and uses the captured 'DataTable' to emit a 'FlatFileFormat' (for CSV) or a generated 'CREATE TABLE [Query]' DDL (for Excel) with column types mapped from SQL Server types.
At runtime the generated package runs in this order: an optional 'FileSystem' task deletes any pre-existing Excel file, a 'Script' task stamps the file name with the current date/time and replaces any 'GETDATE()' token in the procedure parameters with a literal datetime, an 'ExecuteSQL' task issues the 'CREATE TABLE' DDL into the new Excel sheet when needed, and a final 'Dataflow' calls the procedure via 'OleDbSource' (with 'VariableInput' bound to the assembled 'EXEC' statement) and writes rows to either the 'FlatFileDestination' or the 'ExcelDestination'.
Set the input parameters at the top of the script ('serverName', 'databaseName', 'storedProcedureSchema/Name', 'storedProcedureParameters', 'destinationFileType', 'fileName', 'destinationFolder', and so on). Limitations: SQL Server 2008 (R2) and 2012 are supported; on 2008 R2 every stored procedure date parameter must be 'DATETIME' (SSIS OLE DB Command bug); decimal/numeric precision cannot exceed 28; 'SMALLMONEY' and 'SMALLDATETIME' are not supported; stored procedures that use temp tables fail under 'SET FMTONLY ON', so substitute table variables or a dummy procedure with the same shape; and Excel files generated via the ACE provider have known data-type issues, so prefer Jet when possible.
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<#
/* ========================================================================= */
/* === GENERATE EXTRACTOR PACKAGE === */
/* ========================================================================= */
/* === DESCRIPTION === */
/* ===-------------------------------------------------------------------=== */
/* This Biml script will generate a SSIS package that will extract */
/* data from a stored procedure and will persist them in CSV or */
/* EXCEL. */
/* SQL Server 2008 (R2) and SQL Server 2012 are supported. */
/* ===-------------------------------------------------------------------=== */
/* === LIMITATIONS === */
/* ===-------------------------------------------------------------------=== */
/* 1. If SQL Server 2012 or greater is NOT used, every stored */
/* procedure date parameter MUST be DATETIME due to a SSIS OLEDB */
/* Command bug (*); */
/* 2. DECIMAL and NUMERIC data types precision cannot exceed 28; */
/* 3. SMALLMONEY and SMALLDATETIME data types are not supported; */
/* 4. Temporary tables in stored procedures cause the failure of the */
/* Biml script because it uses FMTONLY ON in order to retrieve */
/* the stored procedure schema infos; in that case you can */
/* compile the Biml script: */
/* a. using a dummy stored procedure that has just a SELECT */
/* that returns a dummy resultset with the same schema of */
/* the original one; */
/* b. using Table Variables instead of temporary tables if you */
/* don't have performance problems */
/* 5. Excel files generated with the ACE provider have incorrect column */
/* data types due to a SSIS bug (**). All works fine using the */
/* Jet one */
/* ========================================================================= */
// (*) Microsoft Connect bug 628743 — SSIS OLEDB Command date datatype in stored procedure SqlCommand yields operand error (Connect retired)
// (**) Microsoft Connect bug 477970 — Excel data types from SSIS (Connect retired)
/* ======================================================================= */
/* === INPUT PARAMETERS === */
/* ======================================================================= */
//
// Stored procedure's SQL Server server name/instance and database name
//
string serverName = @"localhost\denali";
string databaseName = "AdventureWorks2012";
//
// OLE DB Provider used by the OLEDB Source Component in order to execute the Stored Procedure
//
string oledbProvider = "SQLNCLI10.1";
//
// Stored Procedure details:
// - storedProcedureSchema: schema to which the Stored Procedure belongs;
// - storedProcedureName: Stored Procedure name (eg. "stp_attestati_rischio");
// - storedProcedurePrefix: Stored Procedure name's prefix, if present (eg. "stp_");
// - storedProcedureParameters: comma separated list of parameters in the order expected by the Stored Procedure;
// the token "GETDATE()" can be used if you want the package will replace it with the
// date it'll be executed (useful if you want to schedule it in a job); an example of
// provided parameters is: "GETDATE(), '00760', NULL, 3";
//
string storedProcedureSchema = "dbo";
string storedProcedureName = "uspGetWhereUsedProductID";
string storedProcedurePrefix = "usp";
string storedProcedureParameters = "819, GETDATE()";
//
// SSIS Package details:
// - packagePrefix: the package name's prefix (the full name will be composed
// by the package name's prefix and the Stored Procedure
// name without its prefix;
// - packageProtectionLevel: value of the package ProtectionLevel
// - packageConnectionManagerName: name of the package's Connection Manager that points to
// the Stored Procedure database;
//
string packagePrefix = "test_";
string packageProtectionLevel = "EncryptSensitiveWithUserKey";
string packageConnectionManagerName = "ADW";
//
// Output file details:
// - destinationFileType: output file format type (can be CSV or EXCEL)
// - fileName: output file name (without extension)
// - destinationFolder: output file destination folder
//
string destinationFileType = "CSV";
string fileName = "test_GetWhereUsedProductID";
string destinationFolder = @"C:\Users\LZavarella\Desktop";
//
// CSV file details (used if destinationFileType = "CSV"):
// - columnDelimiter: delimiter used to separate file columns
// - rowDelimiter: delimiter used to separate file rows
//
string columnDelimiter = "Semicolon";
string rowDelimiter = "CRLF";
//
// Excel file details (used if destinationFileType = "EXCEL")
// - excelProvider: provider to use in order to create the Excel file;
// the Jet one si used for Excel 97-2003; the ACE one for Excel 2007
// - excelExtendedProperties: extended properties to be used in the Excel connection string
//
string excelProvider = "Microsoft.Jet.OLEDB.4.0"; // e.g. for ACE: "Microsoft.ACE.OLEDB.12.0"
string excelExtendedProperties = "Excel 8.0;HDR=YES;IMEX=0"; // e.g. for ACE: "Excel 12.0 XML;HDR=YES;"
/* ======================================================================= */
/* === DO NOT DELETE/MODIFY ANYTHING UNDER THIS PLACEHOLDER!! === */
/* ======================================================================= */
string filePath = "";
string fileExtension = "";
int excelStringFormat = -1;
string excelDecimalFormat = "";
int excelDecimalPrecision = -1;
int excelDecimalScale = -1;
string excelFieldTerminator = ",";
string excelTableTerminator = ")";
string excelTempTerminator = "";
string excelColumnType = "";
string sourceConnectionString = "";
string connStrOLEDB = String.Format("Data Source={0};Initial Catalog={1};Provider={2};Integrated Security=SSPI;Auto Translate=False;", serverName, databaseName, oledbProvider);
string connStr = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", serverName, databaseName);
if (destinationFileType == "CSV")
{
fileExtension = ".csv";
filePath = destinationFolder + fileName + fileExtension;
}
else if (destinationFileType == "EXCEL")
{
fileExtension = excelProvider.ToUpper().Contains("JET") ? ".xls" : ".xlsx";
filePath = destinationFolder + fileName + fileExtension;
sourceConnectionString = String.Format("Provider={0};Data Source=\"{1}\";Extended Properties=\"{2}\"",
excelProvider, filePath, excelExtendedProperties);
}
string packageName = packagePrefix + (storedProcedurePrefix == "" ? storedProcedureName : storedProcedureName.Replace(storedProcedurePrefix, ""));
string executeStatement = storedProcedureSchema + "." + storedProcedurePrefix +
(storedProcedurePrefix == "" ? storedProcedureName : storedProcedureName.Replace(storedProcedurePrefix, "")) +
" " + storedProcedureParameters.Replace("GETDATE()", "'20130101'");
string storedProcedure = storedProcedureSchema + "." + storedProcedurePrefix +
(storedProcedurePrefix == "" ? storedProcedureName : storedProcedureName.Replace(storedProcedurePrefix, ""));
string storedProcedureMetadataQuery = "SET FMTONLY ON; EXEC " + executeStatement + "; SET FMTONLY OFF;";
string queryGetMajorVersion = "DECLARE @version nvarchar(128) = CAST(serverproperty('ProductVersion') AS nvarchar); " +
"DECLARE @ver INT; " +
"SET @ver = CAST(SUBSTRING(@version, 1, CHARINDEX('.', @version) - 1) AS INT); " +
"SELECT major_version = @ver;";
using (SqlConnection sqlConnection = new SqlConnection(connStr))
{
SqlCommand cmdMajorVer = new SqlCommand(queryGetMajorVersion, sqlConnection);
SqlCommand cmdMetadata = new SqlCommand(storedProcedureMetadataQuery, sqlConnection);
sqlConnection.Open();
int major_version = (int)cmdMajorVer.ExecuteScalar();
SqlDataReader reader = cmdMetadata.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable storedProcedureTable = reader.GetSchemaTable();
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<# if (destinationFileType == "CSV") { #>
<FileFormats>
<FlatFileFormat Name="CSVFlatFile" RowDelimiter="CRLF" HeaderRowDelimiter="CRLF" ColumnNamesInFirstDataRow="true" IsUnicode="1">
<Columns>
<!--The RowDelimiter SSIS takes into account is the one of the last column.-->
<# string tempDelimiter = "";
foreach (DataRow column in storedProcedureTable.Rows) {
if (((int)column["ColumnOrdinal"] + 1) < storedProcedureTable.Rows.Count)
tempDelimiter = columnDelimiter;
else
tempDelimiter = rowDelimiter;
#>
<Column Name="<#=column["ColumnName"].ToString()#>" DataType="String" Length="256" Delimiter="<#=tempDelimiter#>" />
<# } #>
</Columns>
</FlatFileFormat>
</FileFormats>
<# } #>
<Connections>
<OleDbConnection Name="<#=packageConnectionManagerName#>" ConnectionString="<#=connStrOLEDB#>"/>
<# if (destinationFileType == "CSV") { #>
<FlatFileConnection Name="CSV" FilePath="<#=filePath#>" FileFormat="CSVFlatFile">
<Expressions>
<Expression PropertyName="ConnectionString">@[User::param_DestinationFolder] + @[User::param_FileName] + "<#=fileExtension#>"</Expression>
</Expressions>
</FlatFileConnection>
<# } #>
<# if (destinationFileType == "EXCEL") { #>
<FileConnection Name ="<#=filePath #>" FilePath="<#=filePath#>" DelayValidation="true"/>
<ExcelConnection DelayValidation="true" Name="EXCEL" ConnectionString="<#=sourceConnectionString.Replace("\"", """)#>">
<Expressions>
<Expression PropertyName="ExcelFilePath">@[User::param_DestinationFolder] + @[User::param_FileName] + "<#=fileExtension#>"</Expression>
</Expressions>
</ExcelConnection>
<# } #>
</Connections>
<Packages>
<Package Name="<#=packageName#>" ConstraintMode="Linear" DelayValidation ="true" ProtectionLevel="<#=packageProtectionLevel#>">
<Variables>
<Variable Name="param_StoredProcedureParameters" DataType="String" EvaluateAsExpression="1"><#=storedProcedureParameters#></Variable>
<Variable Name="param_DestinationFolder" DataType="String"><#=destinationFolder#></Variable>
<Variable Name="param_FileName" DataType="String" EvaluateAsExpression="1"><#=fileName#></Variable>
<Variable Name="ExecuteStatement" DataType="String" EvaluateAsExpression="1">EXEC <#=executeStatement#></Variable>
</Variables>
<Connections>
<Connection ConnectionName="<#=packageConnectionManagerName#>"></Connection>
</Connections>
<Tasks>
<# if (destinationFileType == "EXCEL") { #>
<FileSystem Name="Delete Excel File" Operation="DeleteFile">
<FileInput ConnectionName="<#=filePath #>"/>
</FileSystem>
<# } #>
<Script Name="Add Data and Time to the File Name">
<ScriptTaskProject>
<ScriptTaskProject Name="Add Data and Time to the File Name and Parameters">
<AssemblyReferences>
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />
<AssemblyReference AssemblyPath="System.dll" />
<# if (major_version < 11) { #>
<AssemblyReference AssemblyPath="System.AddIn.dll" />
<# } #>
<AssemblyReference AssemblyPath="System.Data.dll" />
<AssemblyReference AssemblyPath="System.Windows.Forms.dll" />
<AssemblyReference AssemblyPath="System.Xml.dll" />
</AssemblyReferences>
<ReadWriteVariables>
<Variable VariableName="param_FileName" DataType="String"/>
<Variable VariableName="param_StoredProcedureParameters" DataType="String"/>
<Variable VariableName="param_DestinationFolder" DataType="String"/>
<Variable VariableName="ExecuteStatement" DataType="String"/>
</ReadWriteVariables>
<Files>
<File Path="ScriptMain.cs">
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
<# if (major_version < 11) { #>
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
<# } else { #>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
<# } #>
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
public void Main()
{
bool fireAgain = true;
string fileName = Dts.Variables["User::param_FileName"].Value.ToString();
string stpParam = Dts.Variables["User::param_StoredProcedureParameters"].Value.ToString();
string destinationFolder = Dts.Variables["User::param_DestinationFolder"].Value.ToString();
// Add date and time to the file name
<#if (destinationFileType == "CSV") {#>
Dts.Variables["User::param_FileName"].Value = fileName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");
<#} else if (destinationFileType == "EXCEL") {#>
Dts.Variables["User::param_FileName"].Value = fileName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");
<#}#>
// Replace the GETDATE() token with the current datetime
string currentDate = "'" + DateTime.Now.ToString("yyyyMMdd HH:mm:ss.fff") + "'";
Dts.Variables["User::param_DestinationFolder"].Value = destinationFolder.EndsWith("\\") ? destinationFolder : destinationFolder + "\\";
Dts.Variables["User::param_StoredProcedureParameters"].Value = stpParam.Replace("GETDATE()", currentDate);
Dts.Variables["User::ExecuteStatement"].Value = String.Format("EXEC <#=storedProcedure#> {0}", stpParam.Replace("GETDATE()", currentDate));
Dts.Events.FireInformation(0, string.Empty, string.Format("Calling procedure: [{0}]", Dts.Variables["User::ExecuteStatement"].Value), string.Empty, 0, ref fireAgain);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
</File>
</Files>
</ScriptTaskProject>
</ScriptTaskProject>
</Script>
<# if (destinationFileType == "EXCEL") { #>
<ExecuteSQL Name="Create Excel Sheet" FailPackageOnFailure="true" ConnectionName="EXCEL">
<DirectInput>CREATE TABLE [Query] (
<# foreach (DataRow column in storedProcedureTable.Rows)
{
if (((int)column["ColumnOrdinal"] + 1) < storedProcedureTable.Rows.Count)
excelTempTerminator = excelFieldTerminator;
else
excelTempTerminator = excelTableTerminator;
excelStringFormat = (int)column["ColumnSize"];
excelDecimalFormat = String.Format("{0},{1}", column["NumericPrecision"], column["NumericScale"]);
if (column["DataTypeName"].ToString() == "bigint")
excelColumnType = "decimal(18,0)";
else if (column["DataTypeName"].ToString() == "bit")
excelColumnType = "bit";
else if (column["DataTypeName"].ToString() == "char")
if (excelStringFormat <= 255)
excelColumnType = "char(" + excelStringFormat + ")";
else
excelColumnType = "longtext";
else if (column["DataTypeName"].ToString() == "datetime"
|| column["DataTypeName"].ToString() == "date")
excelColumnType = "datetime";
else if (column["DataTypeName"].ToString() == "decimal"
|| column["DataTypeName"].ToString() == "numeric")
if (excelDecimalPrecision <= 28)
excelColumnType = "decimal(" + excelDecimalFormat + ")";
else
excelColumnType = "decimal(28,4)";
else if (column["DataTypeName"].ToString() == "float")
excelColumnType = "float";
else if (column["DataTypeName"].ToString() == "int")
excelColumnType = "int";
else if (column["DataTypeName"].ToString() == "money")
excelColumnType = "currency";
else if (column["DataTypeName"].ToString() == "nchar")
if (excelStringFormat <= 255)
excelColumnType = "nchar(" + excelStringFormat + ")";
else
excelColumnType = "longtext";
else if (column["DataTypeName"].ToString() == "ntext")
excelColumnType = "longtext";
else if (column["DataTypeName"].ToString() == "nvarchar")
if (excelStringFormat <= 255)
excelColumnType = "varchar(" + excelStringFormat + ")";
else
excelColumnType = "longtext";
else if (column["DataTypeName"].ToString() == "real")
excelColumnType = "real";
else if (column["DataTypeName"].ToString() == "smallint")
excelColumnType = "smallint";
else if (column["DataTypeName"].ToString() == "text")
excelColumnType = "longtext";
else if (column["DataTypeName"].ToString() == "tinyint")
excelColumnType = "byte";
else if (column["DataTypeName"].ToString() == "varchar")
if (excelStringFormat <= 255)
excelColumnType = "varchar(" + excelStringFormat + ")";
else
excelColumnType = "longtext";
#>
[<#=column["ColumnName"].ToString()#>] <#=excelColumnType#><#=excelTempTerminator#>
<# } //...foreach #>
</DirectInput>
</ExecuteSQL>
<# } //...if (destinationFileType == "EXCEL")#>
<Dataflow DelayValidation="true" Name="Load Destination File">
<Transformations>
<OleDbSource Name="<#=storedProcedureName#>" ConnectionName="<#=packageConnectionManagerName#>" ValidateExternalMetadata="false">
<VariableInput VariableName="User.ExecuteStatement"/>
</OleDbSource>
<# if (destinationFileType == "CSV") { #>
<FlatFileDestination Name="CSV" ConnectionName="CSV"/>
<# } #>
<# if (destinationFileType == "EXCEL") {
bool dataConversionNeeded = false;
foreach (DataRow column in storedProcedureTable.Rows) {
if (column["DataTypeName"].ToString() == "char" || column["DataTypeName"].ToString() == "varchar" || column["DataTypeName"].ToString() == "text") {
dataConversionNeeded = true;
break;
}
}
if (dataConversionNeeded) { #>
<DataConversion Name="Data Conversion" ValidateExternalMetadata="false">
<Columns>
<# foreach (DataRow column in storedProcedureTable.Rows) {
excelStringFormat = (int)column["ColumnSize"];
excelDecimalFormat = String.Format("{0},{1}", column["NumericPrecision"], column["NumericScale"]);
if (column["DataTypeName"].ToString() == "char" || column["DataTypeName"].ToString() == "varchar") { #>
<Column SourceColumn="<#=column["ColumnName"].ToString()#>" DataType="String" Length="<#=excelStringFormat#>"/>
<# } else if (column["DataTypeName"].ToString() == "text") { #>
<Column SourceColumn="<#=column["ColumnName"].ToString()#>" DataType="String" Length="4000"/>
<# } #>
<# } //...foreach #>
</Columns>
</DataConversion>
<# } //... if dataConversionNeeded
// In order to compile the current Biml script, the next code will create the Excel file
if(File.Exists(filePath))
File.Delete(filePath);
string excelSheetMetadata = "CREATE TABLE [Query] (";
foreach (DataRow column in storedProcedureTable.Rows)
{
if (((int)column["ColumnOrdinal"] + 1) < storedProcedureTable.Rows.Count)
excelTempTerminator = excelFieldTerminator;
else
excelTempTerminator = excelTableTerminator;
excelStringFormat = (int)column["ColumnSize"];
excelDecimalFormat = String.Format("{0},{1}", column["NumericPrecision"], column["NumericScale"]);
if (column["DataTypeName"].ToString() == "bigint")
excelColumnType = "decimal(18,0)";
else if (column["DataTypeName"].ToString() == "bit")
excelColumnType = "bit";
else if (column["DataTypeName"].ToString() == "char")
if (excelStringFormat <= 255)
excelColumnType = "char(" + excelStringFormat + ")";
else
excelColumnType = "longtext";
else if (column["DataTypeName"].ToString() == "datetime"
|| column["DataTypeName"].ToString() == "date")
excelColumnType = "datetime";
else if (column["DataTypeName"].ToString() == "decimal"
|| column["DataTypeName"].ToString() == "numeric")
if (excelDecimalPrecision <= 28)
excelColumnType = "decimal(" + excelDecimalFormat + ")";
else
excelColumnType = "decimal(28,4)";
else if (column["DataTypeName"].ToString() == "float")
excelColumnType = "float";
else if (column["DataTypeName"].ToString() == "int")
excelColumnType = "int";
else if (column["DataTypeName"].ToString() == "money")
excelColumnType = "currency";
else if (column["DataTypeName"].ToString() == "nchar")
if (excelStringFormat <= 255)
excelColumnType = "nchar(" + excelStringFormat + ")";
else
excelColumnType = "longtext";
else if (column["DataTypeName"].ToString() == "ntext")
excelColumnType = "longtext";
else if (column["DataTypeName"].ToString() == "nvarchar")
if (excelStringFormat <= 255)
excelColumnType = "varchar(" + excelStringFormat + ")";
else
excelColumnType = "longtext";
else if (column["DataTypeName"].ToString() == "real")
excelColumnType = "real";
else if (column["DataTypeName"].ToString() == "smallint")
excelColumnType = "smallint";
else if (column["DataTypeName"].ToString() == "text")
excelColumnType = "longtext";
else if (column["DataTypeName"].ToString() == "tinyint")
excelColumnType = "byte";
else if (column["DataTypeName"].ToString() == "varchar")
if (excelStringFormat <= 255)
excelColumnType = "varchar(" + excelStringFormat + ")";
else
excelColumnType = "longtext";
excelSheetMetadata += String.Format("[{0}] {1}{2}", column["ColumnName"].ToString(), excelColumnType, excelTempTerminator);
}
DataTable excelSheet = ExternalDataAccess.GetDataTable(sourceConnectionString, excelSheetMetadata);
#>
<ExcelDestination Name="EXCEL" ConnectionName="EXCEL" UseFastLoadIfAvailable="false" MaximumInsertCommitSize="2147483647" ValidateExternalMetadata="false">
<ErrorHandling ErrorRowDisposition="IgnoreFailure" TruncationRowDisposition="IgnoreFailure">
<Inputs>
<Input ErrorRowDisposition="FailComponent" TruncationRowDisposition="IgnoreFailure" InputName="Destination Input" />
</Inputs>
</ErrorHandling>
<ExternalTableOutput Table="[Query]"/>
<Columns>
<# foreach (DataRow column in storedProcedureTable.Rows) { #>
<Column SourceColumn="<#=column["ColumnName"].ToString()#>" TargetColumn="<#=column["ColumnName"].ToString()#>"/>
<# } #>
</Columns>
</ExcelDestination>
<#reader.Close();
} //...if (destinationFileType == "EXCEL")
} //...using #>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
Submitted by Luca Zavarella and Mauro Regoli.