使用 SSIS 脚本任务将数据从多个 SQL 表导出到不同的平面文件
我正在尝试创建一个数据网格并使用 VB.NET 将内容导出到文本文件,并且我在 SSIS 脚本任务中执行此操作,以便自动执行将动态表导出到文本文件的过程。我没有收到任何错误,并且文件已创建,但文件是空的。
我在这段代码中做错了什么?
Public Sub Main()
Dim FName As String = "D:\test.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
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:\test.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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这是使用脚本任务将不同结构的表导出到平面文件的可能方法。此示例将使用脚本任务将包含不同字段和数据的两个表导出到平面文件。为了导出数据,您可以使用
DataReader
而不是使用DataGrid
。可能还有其他可能的方法来做到这一点。分步过程:
dbo.TablesList
、dbo.Source1
和dbo.Source2
使用SQL 脚本部分中给出的脚本。dbo.TablesList
、dbo.Source1
和 `dbo.Source2``。连接管理器
上,创建一个名为 SQLServer 的OLE DB 连接
,以连接到 SQL Server 实例,如屏幕截图所示 # 2.Foreach 循环容器
中放置一个执行 SQL 任务
、一个Foreach 循环容器
和一个脚本任务
代码> 如屏幕截图#4 所示。执行 SQL 任务
,如屏幕截图 #5 和 #6 所示。Foreach 循环容器
,如屏幕截图 #7 和 #8 所示。脚本任务代码
部分中给出的代码。希望有帮助。
SQL 脚本:
脚本任务代码:(使用下面给出的代码替换脚本任务中的 Main() 方法)
VB Main()可在
SSIS 2005 及更高版本
中使用的方法代码:Screenshot #1:
屏幕截图 #2:
屏幕截图 #3:
屏幕截图 #4:
屏幕截图 #5:
屏幕截图 #6:
屏幕截图 #7:< /strong>
屏幕截图#8:
屏幕截图 #9:
屏幕截图 #10:
屏幕截图 #11:
屏幕截图 #12:
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 theDataReader
instead of using theDataGrid
. There could be other possible ways to do this.Step-by-step process:
dbo.TablesList
,dbo.Source1
anddbo.Source2
using the scripts given under SQL Scripts section.dbo.TablesList
,dbo.Source1
and `dbo.Source2`` with data shown in screenshot #1.Connection manager
, create anOLE DB connection
named SQLServer to connect to the SQL Server instance as shown in screenshot #2.Execute SQL Task
, aForeach Loop Container
and aScript Task
within theForeach loop container
as shown in screenshot #4.Execute SQL task
as shown in screenshots #5 and #6.Foreach Loop container
as shown in screenshots #7 and #8.Script Task Code
.Hope that helps.
SQL Scripts:
Script Task Code: (Use the code given below to replace the Main() method in your Script task)
VB Main() method code that can be used in
SSIS 2005 and above
:Screenshot #1:
Screenshot #2:
Screenshot #3:
Screenshot #4:
Screenshot #5:
Screenshot #6:
Screenshot #7:
Screenshot #8:
Screenshot #9:
Screenshot #10:
Screenshot #11:
Screenshot #12:
为什么不使用 OLEDB 源组件,将表查询放入其中,然后将其输出到 SSIS 中的平面文件编写器,而不是使用脚本组件? 此博客post 说明了如何做到这一点。
Why don't you use an OLEDB source component, put your table query in there and then output it to a flat file writer in SSIS instead of using a script component? This blog post illustrates how to do this.