SSIS: Make your output files dynamic

I like making my SSIS packages as dynamic as possible. Once that package has been deployed into production I want to avoid opening it up in BIDS if possible. I’ve blogged previously about using Stored Procedures in Execute SQL Tasks but this only gives us flexibility in terms of the where clause. We have no flexibility in terms of the columns unless we open the package up in BIDS.

I wanted to build a little more flexibility into my packages. Wouldn’t it be great if we could simply modify a stored procedure to include additional columns, and these changes would be reflected in the output files, with no further work required? Here’s an outline of my first steps towards achieving this.

• First create a stored procedure, that returns a resultset, in a test database. Anyone will do. Here’s one I created in the AdventureWorks test database.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_test] AS
BEGIN

	SELECT *
	FROM Production.Product;

END

• Open up BIDS and create a new integration services project.
• Add an object variable like below called resultset.

• Add an Execute SQL Task to the designer and configure it to execute the procedure you created earlier. Configure it similarly to below, ensuring Result Set is set to Full result set.

• Go to the Result Set tab and add a mapping to the resultset object variable. Configure this exactly as shown below.

• Add a Script Task to the designer and connect the Execute SQL Task to it. Edit the Script Task and enter the resultset variable in the ReadOnlyVariable box.

• Click “Edit Script” and paste the below code into VSTA. The only part of this code you should need to change is the path where the text file is written to.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.IO

 _
 _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()
        '
        ' Add your code here
        '
        Dim filename As String = Format(Date.Now, "yyyy-MM-dd_hh_mm_ss") & ".csv"
        Dim fileContents As String = ""

        Dim oledb As OleDbDataAdapter = New OleDbDataAdapter()
        Dim table As DataTable = New DataTable()
        Dim rs As System.Object = Dts.Variables("resultset").Value

        oledb.Fill(table, rs)

        ' Get the column names
        For Each col In table.Columns
            fileContents &= col.ColumnName & "|"
        Next

        ' remove last pipe
        fileContents = fileContents.Substring(0, fileContents.Length - 1)
        fileContents &= Environment.NewLine

        ' For each row in the dataset
        Dim i As Integer
        For Each row As DataRow In table.Rows
            ' For each column in the row
            For i = 1 To table.Columns.Count
                fileContents &= row(i - 1).ToString() & "|"
            Next

            ' Remove last pipe and add a newline to the end of each row
            fileContents = fileContents.Substring(0, fileContents.Length - 1)
            fileContents &= Environment.NewLine

        Next

        ' write data to the text file
        ' Change the path to something appropriate
        SaveTextToFile(fileContents, "C:\Users\Rhys\Desktop\" & filename)

        Dts.TaskResult = ScriptResults.Success
    End Sub

    ' Function from http://www.freevbcode.com/ShowCode.Asp?ID=4492
    Public Function SaveTextToFile(ByVal strData As String, _
     ByVal FullPath As String, _
       Optional ByVal ErrInfo As String = "") As Boolean

        Dim bAns As Boolean = False
        Dim objReader As StreamWriter
        Try

            objReader = New StreamWriter(FullPath)
            objReader.Write(strData)
            objReader.Close()
            bAns = True
        Catch Ex As Exception
            ErrInfo = Ex.Message

        End Try
        Return bAns
    End Function

End Class

• Save the script and return to the designer. The final package should look something like below.

• Execute the package and then check the location where your output file should be written to. Here’s what the file should look like if you’re using my usp_test stored procedure in the AdventureWorks database.

• Now comes the fun part! Modify the usp_test procedure to select from a completely different table. For example;

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_test] AS
BEGIN

	SELECT *
	FROM HumanResources.Employee;

END

• Ordinarily this change would break an SSIS package. Execute the package again and check the output file.

• Let’s try another version of usp_test.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_test] AS
BEGIN

	SELECT 'I can change my output files simply by changing this stored procedure!', GETDATE();

END

• Run the package and check the new output file.

OK, so we now have an SSIS package, producing output files, that copes with complete modifications to stored procedures with no other changes needed. This isn’t yet production ready as there are a few issues I need to resolve.

• The Script Task seems to buckle for XML data types giving an unsupported conversion error. (Use the Person.Contact table in the AdventureWorks database to view this issue).
• The package does not seem to cope with a large number of rows. Execution appears to freeze for datasets containing somewhat over 1,000 rows. (Use the Person.Address table in the AdventureWorks database to view this issue). I’m wondering if this is due to size limitation with the System.Object type.

I’ll update this post one I get around to resolving these issues.

UPDATE - See Part 2 to see how I resolved these issues.