February 7, 2010 at 9:43 pm
Hi All,
We are running SQL 2005 SP3 (DEV) and SQL 2005 SP2 (UAT & PROD) and I'm in the process of developing a SSIS component to popualte the destination from the source using dynamic SQL. I've run into an interesting issue where I'm unable to declare a MainPipe variable in the script, all the references and imports look correct; however even implementing examples for here (they have been a god send in solving issues) I still encounter the MainPipe issue.
This is causing delays and I'm not to big a fan of having to create 80 odd data flow tasks when all the examples show me I can drive the data flow task dynamically.
Thanks in advance.
February 8, 2010 at 1:07 am
Please post the code fragment which is causing the error and the text of the error message.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 8, 2010 at 1:26 am
Here is the code, everything in it works except the declaration of the MainPipe variable.
Imports System
Imports System.Data
Imports System.Math
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Public Class ScriptMain
Public Sub Main()
Dim DataFlowTaskHost As TaskHost
Dim SourceConnection As ConnectionManager = Nothing
Dim DestinationConnection As ConnectionManager = Nothing
Dim SQLApp As New Microsoft.SqlServer.Dts.Runtime.Application
Dim BaseTransferPackage As Microsoft.SqlServer.Dts.Runtime.Package
Dim SourceSQL As String = CStr(Dts.Variables("User::SourceSQL").Value)
Dim BasePackagePath As String = CStr(Dts.Variables("User::PackagePath").Value)
Dim DestinationTable As String = CStr(Dts.Variables("User::DestinationTable").Value)
When the following is enable the first 2 lines are fine; it's the MainPipe regardless of how the variable is declared and assigned.
' Dim p As Package = New Package()
' Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")
On enabled the following line, it shows with the squiggly underline.
' Dim thMainPipe As TaskHost = e as MainPipe
' Dim dataFlowTask As MainPipe = thMainPipe.InnerObject as MainPipe
Try
BaseTransferPackage = SQLApp.LoadPackage(BasePackagePath, Nothing)
SourceConnection = BaseTransferPackage.Connections.Item("SCR - OOHS_MDS")
DestinationConnection = BaseTransferPackage.Connections.Item("DST - dgps01")
DataFlowTaskHost = CType(BaseTransferPackage.Executables.Item(0), TaskHost)
Dts.TaskResult = Dts.Results.Success
'Dts.TaskResult = BaseTransferPackage.Execute()
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
'DataFlowTaskHost = CType(BaseTransferPackage.Executables(), TaskHost)
'
' MsgBox("Source: " + SourceSQL)
' MsgBox("Destination: " + DestinationTable)
' MsgBox(BasePackagePath)
BaseTransferPackage.Dispose()
End Sub
End Class
Any idea why I can't declare a MainPipe variable.
Thanks in advance.
February 8, 2010 at 12:11 pm
Under the squiggly line it will usually tell you what it can't set the variable. What does it say?
February 8, 2010 at 2:54 pm
Error message is: Type 'MainPipe' is not defined.
I've looked in the project references to try and add; and I can't find the Microsoft.SqlServer.DTSPipelineWrap.dll, let alone any Microsoft.SqlServer.????? references.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply