123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520<#@ 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();#>xmlns="http://schemas.varigence.com/biml.xsd"<# if (destinationFileType == "CSV") { #>Name="CSVFlatFile" RowDelimiter="CRLF" HeaderRowDelimiter="CRLF" ColumnNamesInFirstDataRow="true" IsUnicode="1"<!--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;elsetempDelimiter = rowDelimiter;#>Name="<#=column["ColumnName"].ToString()#>" DataType="String" Length="256" Delimiter="<#=tempDelimiter#>"<# } #><# } #>Name="<#=packageConnectionManagerName#>" ConnectionString="<#=connStrOLEDB#>"<# if (destinationFileType == "CSV") { #>Name="CSV" FilePath="<#=filePath#>" FileFormat="CSVFlatFile"PropertyName="ConnectionString"@[User::param_DestinationFolder] + @[User::param_FileName] + "<#=fileExtension#>"<# } #><# if (destinationFileType == "EXCEL") { #>Name ="<#=filePath #>" FilePath="<#=filePath#>" DelayValidation="true"DelayValidation="true" Name="EXCEL" ConnectionString="<#=sourceConnectionString.Replace("\"", """)#>"PropertyName="ExcelFilePath"@[User::param_DestinationFolder] + @[User::param_FileName] + "<#=fileExtension#>"<# } #>Name="<#=packageName#>" ConstraintMode="Linear" DelayValidation ="true" ProtectionLevel="<#=packageProtectionLevel#>"Name="param_StoredProcedureParameters" DataType="String" EvaluateAsExpression="1"<#=storedProcedureParameters#>Name="param_DestinationFolder" DataType="String"<#=destinationFolder#>Name="param_FileName" DataType="String" EvaluateAsExpression="1"<#=fileName#>Name="ExecuteStatement" DataType="String" EvaluateAsExpression="1"EXEC <#=executeStatement#>ConnectionName="<#=packageConnectionManagerName#>"<# if (destinationFileType == "EXCEL") { #>Name="Delete Excel File" Operation="DeleteFile"ConnectionName="<#=filePath #>"<# } #>Name="Add Data and Time to the File Name"Name="Add Data and Time to the File Name and Parameters"AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll"AssemblyPath="Microsoft.SqlServer.ScriptTask.dll"AssemblyPath="System.dll"<# if (major_version < 11) { #>AssemblyPath="System.AddIn.dll"<# } #>AssemblyPath="System.Data.dll"AssemblyPath="System.Windows.Forms.dll"AssemblyPath="System.Xml.dll"VariableName="param_FileName" DataType="String"VariableName="param_StoredProcedureParameters" DataType="String"VariableName="param_DestinationFolder" DataType="String"VariableName="ExecuteStatement" DataType="String"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 datetimestring 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;}}<# if (destinationFileType == "EXCEL") { #>Name="Create Excel Sheet" FailPackageOnFailure="true" ConnectionName="EXCEL"CREATE TABLE [Query] (<# foreach (DataRow column in storedProcedureTable.Rows){if (((int)column["ColumnOrdinal"] + 1) < storedProcedureTable.Rows.Count)excelTempTerminator = excelFieldTerminator;elseexcelTempTerminator = 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 + ")";elseexcelColumnType = "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 + ")";elseexcelColumnType = "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 + ")";elseexcelColumnType = "longtext";else if (column["DataTypeName"].ToString() == "ntext")excelColumnType = "longtext";else if (column["DataTypeName"].ToString() == "nvarchar")if (excelStringFormat <= 255)excelColumnType = "varchar(" + excelStringFormat + ")";elseexcelColumnType = "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 + ")";elseexcelColumnType = "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">VariableName="User.ExecuteStatement"<# if (destinationFileType == "CSV") { #>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) { #>Name="Data Conversion" ValidateExternalMetadata="false"<# 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") { #>SourceColumn="<#=column["ColumnName"].ToString()#>" DataType="String" Length="<#=excelStringFormat#>"<# } else if (column["DataTypeName"].ToString() == "text") { #>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 fileif(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;elseexcelTempTerminator = 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 + ")";elseexcelColumnType = "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 + ")";elseexcelColumnType = "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 + ")";elseexcelColumnType = "longtext";else if (column["DataTypeName"].ToString() == "ntext")excelColumnType = "longtext";else if (column["DataTypeName"].ToString() == "nvarchar")if (excelStringFormat <= 255)excelColumnType = "varchar(" + excelStringFormat + ")";elseexcelColumnType = "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 + ")";elseexcelColumnType = "longtext";excelSheetMetadata += String.Format("[{0}] {1}{2}", column["ColumnName"].ToString(), excelColumnType, excelTempTerminator);}DataTable excelSheet = ExternalDataAccess.GetDataTable(sourceConnectionString, excelSheetMetadata);#>Name="EXCEL" ConnectionName="EXCEL" UseFastLoadIfAvailable="false" MaximumInsertCommitSize="2147483647" ValidateExternalMetadata="false"ErrorRowDisposition="IgnoreFailure" TruncationRowDisposition="IgnoreFailure"ErrorRowDisposition="FailComponent" TruncationRowDisposition="IgnoreFailure" InputName="Destination Input"Table="[Query]"<# foreach (DataRow column in storedProcedureTable.Rows) { #>SourceColumn="<#=column["ColumnName"].ToString()#>" TargetColumn="<#=column["ColumnName"].ToString()#>"<# } #><#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.