Case Sensitive SSIS

Question

In our environments, sometimes the case changes. Id becomes ID and then morphs into id.

When this happens, my SSIS packages (sql server 2008) fail because the column name is not the same case as expected.

Here is a way to reproduce the problem. In your Dev SQL Server, create the following tables:

DROP Table dbo.CartoonCharacters
Go
Create Table dbo.CartoonCharacters
(
    CartoonCharacterID INT,
    CartoonCharacterName VarChar (100)
)

DROP Table dbo.ToonCharacters
Go
Create Table dbo.ToonCharacters
(
    ToonCharacterID INT,
    ToonCharacterName VarChar (100)
)

INSERT INTO dbo.CartoonCharacters VALUES 
 (1, 'Don Duck')
,(2, 'Mike Mouse')
,(3, 'Rog Rabbit')
GO

Create a SSIS package with a single Data Flow Task to which add an OLE DB Source that points to CartoonCharacters and an OLEDB Destination that points to ToonCharactes and connect the green arrows and do the column mapping.

Run the package and 3 rows are transferred.

Now, in SSMS, change CartoonCharacterID to cartooncharacterid (all lowercase).

Try to run the package again. It bombs. :( It says VS_NeedNewMetadata. This is, of course, because the CASE changed. :(

Is there a way to make SSIS package metadata case insensitive?

Answer 1

You can work around changing case on the base tables by getting data from

1. View on the table
2. A SQL statement that lists the columns.