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, Jun 12 '12 at 00:25

score 1 · Accepted Answer · edited May 23 '17 at 12:19

1

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.

edited May 23 '17 at 12:19

Community

1
1

answered Jun 12 '12 at 14:03

billinkc

59,250
9
102
159

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. – HaBo Jun 12 '12 at 14:32
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. – rvphx Jun 12 '12 at 15:11
@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? – HaBo Jun 12 '12 at 15:34
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](http://sqlsrvintegrationsrv.codeplex.com/releases/view/21238) to programmatically create all the packages. – billinkc Jun 12 '12 at 16:06

SSIS Ole DB Destination Dynamic Table Name

Error Message:

1 Answers1