SSIS and MERGE statement

I encountered an error while working on a DW/ETL project.

If you are using dynamic connection manager settings in your SSIS packages, you some how set the connection string as follows

Provider=SQLOLEDB.1;Initial Catalog=MYDB;Data Source=(local);User ID=myuser;Password=myPassword;

Well this works just fine as long as you are NOT using any of the SQL Server 2008 new features.

In my case, I had to use MERGE statement and above connection string did not work. After spending couple of hours, I found out the issue. You have to make sure that, new provider will be used in your connection string. The connection string should look like this.

Provider=SQLNCLI10;Initial Catalog=MYDB;Data Source=(local);User ID=myuser;Password=myPassword;

Hope this helps....