Search code examples
sql-serverssisetlssis-2012

How to add new column in SSIS Package. When the columns are at max?

I have a package with 600 columns, and now I need to add a new column to the package. But SSIS is telling me that I reached the maximum limit of columns. Is there any way I can add the new column?

Solution

  • Before thinking about adding a new column, you should think about why you have 600 columns in a single package!!

    When working with a data source with a considerable number of data columns, you should ignore the useless columns or merge them into one Jumbo column (comma-separated values, JSON, XML...).

    Example 1 - Flat File

    In case that your data source is a flat file and that only the first few columns are useful, you can go to the Flat File Connection Manager > Advanced Tab and minimize the number of columns.

    enter image description here

    Example 2 - SQL Server source

    If your data source is an SQL database, you can customize the columns you need to merge using functions like CONCAT_WS() (Concatenate with separator). As an example:

    SELECT [Co1], [Col2], [Col5], CONCAT_WS(';',[Col3],[Col4],[Col6]) as [Jumbo1], CONCAT_WS(';',[Col7],[Col8],[Col9]) as [Jumbo2]
FROM MyTable

    Side IMPORTANT note: If you use an OLE DB source, do not ignore columns by unchecking them from the OLE DB Source editor. Use a SQL command instead. I highly suggest reading the following articles:

    Similar Issues