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

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

Ole DB Destination settings

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.

Solution

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.

Comments

[HaBo]

For each loop the Source File and Destination table's metadata changes but identical for that Loop-instance. The Problem I am having is When I run the package, First Loop works perfect for the second loop, Destination table Name is still pointing to the First Loop Table Name and throwing error "Metadata not matching" - "Table name or view variable" is not changing from second instance in the loop.

[rvphx]

If your metadata changes from file to file, I am afraid that you cant achieve the effect you are trying for using the same package and logic.

[HaBo]

@RajivVarma So what do you suggest, I have 104 Flat Files, each File is Named with the Table Name each File Fields are different. How should I make this work? Do i have to create a package for each File?

[billinkc]

What are your needs? Is it simply to load all the data from source file to destination or do you need to perform manipulation of the data? If it's a one-time load with no transformations, I'd use bcp. If it's an ongoing process or needs manipulation, I'd use something like ezapi to programmatically create all the packages.