Our requirement is to create a dynamic package from Script task based on the type of connection and table from input variable. Im trying to connect to Netezza with odbc connection using DSN with the below code but its not working. Its throwing exception at source.instantiate(). Our dev server is 64bit and Netezza server is 32bit, so I have set the Run64BitRuntime to False. Please give a solution. ConnectionManager SourceCon = package.Connections.Add("ODBC"); SourceConString = "uid=DBname;pwd=password;Dsn=DSNname"; SourceCon.ConnectionString = SourceConString; SourceCon.Name = "Source"; ConnectionManager SconMgr = package.Connections["Source"]; source.ComponentClassID = "ADO.NET:System.Data.Odbc.OdbcConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"; CManagedComponentWrapper srcDesignTime = source.Instantiate();

Can you check connection to Netezza from outside SSIS. Use a .udl fil to test this connectivity so that once the connection is eastablished you can copy the connection string from the udl file. Check this on how to use a .udl to connect to netezza.http://btsbee.wordpress.com/

Its not working. When I select .NET provider for ODBC, the DSN we have is not listed. Instead it lists NZSQL. I wonder why!

Got it finally. The connection type has to be changed. I tried with the large string which appeared in the Connection type property for the same static connection. The connections.add has to be like the below. ConnectionManager SourceCon = package.Connections.Add("ADO.NET:System.Data.Odbc.OdbcConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"); And the ComponentClassId for ADO.NEt source should be the below. source.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";