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.
