vb.net – 使用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
这是使用Script Task将不同结构的表导出到平面文件的可能方法.此示例将使用“脚本任务”将包含不同字段和数据的两个表导出到平面文件.为了导出数据,您可以使用DataReader而不是使用DataGrid.可能有其他可能的方法来做到这一点.
分步过程: >使用SQL Scripts部分下给出的脚本创建三个名为dbo.TablesList,dbo.Source1和dbo.Source2的表. 希望有所帮助. 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,[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,[TableName] [varchar](50) NOT NULL,[FilePath] [varchar](255) NOT NULL,CONSTRAINT [PK_Tables] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO 脚本任务代码:(使用下面给出的代码替换脚本任务中的Main()方法) 可以在SSIS 2005及更高版本中使用的VB Main()方法代码: 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: (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |