I have simple package, which extracts data from DB2 to SQL Server database. DB2 connection uses Microsoft OLE DB Provider for DB2 driver. DB connection string is made as an expression, because solution will have to be deployed to different environments of Integration Services Catalogs (further ICS) where different configurations will be made. For example in TEST environment TestUser will be configured to connect to DB2 test database, and so on with Acceptance and Prod environments.
Solution's ProtectionLevel is set as DontSaveSensitive.
Solution's variables for the connection are declared in project parameters. Password is set as Sensitive, so value is not stored in package and has to be provided by ICS Environment Properties where password value is stored as Sensitive as well.
Connection's expression:
"Data Source=" + @[$Project::DB2DataSource] + ";User ID=" + @[$Project::DB2UserId]+ ";Initial Catalog=" + @[$Project::DB2InitialCatalog] + ";Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=" + @[$Project::DB2NetworkAddress] + ";Network Port=" + @[$Project::DB2NetworkPort] + ";Package Collection=NULLID;"
Which evaluates to:
Data Source=DB2test;User ID=myUser;Initial Catalog=DB2test;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=DB2test;Network Port=99999;Package Collection=NULLID;
Password is provided separately in DB2 Connections properties as Password expression. I've separated it from ConnectionString, because password is set as Sensitive, so ConnectionString expression fails to evaluate, picture: DB2 Connection properties Well, at least I want it to work like that. While developing, I'm changing password as not Sensitive, so I could test the flow via Visual Studio and it works. When deploying to ICS I change password to Sensitive, because ICS environment password variable is set to Sensitive and statuses has to match both in package and ICS environment.
And in ICS flow is not working, I'm always getting some connection errors. DB2 connection variables in ICS env are the same like in solution properties, so values are not the reason of failure. Example of one run errors:
Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DB2_user" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Extract_DB2_Db2PasyIsoExtract:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E73. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E73 Description: "Format of the initialization string does not conform to the OLE DB specification.".
So it looks like there's something with connection string. Maybe password not passed properly? I didn't find proper way how to debug generated ConnectionString via ICS.
Maybe there are some security settings which of I should be aware of? Other ProtectionLevel options are not possible to due specific deployment process. Any comments or approach suggestions are welcome!
I was able to figure out the issue and the short answer is that SSIS package was failing due to Microsoft OLE DB Provider for DB2 driver version difference between environments.
My workstation and TEST environments had a Microsoft OLE DB Provider for DB2 Version 4.0. SYST and PROD had older versions, which didn't event support a parameters in SQL script task. For example SELECT * FROM dbo.Customers WHERE Id > ?
are not supported in older versions of driver.
I was suspecting dynamic connection strings, but they are working as expected.
Simple issue, yet it took quite a while to find out that drivers were different and this caused problems.
Suggestion: for correct connection string use Microsoft Data Access Tool. Its installed with DB2OLEDBV6_x64.msi which part of SQL SERVER 2016 SP2 Feature Pack
User contributions licensed under CC BY-SA 3.0