<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<#
/* ========================================================================= */
/* === GENERATE EXTRACTOR PACKAGE === */
/* ========================================================================= */
/* Version: 1 */
/* Release Date: 2013-07-19 */
/* Authors: Luca Zavarella & Mauro Regoli (SolidQ.com) */
/* License: Creative Commons Attribution-ShareAlike 3.0 */
/* Italy License */
/* (http://creativecommons.org/licenses/by-sa/3.0/it/deed.en) */
/* ===-------------------------------------------------------------------=== */
/* === 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 */
/* ========================================================================= */
// (*) http://connect.microsoft.com/SQLServer/feedback/details/628743/ssis-oledb-command-date-datatype-in-stored-procedure-sqlcommand-yields-operand-error
// (**) https://connect.microsoft.com/SQLServer/feedback/details/477970/excel-data-types-from-ssis
/* ======================================================================= */
/* === 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>
Extract a Stored Procedure resultset in CSV or Excel
lucazav
This Biml script will generate a SSIS package that will extract data from a stored procedure and will persist them in a CSV file or an EXCEL file. SQL Server 2008 (R2) and SQL Server 2012 are supported.
published 07.19.13
last updated 07.21.13
Share
Comments
There are no comments yet.