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?
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
- inserting data into SQL DB from C# ASP.NET
- Fastest way to determine if record exists
- Create Index without name while creating table
- Unrecognized column name that I define
- How to merge 2 or more rows and SUM a column in an UPDATE without a primary key?
- What are the advantages of running SQL Server in a Docker container?
- How do I get the MAX of two values in SQL Server?
- How can I split a single, very long string into separate columns and rows delimited by commas and semicolons in SQL Server?
- Why can't I use table names of aliased tables in WHERE clause?
- Function vs. Stored Procedure in SQL Server
- How to correctly join two tables that each have Start Date and Stop Date columns?
- How to run sql script using SQL Server Management Studio?
- SQL copying data with new foreign keys
- SQL Server truncates decimal points of a newly created field in a view
- SQL Grouping and Nested Concatenation
- How to pull width & height out of string?
- How to delete, re-create, move, import, and export LocalDB databases?
- The locale identifier (LCID) 8192 is not supported by SQL Server
- SQL Server Initial database connection timeout in 30 seconds
- Why use a READ UNCOMMITTED isolation level?
- Recursive CTE and how to overcome circular reference error
- Select min or max based on where duplicate value exists
- Converting accented characters in varchar() to XML causing "illegal XML character"
- Incorrect syntax near '1'. Expecting ID, QUOTED_ID, or '.' Error with SQL Pivot
- Update SQL table using selection from another table
- Clarification on joins
- Foreign key constraint may cause cycles or multiple cascade paths?
- pyodbc cursor.fetchall() is returning "strange" values
- T-SQL Get File Extension Name from a Column
- How to do a cross-database-query join in SQLAlchemy?