Search code examples
sql-serverssisetlbids

How to add null value if a column doesn't exist

I have a package in my ETL that loops through 4 different databases, each with a copy of the same table. I found out that one of the four tables has one extra column named MTFvalue_Permit thus giving me the below error. My fact and staging tables, however, do include this column.

I was hoping ssis would just insert a null value if the column didn't exist. How can I add a null value into my Data Warehouse tables for the other three tables in which this column doesn't exist?

Error:

[Source DB IBS [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Invalid column name 'MTFvalue_Permit'.".

Edit: I'm thinking about using a case statement like this for the query but I'm getting a different error.

SELECT 
[blahblahblah] ,
[blahblahblah] ,
[blahblahblah] ,
[blahblahblah] ,
[blahblahblah] ,
CASE WHEN COL_LENGTH('wmManifests', 'MTFvalue_Permit') IS NOT NULL
     THEN [MTFvalue_Permit]
     ELSE NULL
END AS 'MTFvalue_Permit'
FROM dbo.wmManifests

Invalid column name 'MTFvalue_Permit'.

Solution

  • I don't think that you can do it using expressions.

    You can do this workaround.

    • Add a Script task that list columns from the table

    • Build the Select query dynamically:

      If the column is not found pass NULL instead of the column name and give an alias (same as column name)

    • save this query string into a SSIS variable and use it as a source

    You can also create a stored procedure that generate the query string. Execute it from a sql task and store the query string into ssis variable