Custom Task: How to access/modify the variables of a package

Hi,

I'm trying to simplify the deployment process of my project. I already had some troubles with the config files but lets say I solved that issue. I'm going to read a flat file and set the variables of my packages from this file. I was thinking to use a Script Task to do that but I will need to copy this task in every package (I have at least 30). So if I want to make some change this will be painful.

Then, I came up with the idea of creating a Custom Task called Config File Task. I'm working on this but I got stuck trying to get the variables from the package that is running my Config File Task.

This is the code I had in the Script Task:

Dim streamReader As New StreamReader(Dts.Variables("ConfigFilePath").Value.ToString)
Dim line As String
Dim lineArray As String()
Dim variableName As String
Dim variableValue As String
Dim readConfigurations As Boolean = False

While (streamReader.Peek() <> -1)
line = streamReader.ReadLine()
If line = "[CONFIGURATIONS]" Then
readConfigurations = True
ElseIf line = "[/CONFIGURATIONS]" Then
readConfigurations = False
Else
If readConfigurations And line <> "" Then
lineArray = line.Split("|".ToCharArray())
variableName = lineArray(0).Trim()
variableValue = lineArray(1).Trim()

If Dts.Variables.Contains(variableName) Then
Dts.Variables(variableName).Value = variableValue
End If
End If
End If
End While
Dts.TaskResult = Dts.Results.Success

All I want to do is set the variables that exists in each package from the config file. In my UI Class (ConfigFileTaskUI.cs) I can have access to the variables via the TaskHost which is passed as an argument of Initialize() method.

Any thoughts? I'd really appreciate some help!

P.S. I've been working on this for 2 days!

I don't have an answer to your question, but have you looked at using package configurations (SSIS > Package Configurations from the BIDS menu)? these can be stored as simple XML files that can be used for the same purpose as what I think you are trying to do here.

Note that BIDS will not save connection string passwords for you - you would have to open the XML file and edit it manually to add these if you are using it for connection strings.

You can use these XML files to store variable values as well as lots of other properties of your package objects. You could write your own code to manipulate the contents of the XML configuration files however you like.

Another option would be to store your values in a database and fetch the values into the variables using SSIS Execute SQL tasks or a similar method.

Maybe you've thought of all this already and ruled it out for some other reason - but based on your post this would seem a much simpler method?

|||Hi Slicktop,

Yes, I tried the XML config file but you cannot have configurations that don't exists in the package. About the SSIS_Configuration table, I thought of this but it's not a good idea for my deployment process. Somehow, I need to get the information from this table and put it in a flat file so I can store it in CVS. Plus, I need to fill this table every time I deploy my packages. I know this is not so difficult but my client don't want to do this.

There is a property in the Package object which disable the warning messages from the configuration file. If I set to True, I don't get an error message when I have just one XML config file with multiple configurations which they may or may not exists in the package to be executed. I don't like doing this because it doesn't seem right.

Thanks for your ideas.|||

What about just reading the flat file using SSIS? You could either load it into a work table and then query it to set variables, or maybe even use a transform to extract the values you need.

When you say "you cannot have configurations that don't exist in the package", what do you mean exactly? Your variable names aren't defined in the package?

|||That's right!. I don't have the same variables in every package. I have a log file per package so in the flat file I have:

log_package_1 = something
log_package_2 = something
log_package_3 = something
conn_db = connection string to db
file_vpn = path to the vpn file

and so on, so in package 1 I only have declared variable log_package_1 but not the rest of the variables of my flat file. Reading the flat file with SSIS could be a possible option but the thing is I have a custom flat file, I mean it's not comma separated and I have some "tags" so I don't think SSIS can read it.

Thanks for the ideas!|||

well obviously I don't know all the details of your situation but it sure seems like you are trying to do this the hard way.

Is there some reason why your custom flat file has to have a "custom" format?

If your goal is to dynamically populate package variables, and the variable names are defined in the package definition, then you can use package configurations to do this. If your goal is to have only a single flat file that contains all variable values across multiple packages, then why not just write these into a standard, machinable format (XML, delimited file, whatever) and then use SSIS to load the file into a DB table. Then from each package, query the table for the variable values needed for that particular package and use the resultset to populate the package variables.

Your table could be generic, with two varchar columns, var_name and var_value.

so, in package 1, you could write an execute SQL task that runs "select var_value from control_table where var_name = 'log_package_1', etc.

If you put a bit of thought into this you could probably make parts of this solution quite generic so that you could write it once and copy/paste the tasks into each package or nest your packages to re-use it. for example, maybe add a "package_name" column to the control table that you could use to query all variables for a package using the same SQL.

sorry, I know I haven't answered your original question (don't know the answer) but thought maybe there is a better (easier) way to do this.

|||Why are you punishing yourself with such a masochistic task?

I mean, why not just use XML Configuration files?|||

Has the original question been answered?

The Task.Execute method is where you will place the code that reads from the file and sets variables. The execute gives you access to the VariableDispenser object as on of the parameters, in much the same way as the TaskUI.Initialize method does, is this not enough?

The Dts.Variables syntax you have used in the Script Task is not applicable, you need to manually control the locking in the variable dispenser. Look at the Lock* methods as documented for the variable dispenser.

|||Hi DarrenSQLIS,

No, the original question haven't been answered yet. I know that Dts.Variables is not applicable in my case when creating a Custom Task. I checked the VariableDispenser object and there are a few methods. For example, the GetVariables(ref Variables variables) but I don't have the collections of variables to pass it as the parameter for this method.

I think I need somehow to tell SSIS which variables are for read-only access and which ones are for read/write. Even so, I would like to know first the variable name and their respective values first. The VariableDispenser object seems useless because I don't know how to get the variables within the package that is executing my Custom Task.

My goal is to know the name of all the User Variables in a package and then set these variables to their respective values that I'm reading from a flat file.

Using XML configuration files is not a solution, I'm not going to set the SuppressConfigurationWarnings property to True in each package. Probably you think I'm trying to do it the hard way but I have to follow some constraints and standards from my client.

Thank you all for your replies.|||

The methods, GetVariables, LockOnForRead or LockOneForWrite all expect a reference parameter of type Variables. This is just the variables collection that is filled with the 'locked' variables that you have asked for, e.g.

Variables variables = null;
variableDispenser.LockOneForRead(this.SnapshotName, ref variables);
snapshotName = variables[0].Value as string;
variables.Unlock();

From what you're saying though you don't know in advance what variables you want to lock, and that is a problem. There is no way to just ask for a list of all variables. I assume your pseudo config item and the variable name match, so at best you can loop through the config items and ask if a variable of that name exists, using the VariableDispenser.Contains method. If found then lock it and do what you want, but you cannot just eneumerate a collection of all variables in the package to find out what is there.

|||Hi DarrenSQLIS,

Yeah, you're right! My pseudo config item and the variable name match so I can loop through the config items and that's what I did. I wasn't understanding the purpose of the GetVariables() method of VariablesDispenser. After I played with it some time I realized it copies the variables from the package that you lock (read-only or r/w) before to the Variables collection that you pass by reference. Then you can access the variables from this collection and set them. What I don't understand completely is how the variables are return to the package or VariablesDispenser. I'm guessing is after you unlock them.

Well, thank you so much for your tips/ideas/suggestions. I finally finished building my Custom Task as I wanted. It's a shame you cannot access all the variables without knowing the name of the variables ahead. In this case I'm assuming that whatever is in the package is also in the config file but my process will fail if the package has a variable that the config file doesn't.sql