SSIS 2005

Question

SSIS 2005

lee.adrian

SSC Enthusiast

Points: 129
More actions
February 7, 2010 at 9:43 pm

#144768

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Please post the code fragment which is causing the error and the text of the error message.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

lee.adrian SSC Enthusiast Points: 129 More actions · Answer 2

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.

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 3

Under the squiggly line it will usually tell you what it can't set the variable. What does it say?

lee.adrian SSC Enthusiast Points: 129 More actions · Answer 4

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.