question on namespaces

question on namespaces

• stevantosic

  You need to reference assembly Microsoft.SqlServer.DtsPipelineWrap. It should be in the Add Reference dialog.
  
  Note the namespace is Microsoft.SqlServer.Dts.Pipeline.Wrapper (unlike Runtime, there is no managed object model for pipeline, just this interop on top of native COM API).
  
  

• bibifokk

  http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.wrapper(SQL.90).aspx

  It looks like dtspipeline.dll is the dll you need to reference. I'm not sure what you need to do to have that installed. I'm just looking on google, I've not used it.

  
  
  

• ietur

  What I am trying to do is to create a function that would receive a type of connection manager and would check if a connection can be established. I tried using AcquireConnection but this does seem to just return the connection string. Particularly, I noticed that for SMTP and flatfiles, it always returns true even if there are no SMTP parameters or the flat file does not exist.

  This is the code that I created but does not seem to work the way I want:

  Public Function connect(ByVal connMgr As Object) As String
  Dim connected As String
  Try
  Dts.Connections(connMgr.name).AcquireConnection(Nothing)
  Return "Successful"
  Catch ex As Exception
  Return "Failure"
  Finally
  Dts.Connections(connMgr.name).ReleaseConnection(Nothing)
  End Try
  End Function

  
  I am looking at implementing the AcquireConnections method then customize, but it needs

  Namespace: Microsoft.SqlServer.Dts.Pipeline
  Assembly: Microsoft.SqlServer.PipelineHost (in microsoft.sqlserver.pipelinehost.dll)

  Here is the code that I got from MSDN that I am trying to incorporate to my code then customize:

  Public Overrides Sub AcquireConnections(ByVal transaction As Object) 
   If Not (ComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager Is Nothing) Then 
    ' Convert the native IDTSConnectionManager90 to the managed ConnectionManager,
    ' then retrieve the underlying connection through the InnerObject.
    Dim cm As ConnectionManager = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager) 
    Dim cmado As ConnectionManagerAdoNet = CType(ConversionHelpers.AsWorkaround(cm.InnerObject, GetType(ConnectionManagerAdoNet)), ConnectionManagerAdoNet) 
    ' If the InnerObject is not an ConnectionManagerAdoNet, then
    ' the cmado object is null.
    If cmado Is Nothing Then 
     Throw New Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.") 
    End If 
    ' Get the underlying connection object.
    Me.oledbConnection = CType(ConversionHelpers.AsWorkaround(cmado.AcquireConnection(transaction), GetType(OleDbConnection)), OleDbConnection) 
    If Me.oledbConnection Is Nothing Then 
     Throw New Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.") 
    End If 
    isConnected = True 
   End If 
  End Sub
  

  
  

• neoloco

  Correct, I was thinking about regular C# or VB.NET project. The Script Task editor only allows references to assemblies in .NET Framework folder (this is limitation of VSA that we are using).
  
  Why do you need to use pipeline (data flow) assembly in Script Task (i.e. in control flow)
  
  

• Klown

  Sounds to me like you need to add a reference to your project.

  
  
  

• JLucio

  Hi,

  In the Integration Services Project template, I can only Add Reference inside the Script Task. When I did this, there is no Microsoft.SqlServer.Dts.PipelineWrap on the list.

  
  

• Duncan Goodall

  its not on the list.
  

• Suneel

  I would like to have this script task on event handling onpreExecute so that before running the SSIS package, it would check first if all the connections can be established.

  Any help would be appreciated. Thanks.

  
  

• Oster

  The code sample you have is the AcquireConnections method for a pipeline component, so it not really relevant. It would suffer from exactly the same issues as you previous method. Some connections just return a string, that string may or may not be valid for the end use, but as far as the connection design is concerned it is fine. If a flat file does not exist, that can be correct, see the FileUsageType property which tells you more, and how you can validate further should you want to. It has always been up to the consumer of the connection to check things like FileUsageType and the existence or not of a file or folder.

  Some connections return a live object, such as the ADO.Net CM which gives you a real live open connection of the type you specified. Others return a rather inanimate object and it is up to the consumer to know what to do with it. In this case you are the consumer, so you need to do some extra work to check files exist if FileUsageType says they should. You would normally implement this in the Validate method of the task, if writing a proper task for example.

  A connection is not always what you think it should be, string vs some open pipe to an SMTP server, but that is by design, you just need to understand what is actually returned from each connection manager and deal with it correctly.

  
  
  
• question on namespaces