使用 SSIS 脚本任务将数据从多个 SQL 表导出到不同的平面文件 [英] Exporting data from multiple SQL tables to different flat files using SSIS Script Task

问题描述

我正在尝试创建一个数据网格并使用 VB.NET 将内容导出到文本文件，我在 SSIS 脚本任务中执行此操作，以便自动执行将动态表导出到文本文件的过程.我没有收到任何错误，文件已创建，但文件为空.

I am trying to create a datagrid and export the contents to a text file using VB.NET and I am doing this inside an SSIS script task in order to automate the process to export a dynamic table to text file. I don't get any error and the files are created but the files are empty.

我在这段代码中做错了什么?

What am I doing wrong here in this code?

Public Sub Main()

    Dim FName As String = "D:	est.TXT"

    ''''''''''''''''''''''''''''''''''''''''''
    If File.Exists(FName) Then
        File.Delete(FName)
    End If
    ''''''''''''''''''''''''''''''''''''''''''

    Dim myConnection As OleDbConnection = New OleDbConnection("Data Source=localhost;Provider=SQLNCLI10;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=SSPI;")
    Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from Table")
    Dim ds As DataSet = New DataSet

    da.Fill(ds, "Test")

    Dim DataGrid1 As New DataGrid
    DataGrid1.DataSource = ds.DefaultViewManager
    Dim DataGridView1 As New DataGridView

    DataGridView1.DataSource = ds

    Dim dgvc As DataGridViewCell
    Dim sw As New System.IO.StreamWriter(FName)

    For Each dgvr As DataGridViewRow In DataGridView1.Rows

        Dim intCellCount As Integer = dgvr.Cells.Count
        Dim intCounter As Integer = 1

        For Each dgvc In dgvr.Cells()
            If intCounter <> intCellCount Then
                sw.Write(dgvc.Value.ToString & "|")
            Else
                sw.WriteLine(dgvc.Value.ToString)
            End If

            intCounter += 1
        Next
    Next

    Dts.TaskResult = ScriptResults.Success

End Sub

推荐答案

这里是一种使用 Script Task 将不同结构的表格导出到平面文件的可能方法.此示例将使用脚本任务将包含不同字段和数据的两个表导出到平面文件.为了导出数据，您可以使用DataReader 而不是使用DataGrid.可能还有其他可能的方法来做到这一点.

Here is a possible way of exporting the tables of different structure to flat file using Script Task. This example will export two tables containing different fields and data to a flat file using Script Task. In order to export the data, you can use the DataReader instead of using the DataGrid. There could be other possible ways to do this.

分步过程:

1. 使用 SQL 脚本<下给出的脚本创建三个名为 dbo.TablesList、dbo.Source1 和 dbo.Source2 的表/strong> 部分.
2. 使用屏幕截图 #1 中显示的数据填充表 dbo.TablesList、dbo.Source1 和 `dbo.Source2`.
3. 在 SSIS 包的 Connection manager 上，创建一个名为 SQLServer 的 OLE DB 连接 以连接到 SQL Server 实例，如屏幕截图所示#2.
4. 在包中，创建 4 个变量，如屏幕截图 #3 所示.
5. 在控制流中，在 Foreach 循环中放置一个 Execute SQL Task、一个 Foreach Loop Container 和一个 Script Task容器，如屏幕截图 #4 所示.
6. 配置执行 SQL 任务，如屏幕截图 #5 和 #6 所示.
7. 配置 Foreach Loop 容器，如屏幕截图 #7 和 #8 所示.
8. 将脚本任务中的 Main 方法替换为 脚本任务代码 部分下给出的代码.
9. 屏幕截图 #9 显示了包的执行情况.
10. 屏幕截图 #10 - #12 显示使用脚本任务代码从 SSIS 导出的文件.

1. Create three tables named dbo.TablesList, dbo.Source1 and dbo.Source2 using the scripts given under SQL Scripts section.
2. Populate the tables dbo.TablesList, dbo.Source1 and `dbo.Source2`` with data shown in screenshot #1.
3. On the SSIS package's Connection manager, create an OLE DB connection named SQLServer to connect to the SQL Server instance as shown in screenshot #2.
4. In the package, create 4 variables as shown in screenshot #3.
5. In the Control Flow, place an Execute SQL Task, a Foreach Loop Container and a Script Task within the Foreach loop container as shown in screenshot #4.
6. Configure the Execute SQL task as shown in screenshots #5 and #6.
7. Configure the Foreach Loop container as shown in screenshots #7 and #8.
8. Replace the Main method inside the Script Task with the code given under the section Script Task Code.
9. Screenshot #9 shows package execution.
10. Screenshots #10 - #12 show the files exported from SSIS using Script Task code.

希望有所帮助.

SQL 脚本:

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,
    [TableName] [varchar](50) NOT NULL,
    [FilePath] [varchar](255) NOT NULL,
CONSTRAINT [PK_Tables] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

脚本任务代码:(使用下面给出的代码替换脚本任务中的 Main() 方法)

Script Task Code: (Use the code given below to replace the Main() method in your Script task)

VB可以在SSIS 2005及以上中使用的Main()方法代码:

VB Main() method code that can be used in SSIS 2005 and above:

Public Sub Main()

    Dim varCollection As Variables = Nothing

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

    Dim fileName As String = varCollection("User::FileName").Value.ToString()
    Dim query As String = "SELECT * FROM " & varCollection("User::TableName").Value.ToString()
    Dim delimiter As String = varCollection("User::Delimiter").Value.ToString()

    Dim writer As StreamWriter = Nothing
    Dim connection As OleDbConnection = New OleDbConnection(Dts.Connections("SQLServer").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()

        If reader.HasRows Then

            writer = New System.IO.StreamWriter(fileName)
            Dim row As Integer = 0
            While reader.Read()

                Dim header As Integer = 0
                Dim counter 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

                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

                row += 1
            End While
        End If
    Catch ex As Exception
        Throw ex
    Finally
        connection.Close()
        writer.Close()
    End Try

    Dts.TaskResult = ScriptResults.Success

End Sub

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

屏幕截图 #5:

截图 #6:

屏幕截图 #7:

截图 #8:

屏幕截图 #9:

屏幕截图 #10:

屏幕截图 #11:

屏幕截图 #12:

这篇关于使用 SSIS 脚本任务将数据从多个 SQL 表导出到不同的平面文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！