如何使用SSIS脚本任务将多个存储过程中的数据写入文件？

Question

我在 SQL Server 2008 中创建了一个 SSIS 包，需要它来迭代包含 ID、输出文件位置、要执行的 spname 的表，并包含表中每个 clientID 的数据。存储过程输出不同的报告并包含不同的字段，这是自动化当前使用多个 SSIS 包生成客户报告的过程所必需的，我想简化整个过程。

我创建的包包含一个 SQL 脚本组件，它将数据保存到一个变量对象，“每个容器”循环访问该对象变量并将数据保存到各个变量。但是，我需要脚本任务方面的帮助，因为我当前可以连接到 oledb 连接管理器，但是在尝试执行存储过程时脚本任务失败，可能是因为数据不是通过视图或表输出。

我是 VB.NET 脚本编写的新手，对 VB.NET 没有太多了解，我使用以下线程到达我当前的位置，并将 dbcommand 替换为我

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

使用以下帖子到达我当前的位置：

从以下位置导出数据使用 SSIS 脚本任务将多个 SQL 表复制到不同的平面文件

当我执行脚本任务时，它在打开 try 和 dbconnection 时失败。我尝试将其注释掉并在没有 try 部分的情况下运行脚本任务，但它仍然出错。

Answer 1

由于您使用我提供的示例作为问题的答案 使用 SSIS 脚本任务将数据从多个 SQL 表导出到不同的平面文件，我采用了相同的示例并对其进行了修改以运行存储过程。

它似乎工作没有任何问题。确保它与您想要做的事情相匹配。此示例使用 SSIS 2005 和 SQL Server 2008 R2 数据库。

分步过程：

创建三个表，即 dbo.TablesList、dbo.Source1 和 dbo.Source2。使用一些示例数据填充表。另外，创建两个名为 dbo.SP1 和 dbo.SP2 的存储过程。下面给出的脚本执行这些任务来创建表和存储过程。

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

表中的数据将如下面的屏幕截图所示。

在 SSIS 包上，创建到 SQL Server 实例的连接管理器。

在 SSIS 包上，创建 4 个变量，即 Delimiter、FileName、<代码>RunSP和<代码>SPsList。另外，将“执行 SQL 任务”、“Foreach 循环容器”和“脚本任务”放置在“控制流”选项卡上，如下面的屏幕截图所示。

配置执行 SQL 任务以获取存储过程列表，如下面两个屏幕截图所示。

配置 Foreach 循环容器以循环访问变量 SPsList 中存储的结果集，如下面两个屏幕截图所示。

在脚本任务的脚本部分中，单击设计脚本...按钮以打开 VSTA 编辑器。

将 VB.NET 代码替换为以下给出的代码。粘贴代码后，关闭 VSTA 编辑器以保存更改。

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

执行包之前文件夹 F:\Temp 的内容。该文件夹是空的。

下面的屏幕截图显示了成功的包执行。

文件夹路径 F:\Temp 现在包含包内脚本任务使用提供的数据生成的两个文件由单独的存储过程 SP1 和 SP2 执行。

文件内容如下面的屏幕截图所示。文件内容以竖线分隔，您可以注意到数据与前面的屏幕截图中显示的表数据匹配。

希望有帮助。