Can a SSIS package be configured to upper-case all columns?

Question

I don't have much experience in SSIS but was told our database tables will be exported to another system using SSIS. They would like all column-names uppercased (ugh, don't get me started but that's what they want). I'd like to avoid writing custom SQL scripts for each table -- are SSIS packages configurable with transform flags or statements that could uppercase all columns during the export execution?

Answer 1

The data mover process in SSIS is a Data Flow Task. You select a source, any inline transformations and the destination.

You can use a Derived Column Component to edit an existing column so assuming we had columns of NameFirst, NameLast in our buffer, we would create an expression per column to either add a new version of the column to the buffer NameFirstUpper or replace the existing value of the NameFirst column. Either way, the expression becomes

UPPER([NameFirst])

You will have to apply the UPPER function call to every column. Unless you're looking to add some Biml, that's going to be a lot of clicking.

You could save yourself some work by using a custom query per table. Something like the following query will build out your SELECT statements and call the UPPER function (convenient that TSQL and SSIS expression use the same function namefor the call) per column in a table.

SELECT
    'SELECT ' + D.ColumnSelect + ' FROM ' + QUOTENAME(D.SchemaName) + '.' + QUOTENAME(D.TableName) + ';'
FROM
(
    SELECT
        S.name AS SchemaName
    ,   T.name AS TableName
    ,   STUFF(
        (
            SELECT
                ',' + 'UPPER(' + QUOTENAME(C.name) + ') AS ' + QUOTENAME(C.name)
            FROM
                sys.columns AS C
            WHERE 
                C.object_id = T.object_id
            FOR XML PATH('')
        )
        ,1,1, '') AS ColumnSelect
    FROM
        sys.schemas AS S
        INNER JOIN sys.tables AS T
        ON T.schema_id = S.schema_id
)D;

Run that against your source database and you'll get a query per table and it blindly calls UPPER against each column. It doesn't matter if the data type is a guid, number or date, UPPER doesn't care.

Yes, the query could use some of the newer features for string concatenation and aggregation, etc but this works for SQL Server 2005+ instead of just 2012/2017+

Again, you could automate the package design if you want to use a bit of Biml but even a manual package creation should be bad as now you have a Query that does the work for you and your packages become a Source from Query (instead of table) and you know the data coming out is already upper cased. Land that in your destination table and you have a very repeatable process.

Edit

My misunderstand was that the question covered the column contents but instead, the actual column names should be uppercased. The approach of a custom query remains the same, just the technical implementation will change. I moved the TSQL UPPER call from the column itself to just be on the column alias

SELECT
    'SELECT ' + D.ColumnSelect + ' FROM ' + QUOTENAME(D.SchemaName) + '.' + QUOTENAME(D.TableName) + ';'
FROM
(
    SELECT
        S.name AS SchemaName
    ,   T.name AS TableName
    ,   STUFF(
        (
            SELECT
                ',' + '' + QUOTENAME(C.name) + ' AS ' + QUOTENAME(UPPER(C.name))
            FROM
                sys.columns AS C
            WHERE 
                C.object_id = T.object_id
            FOR XML PATH('')
        )
        ,1,1, '') AS ColumnSelect
    FROM
        sys.schemas AS S
        INNER JOIN sys.tables AS T
        ON T.schema_id = S.schema_id
)D
ORDER BY D.SchemaName, D.TableName