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?
1 Answers
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.
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:
- SSIS OLE DB Source: SQL Command vs. Table or View
- Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View
Similar Issues
Related