I'm looking to use an SSIS Script Task to dynamically build an external SSIS package using Data Flows with DataDirect ODBC source and destination connections. What references and syntax need to be applied (non-working e.g. below)?
Dim package As New Package() package.Name = "OdbcSourceTest" package.PackageType = DTSPackageType.DTSDesigner100
Dim cmSource As ConnectionManager = package.Connections.Add("Odbc") cmSource.ConnectionString = "DSN=DataDirect_ODBCsource;DB=DBsource;UID=user;pwd=password" cmSource.Name = "OdbcSource"
Hi John, you can pass the ODBC connection string as follows: for any DataDirect ODBC driver:
cmSource.ConnectionString = "DSN=DataDirect_ODBCsource;UID=user;PWD=password"
What driver(s) are you looking to use?
Are you running into errors with above aporoach?
Check out my world famous blog on data connectivity
Hi Sumit, I have Sybase ASE server source and a SQL Server 2012 destination. I'm wanting to bulk copy 90GB of data from 300 tables using the DataDirect ODBC drivers for Sybase and SQL server to complete the task. Using the drivers via the native ODBC provider in SSIS is extremely fast, however building the packages is not because the SSIS Import/Export Wizard doesn't seem to expose the provider, and a Data Flow Task can't be re-used dynamically. Is there an easier way to achieve this (and maintain performance) rather than creating SSIS Data Flow Task source/destinations for each table?
You should be able to specify the DataDirect ODBC provider in the Import/Export Wizard as".Net Framework Data Provider for Odbc"
Will that work for you?