SSIS dynamic columns validation

Question

I'm trying to use Dynamic Column mapping by selecting the destination table using the Variable Name option in the OLEDB destination. I'm getting the error: "OLE DB Destination" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

I understand from what I've read that Dynamic column validation is not possible in SSIS. But then, why is it possible to select table destination in OLEDB using a variable name? Isn't it dynamic column mapping?

What I'm trying to do is to create a foreach loop to read a list of tables and import these tables from the source db to the staging area. Using the Variable Name destination within OLEDB seems perfect to me, but it does not work, even by enabling DelayValidation in the dataflow.

Thanks, Rodrigo

Answer 1

Why would I use a TableName from Variable for my OLE DB Destination?

1. I automate the heck out of my SSIS package development. Instead of having to specify each table name, I have a variable called FullyQualifiedName that I populate once and then reuse for my package. Think of a truncate and reload pattern: Execute SQL Task to clear out the target table, A Foreach loop to load all the files-either because the names are dynamic or I have multiple days worth of data to load, and then Archive the file. I'd need to reference that table at least twice in that scenario. By having the table name in a variable, I can define it once and reference it in many different locations.

2. I have worked in environments where we physically isolate data based on the customer. i.e Blackstone.Sales, Yampas.Sales, Ranger.Sales, etc. When the customer logs in, their account can only access data in their schema. The tables are identical in structure but they have different names to ensure isolation. For a scenario like that, you could be matching file name to target table and therefore want to use a Variable to control what table is written to.

As you've already determined, you cannot accomplish dynamic column mapping in the manner you are attempting. If it's a straight copy from source to your staging environment, I'd just use a technology like Biml to generate the packages and be done with it.

Answer 2

I have faced and worked on such requests. NO, SSIS won't allow you dynamic column mappings. So I had tried something on the lines of below:

1. You need to first use your knowledge of the system and put together a sort of configuration table that would tell you the following things -

   -Source Table(SourceTable)

   -Columns to be extracted from source table(SourceQuery)

   HINT: A SELECT query..e.g. SELECT ID, Name, Salary from dbo.tblEmployee

   -Destination Table(DestinationTable)

   -Columns which need to be fed from the source

   -Few other details like server name/connection properties etc..

You would need to later traverse through the rows of this table using a ForEach Loop container.

2. Next, identify the maximum number of columns and maximum length of data types in these columns, in the source that might be up for extracting. You would need to create a table with information soon.

3. Create a sort of staging table let's say StgData. I will create this table with 50 columns, all of data type NVARCHAR(MAX). The CREATE statement should look like:

   CREATE TABLE StgData
   (                            
   Column1 NVARCHAR(MAX),
   Column2 NVARCHAR(MAX),
   Column3 NVARCHAR(MAX),
   ....        
   Column50 NVARCHAR(MAX)
   ) 
   

The raw data would be loaded onto StgData.

4. Now have a ForEach loop container traversing through ETLMappings. Inside this, you would have to use INSERT statements in Execute SQL Task to load the data.

The script inside the task would look like:-

INSERT INTO dbo.StgData
?

? corresponds to the SourceQuery column(which should be captured by ForEach container.

Once the StgData is loaded, it should be used to load the DestinationTable(also captured in ForEach loop container)

Now again you need to have good understanding on schema and column mapping. The configuration table should have a column which stores the SQL query in the form

INSERT INTO DestTable1 SELECT Col1, CAST(Col2 as float) Col2 FROM StgData

Something on those lines.

This is just a basic structure. Ofcourse lot of formatting and customization has to be added.