How to write data from multiple stored procedures to files using SSIS script task?

Question

I have created an SSIS package in SQL Server 2008 that is required to iterate through a table that contains the ID, location to output files, spname to execute and contains data for each clientID within the table. The stored procedures output different reports and contain differing fields this is required to automate the current process of using multiple SSIS package for customer reports, I want to simplify the whole process.

The package I've created contains an SQL script component that saves the data to a variable object, a 'for each container' iterates through the object variable and saves the data to individual variables. However I require assistance with the script task as I can currently connect to the oledb connection manager however the script task is failing when trying to execute the stored procedure, maybe as the data is not output via view or a table.

I am new to VB.NET scripting and don't have that much knowledge of VB.NET, I used the following thread to get to my current position and replaced the dbcommand with an

Dim query As String = "Exec " & Dts.Variables("User::RunSP").Value

I used the following posting to get to my current position:

Exporting data from multiple SQL tables to different flat files using SSIS Script Task

When I execute the script task, it fails at the point where the try and dbconnection is opened. I've tried commenting this out and running the script task without the try section but it has continued to error.

Answer 1

Since you were using the example that I provided as answer to the question Exporting data from multiple SQL tables to different flat files using SSIS Script Task, I took the same example and modified it to run the stored procedure.

It seems to work without any issues. Make sure it matches with what you are trying to do. This example uses SSIS 2005 with SQL Server 2008 R2 database.

Step-by-step process:

Create three tables namely dbo.TablesList, dbo.Source1 and dbo.Source2. Populate the tables with some sample data. Also, create two stored procedures named dbo.SP1 and dbo.SP2. Below given scripts does these tasks to create tables and stored procedures.

CREATE TABLE [dbo].[Source1](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [varchar](20) NOT NULL,
    [ItemName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Source1] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Source2](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Country] [varchar](20) NOT NULL,
    [StateProvince] [varchar](50) NOT NULL,
CONSTRAINT [PK_Source2] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TablesList](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SPName] [varchar](50) NOT NULL,
    [FilePath] [varchar](255) NOT NULL,
CONSTRAINT [PK_Tables] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

INSERT INTO dbo.TablesList (FilePath, SPName) VALUES 
    ('F:\Temp\Item_Details.txt', 'SP1'),
    ('F:\Temp\Country_StateProvince.txt', 'SP2');
GO

INSERT INTO dbo.Source1 (ItemNumber, ItemName) VALUES 
    ('34534', 'Keyboard'),
    ('24312', 'Mouse'),
    ('78555', 'Monitor');
GO

CREATE PROCEDURE [dbo].[SP1]
AS
BEGIN   
    SET NOCOUNT ON;

    SELECT * FROM dbo.Source1
END
GO

CREATE PROCEDURE [dbo].[SP2]
AS
BEGIN   
    SET NOCOUNT ON;

    SELECT * FROM dbo.Source2
END
GO

Data in the tables will look like as shown below in the screenshot.

On the SSIS package, create a connection manager to the SQL Server instance.

On the SSIS package, create 4 variables namely Delimiter, FileName, RunSP and SPsList. Also, place an Execute SQL Task, Foreach Loop container and Script task on the Control Flow tab as shown in the below screenshot.

Configure the Execute SQL task to fetch the list of stored procedures as shown in the below two screenshots.

Configure the Foreach Loop container to loop through the result set stored in the variable SPsList as shown in the below two screenshots.

In the Script task's Script section, click on the Design Script... button to bring the VSTA editor.

Replace the VB.NET code with the below given code. After pasting the code, close the VSTA editor to save the changes.

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

Public Class ScriptMain

    Public Sub Main()

        Dim varCollection As Variables = Nothing

        Dts.VariableDispenser.LockForRead("User::FileName")
        Dts.VariableDispenser.LockForRead("User::Delimiter")
        Dts.VariableDispenser.LockForRead("User::RunSP")
        Dts.VariableDispenser.GetVariables(varCollection)

        Dim fileName As String = varCollection("User::FileName").Value.ToString()
        Dim query As String = "EXEC " & varCollection("User::RunSP").Value.ToString()
        Dim delimiter As String = varCollection("User::Delimiter").Value.ToString()

        Dim writer As StreamWriter = Nothing
        Dim connection As OleDbConnection = New OleDbConnection(Dts.Connections("Learn2008R2").ConnectionString)
        Dim command As OleDbCommand = Nothing
        Dim reader As OleDbDataReader = Nothing

        Try
            If File.Exists(fileName) Then
                File.Delete(fileName)
            End If

            connection.Open()
            command = New OleDbCommand(query, connection)
            reader = command.ExecuteReader()

            writer = New System.IO.StreamWriter(fileName)

            Dim row As Integer = 0
            Dim header As Integer = 0
            Dim fieldCount As Integer = reader.FieldCount - 1

            If row = 0 Then
                While header <= fieldCount
                    If header <> fieldCount Then
                        writer.Write(reader.GetName(header).ToString() & delimiter)
                    Else
                        writer.WriteLine(reader.GetName(header).ToString())
                    End If
                    header += 1
                End While
            End If

            If reader.HasRows Then
                While reader.Read()
                    Dim counter As Integer = 0
                    While counter <= fieldCount
                        If counter <> fieldCount Then
                            writer.Write(reader(counter).ToString() & delimiter)
                        Else
                            writer.WriteLine(reader(counter).ToString())
                        End If
                        counter += 1
                    End While
                End While
            End If
        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            writer.Close()
        End Try

        Dts.TaskResult = Dts.Results.Success

    End Sub

End Class

Contents of the folder F:\Temp before executing the package. The folder is empty.

Successful package execution is shown in the below screenshot.

Folder path F:\Temp now contains the two files that were generated by the Script Task inside the package using the data provided by the individual stored procedures SP1 and SP2.

Contents of the files are shown in the below screenshots. The file contents are pipe delimited and you can notice the data matches with the table data shown in the earlier screenshot.

Hope that helps.