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.

ssis resultset variable thumb SSIS: Make your output files dynamic

  • 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.

ssis exec sql task thumb SSIS: Make your output files dynamic

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

ssis exec sql task resultset thumb SSIS: Make your output files dynamic

  • 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.

ssis script task thumb SSIS: Make your output files dynamic

  • 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.

ssis dynamic output file package thumb SSIS: Make your output files dynamic

  • 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.

AdventureWorks Output File 1 thumb SSIS: Make your output files dynamic

  • 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.

AdventureWorks Output File 2 thumb SSIS: Make your output files dynamic

  • 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.

AdventureWorks Output File 3 thumb SSIS: Make your output files dynamic

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.


11 Comments

  1. […] SSIS: Make your output files dynamic – 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. […]

  2. Bobby Earl says:

    Thanks for the post. I have to do this on a fairly large scale in reverse. Taking text files with different delimiters and different column names and importing them into different tables. I’ve got most of the metadata and a schema of control and reporting tables written. Now for the scripting. This post helps a lot.

  3. Rhys says:

    Hi Bobby,

    I was actually thinking of doing some more work this weekend to solve the issues I highlighted. I’d be interested to see your work if you happen to be a blogger.

    Cheers,

    Rhys

  4. Bobby Earl says:

    Rhys,

    I’m not really a blogger, but I will give it a go when I get a working Alpha process – hopefully within a couple of weeks. Thanks again for your site. The name has special meaning to me and the people in my group. We have a huge datawarehouse and amazing automation capabilities doing things “that can’t be done with SQL/DTS/etc.”. Thanks for the great work.

    Bob

  5. Bobby Earl says:

    Rhys –
    I don’t really blog, but I’ll be happy to share when I get a working Alpha – probably a couple of weeks.

    Thanks

    Bob

  6. Rhys says:

    Please do. I’d be happy to put something up here and give you credit.

  7. Bobby Earl says:

    Rhys:
    Just wanted to update you. I’ve almost completed a single package that will grab parameters from a set of tables and pass them into a script task that then creates a package and runs it in memory. I actually generate two packages in the execution of the parent. One for loading data into a stage table (where necessary) and one that handles the archiving of the data. I’m putting the finishing touches on it now and will be willing to share some tidbits if interested when it is complete.

    Bob

  8. Rhys says:

    Hi Bobby,

    Great stuff! Sounds interesting and some tidbits would be appreciated.

    Cheers,

    Rhys

  9. friedrice08 says:

    Thanks for this stuff! I just modified your code to create an excel file out of the resultset.

  10. Latha says:

    Rhys,
    When I try using your above example code in SSIS 2008 R2 I keep getting this error message. Can you please tell me what might’ve been went wrong?
    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
    —> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

    at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.get_Item(Object Index)
    at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
    — End of inner exception stack trace —
    at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
    at ST_4dc02813389a4e2ea843fdf9b59181bc.vbproj.ScriptMain.Main()
    — End of inner exception stack trace —
    at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
    at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    Thanks,
    -Latha

  11. Rhys says:

    Hi Latha,

    It’s been a while but…

    Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.get_Item(Object Index)

    I think this might mean you haven’t configured the resultset variable properly. This needs to be configured on the Script Task properties page or the code will not be able to access the variable. Check you are passing it in the the ReadOnlyVariables section.

    Rhys

Leave a Reply

Current ye@r *