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

▲
1
▼

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'.

sql-server
ssis
etl
bids
asked on Stack Overflow Nov 23, 2016 by Jonathan Porter • edited Nov 23, 2016 by Jonathan Porter

2 Answers

▲
0
▼

Perhaps I am not understanding your question, but it seams like you are approaching this problem backwards. You cannot insert data into columns that do not exist. Is there a reason this column exists in only one out of four versions of this table? And does this column have a default value that you can leverage?

I would continue inserting to the set of columns that are common among all tables and allow the tables with columns that do not exist in every one of the tables in the set to use the default value for that column.

answered on Stack Overflow Nov 23, 2016 by gmiley
▲
0
▼

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

answered on Stack Overflow Nov 24, 2016 by Hadi • edited Nov 24, 2016 by Hadi

User contributions licensed under CC BY-SA 3.0