Dynamic Flat File Source Path

Question

I'm trying to pass a dynamic file path to a SSIS package variable. I'm setting the ConnectionString value to the variable value in the connection expressions.

So to get started I set the initial variable ~~name~~ value to products.csv and get the error:

Nonfatal errors occurred while saving the package: The name cannot contain any of the following characters: / \ : [ ] . =

enter image description here

I remove the .csv from the variable name, since I'm passing this value from code anyways - no biggie. But when I pass the appropriate variables and execute the package I get weirdness. I check the db and the expected values from the package are there, but I get a failure status and errors similar to the one I mentioned above.

Below is my code:

GeneralUtilities.ExecutePackage(
                new ListDictionary()
                    {
                        {"ClientId", Client.Id},
                        {"FileName", ConfigurationManager.AppSettings["DownloadLocation"] + @"\" + this.OriginalName}
                    },
                "Products.dtsx");

public static void ExecutePackage(ListDictionary variables, string packageName)
    {
        string pkgLocation;
        Package pkg;
        Application app;
        DTSExecResult pkgResults;
        Variables vars;

        pkgLocation = ConfigurationManager.AppSettings["PackageLocation"] + packageName;
        app = new Application();
        using (pkg = app.LoadPackage(pkgLocation, null))
        {
            vars = pkg.Variables;

            foreach (DictionaryEntry variable in variables)
            {
                vars[variable.Key].Value = variable.Value;
            }

            pkgResults = pkg.Execute(null, vars, null, null, null);

            for(int i=0;i<pkg.Errors.Count;i++)
                Console.WriteLine(pkg.Errors[i].Description);


            Console.WriteLine(pkgResults.ToString());
        }
    }

And here's the output:

enter image description here

It's cool that it's being input into the db, but I don't think the Failure message will work. Seems like a package setting or something that is causing a 'false failure'. Can anyone provide help? Thanks.

enter image description here

The initial issue you have is that you are setting the name, not the value of a variable to products.csv I'll look at your code after my boys are in bed — billinkc
– billinkc, Commented Mar 14, 2012 at 0:19
Mistake typing too quickly. Have edited the question to reflect the setting of the value, not the name. Thanks. — Jeff Borden
– Jeff Borden, Commented Mar 14, 2012 at 0:34
As I read it, you have a template SSIS package that does some stuff and your ExecutePackage method spins up the SSIS object model, modifies package variables based on a match to an external dictionary. You are then executing the package passing those variables back in, yeah? The end goal is make the execution stop returning errors, yes? — billinkc
– billinkc, Commented Mar 14, 2012 at 2:34

billinkc · Accepted Answer · 2012-03-14 17:24:08Z

1

At this point, I think we need more information. I built out a sample package that imports a CSV; defined 2 variables as indicated (ClientId & FileName); configured the package to use a variable for the CM's ConnectionString property. Finally, I used your your code to run the package. I received no errors.

I was not familiar with the Execute method that takes parameters so I tested your code with both versions of pkg.Execute and the results are the same (you can observe this by saving out the modified package as I do so on the final line and comparing them). Before execution, my variables look like

ClientId: 0
FileName: C:\ssisdata\so\so_matt.csv
RowCount: 0

After execution and saving as so_JeffBorden2.dtsx my variables have been modified as expected.

ClientId: 100
FileName: C:\ssisdata\so\so_matt2.csv

RowCount: 10

private static void so_JeffBorden()
{

    string path = @"C:\sandbox\SSISHackAndSlash2008\SSISHackAndSlash2008\so_JeffBorden.dtsx";
    ListDictionary variables;
    variables = new ListDictionary()
    {
        {"ClientId", 100},
        {"FileName", @"C:\ssisdata\so\so_matt2.csv"}
    };

    string pkgLocation;
    Package pkg;
    Application app;
    DTSExecResult pkgResults;
    Variables vars;

    pkgLocation = path;
    app = new Application();
    using (pkg = app.LoadPackage(pkgLocation, null))
    {
        vars = pkg.Variables;

        foreach (DictionaryEntry variable in variables)
        {
            vars[variable.Key].Value = variable.Value;
        }

        pkgResults = pkg.Execute(null, vars, null, null, null);
        //pkgResults = pkg.Execute();

        for (int i = 0; i < pkg.Errors.Count; i++)
            Console.WriteLine(pkg.Errors[i].Description);

        Console.WriteLine(pkgResults.ToString());
        app.SaveToXml(@"C:\sandbox\SSISHackAndSlash2008\SSISHackAndSlash2008\so_JeffBorden2.dtsx", pkg, null);
    }
}

Is there something cut from your sample code that was needed to generate the error? If you run your full code against my sample package, do you encounter errors?

Root cause

The error in the screenshot indicates there is an expression assigning a value to the Name property of an object. Names in SSIS generally follow the .NET rules for naming (connection managers excluded). There are 5 total objects in this package: two variables and a data flow which contains 3 items (I'm not good at math). The variables are not having their name property modified based on the supplied code. The names of the components in the data flow are all the default names which leaves the data flow's name as the prime suspect. Note that the name of the data flow products is an identical match to the value of the variable @[User::FileName] The problem only surfaces when the package executes which is the only time the value of FileName would change.

To test this, in BIDS/SSDT modify the value of variable FileName to be 'doh.txt' and the package should start throwing errors due to bad name.

To resolve this, right click on the data flow and either delete the expression on the Name property or sanitize the string by removing the offending characters in the expression (liberal use of REPLACE will do the trick)

edited Mar 14, 2012 at 17:24

answered Mar 14, 2012 at 3:27

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeff Borden Over a year ago

Thanks for putting that together. Yes it ran fine and showed the package result as 'Success' and there were no errors. I'm an SSIS noob, so clearly there is a problem with how I setup the package. I've added an image to my original question that shows the setup. Is there anything there that tells you what I've done wrong by chance?

billinkc Over a year ago

Pop a screenshot of the control flow level too please

Jeff Borden Over a year ago

Yea, it works now. In my hours of attempts to get the variable file name to take, I must've set the name expression of the data flow to the fileName variable. Ugg, thanks again - I owe you.

Collectives™ on Stack Overflow

Dynamic Flat File Source Path

1 Answer 1

Root cause

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Root cause

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related