Hi there. Is it possible to programmatically discover at the start of a package (in the package itself) if a configuration file has successfully been read? I would like to be able to fail a package before it runs if the specified xml config file couldn't be used by it. Thanks.

That is a good idea. Although there is no canned mechanisms in SSIS to figure this out other than examining the execution log (a warning with "failed to apply configuration" wording would appear in it) I would solve it like this: Define a config value assigned to a package variable, e.g. ConfigType. Let's give it a value of 'DEV' in while the package is say in BIDS, and we expect it to be PROD when deployed. And another one say called ExpectedConfig. Let's give it a value of DEV, too. This is for dev. For prod let's set the ExpectedConfig to PROD while still in DEV/BIDS before deploying. So when we run the package we can hit a Script task as the 1st element in it in which we would simply inspect the variable contents, if it is PROD and we expected PROD then we are OK, otherwise fail the package. Arthur My Blog

You use the words "programmatically" and "at the start" and "(in the package itself" my experiance the answer is NO, but what i would do is i will set the variable in SSIS that is been used in the XML Config file to a dummy and wrong value so that is the package did not pick up the XML cofig value, it will selec the value from SSIS, which is the bad value that wil raise an error ie. maybe you have a SQL connection in your package, a variable called uVar_SQLDestinationServerName in set to the server name on the SQL connection, set the variable to "BlaBlaBla" and in the XMLCOnfig file set the righ name "MySQLSerName", so if the package dowsnt pick up the XML file is will be looking for SQL Server name "BlaBlaBla" and it will fail. SECOND QUESTION You mentioned "I would like to be able to fail a package before it runs" well before that you said ... " (in the package itself)" this is not possible ,it can only be one or the other. Third question you said ..."I would like to be able to fail a package before it runs if the specified xml config file couldn't be used by it" A: use a Master Package Frame work to call you r package within the master package call your child package through a SCRIPT TASK using .net code set your package like .. Dim app As New Application Dim pkg As Package = app.LoadPackage(ChildPkgPathAndFileName, Nothing) run and fail your package like ... Dim pkgExecResult As DTSExecResult pkgExecResult = pkg.Execute() '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' Capture error Call CaptureSubPackageErrors(pkg, pkgExecResult set your config like ... pkg.ImportConfigurationFile(XMLConfigFileNameAndPath) add functions to check if your variables in the XML config file (and scope) match your package by using .NET if not fail the RUN before running. Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Nik, is it not possible to have a script task in the package and check in there if a configuration file has been loaded at all? I thought it would be possible through the objects exposed in the package.Darek

Arthur, I think I'll first give a go to your solution. Even though I would be much happier if I could discover the status of a config file load using .NET.Darek

Nik, is it not possible to have a script task in the package and check in there if a configuration file has been loaded at all? I thought it would be possible through the objects exposed in the package. Darek Darek, in SSIS 2012 (or actually even any SSIS) you can do a different approach [may be] how about you apply a config forcefully? E.g. in SSIS 2012 you can have done in code, but externally, and in others using DTEXEC /Config option?Arthur My Blog

Well, if that's not possible using .NET from inside the package itself, then I don't really have a choice :(. I'll have to use dummy variables somehow. But that's not very elegant and introduces another complexity into the package, already complex enough.Darek

You need to be aware that a package skims through the config prior to making itself into the execution mode, thus making the .Net code idea irrelevant. Typically, what is happening in case the wrong config is loaded? The package fails, not a big deal, right? And it becomes quote unpleasant when a package loaded the wrong values, this is what you should be primarily concerned about, not the config file as such.Arthur My Blog

The package shouldn't run if a config file couldn't be loaded. This is what I need really. But I'll do it this way. I'll set up a dummy variable in the package and assign it a value. A config file will have a different value for this variable. It is then easy to check which variable's value has been used and therefore will know what's happened. I think this is the easiest solution. To answer your question... It's not as much about loading an incorrect file as about not loading a file at all. The files I have follow a naming convention and there shouldn't be any problem with them having incorrect values (but can happen as well, of course) when loading production or test configurations through them. Incorrect values is a minor concern in my environment because there actually is not much scope for the values to be incorrect :) Thanks, Arthur, for your patience and time.Darek

If your primary objective is the ability to load the config file then: 1st task of the package is the Script task where using System.IO you check if the config file exists, if not, fail it with code Dts.TaskResult = Dts.Results.Failure Arthur My Blog

Well... That's not that simple. I don't know the name of the file until runtme, right? Is there a way to retrieve the name of the file (even if loading it has failed) in the package? Even if there is, the file could be accessed through IO and still not loaded. At any rate, when I think about it more and more, it seems that the trick with the dummy variable is the most straightforward and easiest one and gives me exactly what I want.Darek

I think you would ever need to care or bother to change the config file name after you had created it, so you can simply hardcode it. I am not aware of any code that would permit you to retrieve the config file name. With the variables, yes, like I said, I would solve this using that approach myself, but the package maintenance becomes a tad more complex, especially if somebody inherits it later.Arthur My Blog

Nik, is it not possible to have a script task in the package and check in there if a configuration file has been loaded at all? I thought it would be possible through the objects exposed in the package. Darek again you mentioned "I would like to be able to fail a package before it runs", the answer then is NO but can we see if a XMLConfig file have been set I am going to say yes, but was all the variables used ( because you can have a variable in XML config file with the wrong scop) , I am going to say "As far as i know, NO", and i dont think you can , but out side the package by using a master package , you have more control, to see and check and compare, variables and settings Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Well, if that's not possible using .NET from inside the package itself, then I don't really have a choice :(. I'll have to use dummy variables somehow. But that's not very elegant and introduces another complexity into the package, already complex enough. Darek dummy variables , dummy files and dummy data , this is very common specially in the design of SSIS, and mainly it helps the developerSincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

You need to be aware that a package skims through the config prior to making itself into the execution mode, thus making the .Net code idea irrelevant. Typically, what is happening in case the wrong config is loaded? The package fails, not a big deal, right? And it becomes quote unpleasant when a package loaded the wrong values, this is what you should be primarily concerned about, not the config file as such. Arthur My Blog excelent pointSincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Thing is, I wouldn't like to use a Master Package. I think it's even more hassle than with one dummy variable. The value of the variable will have to be set to one and the same value in each and every config file, so this is not actually a maintenance nightmare. And it will be clearly stated in the documentation. I cannot but wonder how it could be that you are not able to determine in the package itself whether a config file was successfully loaded or not... When you think of it it is such an obvious thing... To clarify things: When I say "before it runs", I really mean "before it has a chance to execute past the executable that would determine if the config file has successfully been loaded".Darek

before run time you can use pkg.Configurations.Item(0) use a loop to check each ITEM of xmlfile set for the child package OR ------------------------- within the running package , let me find it, i'll post if it exists Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

I cannot but wonder how it could be that you are not able to determine in the package itself whether a config file was successfully loaded or not... When you think of it it is such an obvious thing... To clarify things: When I say "before it runs", I really mean "before it has a chance to execute past the executable that would determine if the config file has successfully been loaded" because when designning we have local variable names like MySQLservername in the variable of SSIS , when deploying we never chnage the SQL server name within the SSIS package, we allow the SQL Server name to be changes through the XML files, so if you deploy the packages without XML config files or the files are been set wrong or what every .... the package will fail and the first thing that we always check is config. thats why we dont need to determin to see if the XML config file have been set correctly or not.Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Nik, don't you think it would be nice to be able to discover whether a variable's value comes from the package or a configuration file??? I think IT WOULD.Darek

Nik, don't you think it would be nice to be able to discover whether a variable's value comes from the package or a configuration file??? I think IT WOULD. Darek you are right but as I had mentioned if all the values in the SSIS been set according to my computer setting (i.e., MySQLServername , MYDBName, MyFolderName, etc...) and after that the package runs on the customers Production server (without the right config settings), the package will fail because it will be looking for my settings i.e. MySQLServername, and that is when it raises the right error I think what you need is Variable tracing, this will help you to trace down the variable changes and you can save the changes into a text file. In my case I have variables for SQL server name, Excel file name, excel sheet name and etc... I have set the "Raise event variable value changes" for all the variables that I need, MAINLY MOST of the variables are been set in an xmlConfig file. Then on each change like sqlservername change, excel file by each loop, or excel sheet file, I capture the changes in OnVariableValueChanges You can check this section and make your package fail if you like, but what I I capture the changes in a text file so I know what has happened in my package from a trace point of view.