Another argument for using command source in SSIS

You are building an SSIS package and carelessly create a data flow with a source that connects to a SQL Server table. You simply use the defaults in the OleDB source and select a table name. Then some slacker changes the table definition, not removing or adding columns, but simply carelessly renaming a column, changing the case on some of the letters. What happens? As Nakul Vachhrajani points out (http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/136082/) you will get the dreaded VS_NEEDSNEWMETADATA error.

To summarize Nakul’s point. If you point an OleDB source to a table with a column called IsOrderAGift on my development server, and then deploy the package/project to production where that column is named IsOrderagift, SSIS will think that you have a new column. The problem, as Nakul explains, is that SSIS is case sensitive and SQL Server is normally configured to be case insensitive.

Here is where using a SQL command comes to the rescue. If rather than selecting a table as the source you use a SQL command, you can easily avoid the problem by using the following SQL command:

SELECT IsOrderAGift From tablename

When you move the package to production, SQL Server happily returns IsOrderagift and aliases it as IsOrderAGift. SSIS is happy and you are happy not to get that last minute error right after deployment.

About Russ

Russ Loski is a data warehouse developer focusing on SSIS and SQL Server development. He has worked with SQL Server since 1998. He has used various technologies to move data from one source to others. He has worked for well-known companies such as the Bank of America, the Dallas Cowboys Football Club, T-Mobile and Coca Cola Enterprises. Currently he is developing a Data Warehouse for a large health care organization.
This entry was posted in SSIS. Bookmark the permalink.