September 18, 2012 at 11:30 am
I am trying to create a SSIS Script Task in SQL 2008 R2 (64-bit) that creates a package. The code is creating the package with a data flow task and a ADO NET (ODBC) Data Flow Source. This code is based on code from this source[/url].
The original code uses OLE DB data flow source and destinations. However, I need to use a ADO NET / ODBC data flow source. I tested the code with OLE DB data flow sources and it works great. However, I can't get it to work with a ADO NET / ODBC data flow source.
I've been Googling this for two days now and can't come up with a fix for this. Can anyone give me some advice on how to fix this?
Thanks in advance!
Here is the code that generates a package with a data flow task that contains a ADO NET Data Flow Source:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Collections.Generic
Imports System.Text
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports System.Data.SqlClient
Imports System.Xml
Public Sub Main()
'To Create a package named [Sample Package]'
Dim package As New Package()
package.Name = "OdbcSourceTest"
package.PackageType = DTSPackageType.DTSDesigner100
'To add Connection Manager to the package'
Dim cmSource As ConnectionManager = package.Connections.Add("Odbc")
cmSource.ConnectionString = "DSN=SQL_TestDatabase"
cmSource.Name = "OdbcSource"
'To add Copy Data to the package [Data Flow Task]'
Dim dataFlowTaskHost As TaskHost = DirectCast(package.Executables.Add("SSIS.Pipeline.2"), TaskHost)
dataFlowTaskHost.Name = "Copy Data"
dataFlowTaskHost.FailPackageOnFailure = True
dataFlowTaskHost.FailParentOnFailure = True
dataFlowTaskHost.DelayValidation = True
dataFlowTaskHost.Description = "Data Flow Task"
' Source ODBC connection manager to the package.'
Dim dataFlowTask As MainPipe = TryCast(dataFlowTaskHost.InnerObject, MainPipe)
Dim SconMgr As ConnectionManager = package.Connections("OdbcSource")
' Create and configure an ADO NET source component.'
Dim source As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
source.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
' Create the design-time instance of the source.'
Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()
' The ProvideComponentProperties method creates a default output.'
srcDesignTime.ProvideComponentProperties()
source.Name = "Timberline Data from Source"
' Assign the connection manager.'
source.RuntimeConnectionCollection(0).ConnectionManagerID = SconMgr.ID
source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr)
' Set the custom properties of the source.'
srcDesignTime.SetComponentProperty("AccessMode", "SQL Command")
srcDesignTime.SetComponentProperty("SqlCommand", "SELECT * FROM [dbo].[TestTable]")
' Connect to the data source, and then update the metadata for the source.'
srcDesignTime.AcquireConnections(Nothing) '<<<<< Code generates error at this point'
srcDesignTime.ReinitializeMetaData()
srcDesignTime.ReleaseConnections()
'Saving the package'
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
app.SaveToXml("D:\OdbcSourceTest.dtsx", package, Nothing)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
Here is the error generated:
SSIS package "Package 1.dtsx" starting.
Error: 0x1 at Create and Execute SubPackage: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Pipeline.PipelineComponentHResultException (0xC020801F): Pipeline component has returned HRESULT error code 0xC020801F from a method call.
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)
at ST_b26158309aa4451b9d382487f4a30d68.vbproj.ScriptMain.Main()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Task failed: Create and Execute SubPackage
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package 1.dtsx" finished: Failure.
September 18, 2012 at 1:56 pm
Well right after posting this question, I managed to figure it out.
I changed this:
Dim cmSource As ConnectionManager = package.Connections.Add("Odbc")
to this:
Dim cmSource As ConnectionManager = package.Connections.Add("ADO.NET:System.Data.Odbc.OdbcConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
And this
srcDesignTime.SetComponentProperty("AccessMode", "SQL Command")
srcDesignTime.SetComponentProperty("SqlCommand", "SELECT * FROM [dbo].[TestTable]")
to this:
srcDesignTime.SetComponentProperty("AccessMode", 0)
srcDesignTime.SetComponentProperty("TableOrViewName", """dbo"".""TestTable""")
July 15, 2013 at 10:25 am
This link might be helpful too...
http://bifuture.blogspot.com/2011/01/ssis-adding-derived-column-to-ssis.html
July 15, 2013 at 10:29 am
...and if you're using OleDB connection managers, the EzAPI code seems to work to create SSIS packages as well.
Here's the latest source on CodePlex:
http://sqlsrvintegrationsrv.codeplex.com/SourceControl/list/changesets
The problem I had with the 2008 codebase for EzAPI is that the class that could create ADO.net connections is implemented as a virtual class, so you'd basically need to roll your own from that class...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply