Often times when implementing data integration projects – we come across a need to implement a row/key based lookup and based on the found/not-found condition update or insert a row.
I started implementing a simple data flow in SSIS. The idea was simple and has been implemented by quite a few folks and information is available all over the web. Yet when I built the simple workflow, I ran into an issue – the insert operation would fail with the following error – “[Insert Rows [23]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Connection Name” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.”
After trying several different options with package configurations (yes, they are helpful!) and storing sensitive information like password/s with a password – nothing seemed to work until I realized the driver for destination connection I was using. Everything with the connection string, test connection was working except the insert (weird).
I changed the driver from native client to MSFT OLE DB Driver and viola – everything worked as expected. Took me a couple of hours to figure this out. Hopefully it will save you some time…
