none
No errors received when calling SSIS from C# under erroneous conditions

    Question

  •  

    I am new to SSIS, and have created a very simple package.  I am playing around with a C# console app which loads/executes the package, just to familiarize myself with the SSIS runtime.  I wanted to see what sort of errors I would receive if I passed erroneous data to the SSIS package, so I attempted the following:

     

    Code Snippet
    string pkgLocation = "My Package";
    Package ssisPackage;
    Application ssisApp = new Application();
    DTSExecResult ssisPackageResults;
               
    ssisPackage.ImportConfigurationFile("E:\\SSIS_Configs\\My Package BOGUS.dtsconfig");
    ssisPackage.Variables["TestVar"].Value = "test";
    ssisPackageResults = ssisPackage.Execute();
    Console.WriteLine(ssisPackage.Variables["TestVar"].Value);
    Console.WriteLine(ssisPackage.Variables["RecCount"].Value);
    Console.WriteLine(ssisPackageResults.ToString());
    foreach (DtsError pkgerror in ssisPackage.Errors)
       {
       string err = "Error Source: " + ssisPackage.Name + ": " + pkgerror.Source + " Description: " + pkgerror.Description;
       Console.WriteLine(err);
       }

     

     

     

    The line where I'm importing a configuration file works fine if I use a "real" configuration file, as expected.  For testing error handling, I put in a "bogus" file name, but no errors occur!  The package still runs (using the default configuration file).  This isn't critical, but why would I not get some sort of error trying to run ImportConfigurationFile with a bad filename?  The setting SuppressConfigurationWarnings is set to False.

     

    Secondly, and more importantly, the package variable called TestVar is defined in my package as int32.  I wanted to see what kind of error occurred if I passed a bad value to it, so I set it to the string value "test" prior to execution.  No error happens here either...the package continues merrily along and runs.  I can tell it runs because it only contains one task (sql task to count records in a table and write the count to the variable RecCount).  I'm writing the RecCount variable to the console and can see that it contains the expected value.  In addition, I'm writing the value of the TestVar variable out to the console after the package executes, and it contains "test" even though it is defined as an int32 variable.

     

    Maximum error count for the package is set to 1.  Any clue why I don't seem to be generating errors, particularly in the second case where I'm setting a variable to a value it should not accept?

     

     

     

    Monday, November 17, 2008 10:15 PM

Answers

  • In the first case, as indicated by the property name, configurations never generate errors only warning so you would have to look at the warnings collection to see the configuration warning you received for the bad filename.  As for the second "error" variable type is only fixed at execution time.  Since until you call execute you are in design time you can set the variable and it will change its type based on what you set it to be (unless you set it to read only but then you won't be able to change the value either).

     

    Matt

     

    Tuesday, November 18, 2008 12:01 AM
    Moderator

All replies

  • In the first case, as indicated by the property name, configurations never generate errors only warning so you would have to look at the warnings collection to see the configuration warning you received for the bad filename.  As for the second "error" variable type is only fixed at execution time.  Since until you call execute you are in design time you can set the variable and it will change its type based on what you set it to be (unless you set it to read only but then you won't be able to change the value either).

     

    Matt

     

    Tuesday, November 18, 2008 12:01 AM
    Moderator
  • Thanks for the answer.  It helps to know that I'm in "design time" when changing variables, but surprising that it lets me change a variable type once I've defined it explicitly.  Or once I've set it to a value, it continues to let me set it to different values of different data types...like this:

    Code Snippet

    ssisPackage.Variables["TestVar"].Value = 130.45;

    ssisPackage.Variables["TestVar"].Value = "test";

     

     

    No need to respond...I'm just surprised it lets me do this!

     

    And thanks for the tip on how to see configuration warnings.  I had to do one other thing...should probably have been obvious, but I didn't do it correctly at first.  I had to place the check for package warnings right after the import of the configuration file...(I originally tried it after the package.execute).  This code now returns the warnings:

     

    Code Snippet

    ssisPackage.SuppressConfigurationWarnings = false;

    ssisPackage.ImportConfigurationFile("C:\\My Package.dtsconfig");

    foreach (DtsWarning pkgerror in ssisPackage.Warnings)

    {

    string err = "Warning Source: " + ssisPackage.Name + ": " + pkgerror.Source + " Description: " + pkgerror.Description;

    Console.WriteLine(err);

    }

     

     

    Tuesday, November 18, 2008 3:10 PM