SSIS Ole DB Destination Dynamic Table Name

Question

I have set of flat files (114 files) each file is named with database table name. I have database schema, So my database is ready with no records in it.

All that i have to do is, process these flat files and insert those records in respective tables.

I created an SSIS package, I am trying to set Dynamic table name in Ole DB destination.

I have used variable to set Dynamic table Name.

this is how my package is enter image description here

In script Task, I am setting value for variable

 public void Main()
        {
            // TODO: Add your code here

            string _path = string.Empty;
            if(Dts.Variables["FilePath"] != null)
                _path = Dts.Variables["FilePath"].Value.ToString();
            //MessageBox.Show(_path);
            //Variables vars = default(Variables);
            //Dts.VariableDispenser.LockForWrite("TableName");
            //vars["TableName"].Value = _path.Replace(@"C:\Users\GD\", "").Replace(".txt", "");
            Dts.Variables["TableName"].Value = _path.Replace(@"C:\Users\GD\","").Replace(".txt","");
            MessageBox.Show(_path + Environment.NewLine +"TableName: " + Dts.Variables["TableName"].Value.ToString());
            Dts.TaskResult = (int)ScriptResults.Success;
        }

the messageBox gives me the expected data. But Ole DB Destination table is not recognizing the table name from second loop.

Variables enter image description here

Ole DB Destination settings enter image description here

Error Message:

[OLE DB Destination [55]] Error: Column "ActiveCompositionGenericID" cannot be found at the datasource.

"ActiveCompositionGenericID" this is column in First destination Table and First Source File.

For Second File in the Loop this is not expected in Source File (which is happening), and this should also not be in destination table.

This error message confirms me that, Destination table is still pointing to First table name, and it hasn't changed.

@RajivVarma Problem I have is, First loop will work correctly. For the second loop Ole Db data Source "Table Name" is still pointing to the First loop table and throwing an error saying Source File Columns does not match with Destination Table. is there any way that i can debug the Ole db destination table name? — HaBo

billinkc billinkc · Accepted Answer · 2012-06-12T14:03:02

If the structure of those files and the destination tables are identical then what you are attempting to do will work. Otherwise, the first file(s) will load but once the metadata changes in the input file, the process will fail.

The above pattern would work fine back in DTS but is not valid for SSIS.

SSIS Ole DB Destination Dynamic Table Name

Error Message:

1 Answers