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

Custom Paging

Im in the process of trying to teach myself SqlServer, comming from Oracle. How the heck do I get the equivlent of %ROWNUM pseudo-column in SqlServer? Top just isn't doing it for me.

Oracle Example wrote:

Select * from foo where foo%ROWNUM > 10 and foo%ROWNUM <20;

There is no direct equivalent in SQL Server 2000. There is in SQLServer 2005, but that's another story. To mimic it you have toselect into a temp table with an autonumber column.
Here's an example for Northwind (see thefull article):

CREATE PROCEDURE northwind_OrdersPaged
(
 @.PageIndex int,
 @.PageSize int
)
AS
BEGIN
DECLARE @.PageLowerBound int
DECLARE @.PageUpperBound int
DECLARE @.RowsToReturn int
-- First set the rowcount
SET @.RowsToReturn = @.PageSize * (@.PageIndex + 1)
SET ROWCOUNT @.RowsToReturn
-- Set the page bounds
SET @.PageLowerBound = @.PageSize * @.PageIndex
SET @.PageUpperBound = @.PageLowerBound + @.PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
 IndexId int IDENTITY (1, 1) NOT NULL,
 OrderID int
)
-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
 OrderID
FROM
 Orders
ORDER BY
 OrderID DESC
-- Return total count
SELECT COUNT(OrderID) FROM Orders
-- Return paged results
SELECT
 O.*
FROM
 Orders O,
 #PageIndex PageIndex
WHERE
 O.OrderID = PageIndex.OrderID AND
 PageIndex.IndexID > @.PageLowerBound AND
 PageIndex.IndexID < @.PageUpperBound
ORDER BY
 PageIndex.IndexID
END

|||Nurgle! That's even worse than what you have to do in Oracle to get ROWNUM to work! Now, lets say for a moment that im writing against SqlServer 2k5 april CTP. How would i go about emulating it?|||

Try this link. Hope this helps.

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=633&lngWId=5

|||

Now, lets say for a moment that im writing against SqlServer 2k5 april CTP. How would i go about emulating it?

ROW_NUMBER.

|||

ndinakar wrote:

Now, lets say for a moment that im writing against SqlServer 2k5 april CTP. How would i go about emulating it?

ROW_NUMBER.

would you mind elaborating on how to use this?

|||

Check out this article:http://www.codeproject.com/aspnet/PagingLarge.asp. It handles various paging methods you can use. Aso it has benchmarking results given for each method and comparisons between performance of different methods.

|||Check out this tutorial, which also covers the new RANK function as well:
http://www.sqljunkies.com/HowTo/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
|||Thats a good article Darrel. Most of the commonly used commands summed up in one article. Thanks for the link. I was going to point to the Books on line.|||I'm really impressed by the SQL Server BOL in general. For somereason I think it does a better job than the MSDN library. Idon't know if it's the language (SQL vs. C# or VB), me, or what, but itjust works for me.
|||

Microsoft was selling it $100 but was persuaded by Boolean searches to give it away. This was before the Ken Henderson books and it saved the MCDBA program from the brink of disaster. Who came up with the idea to sell product documentation I don't know because product documentation is usually free but resource kit is not free. But in 2003 when told about developers need for SQL Server they listened and dropped the price of the developer's edition to from $450.00 to $49 or less. So every department leave files for Boolean searches you just have to look for them on any Microsoft site.

|||

DarrellNorton wrote:

I'm really impressed by the SQL Server BOL in general. For some reason I think it does a better job than the MSDN library. I don't know if it's the language (SQL vs. C# or VB), me, or what, but it just works for me.

DOH! I knew there was something i was forgetting to register for and to think all this time i thought it was the motorcycle safety course!|||Try this link. Hope this helps.
http://www.richardxin.com/SQLPaging.aspx

Custom Destination Adapter

Hi All,

I have built a custom flat file destination adapter but it appears that the code is not working. When I debug the process I notice that the ProcessInput section is called multiple times. The first time it looks like everything is working, then it call it again and there is no inpout from the DTSInput90.

Why would it do this?

Thanks

Mike

Data moves down the pipeline in buffers. It is not one stream, or individual rows. Obviously buffers have a limited capacity, so you will get multiple buffers depending on the number of rows. The rows per buffer depends on the size of the row essentially.

So ProcessInput gets called once per buffer. This is why you cache buffer information in PreExecute, so that you get re-use. Querying the information can be quite expensive which is why it is cached to start with.

I'm not sure what you mean by "there is no inpout from the DTSInput90", can you explain?

|||Basically what I am saying is that the second time through there is no data. So if you want to capture all the data from the buffer and write it all out in a file how would you do that?|||

That doesn't sound particularly useful, but maybe how it works. Not sure I've ever stepped through to that degree. It should not cause you any problems however, but are you getting all the data you expect? Is there actually a problem?

|||

I figured it out. The detination component takes the input stream and creates a ZIP file with some delimiter that I have defined. I needed to create a global variable for the string and keep appending until then buffers were complete and then run through the ZIP code.

Thanks

Custom Destination Adapter

Hi All,

I have built a custom flat file destination adapter but it appears that the code is not working. When I debug the process I notice that the ProcessInput section is called multiple times. The first time it looks like everything is working, then it call it again and there is no inpout from the DTSInput90.

Why would it do this?

Thanks

Mike

Data moves down the pipeline in buffers. It is not one stream, or individual rows. Obviously buffers have a limited capacity, so you will get multiple buffers depending on the number of rows. The rows per buffer depends on the size of the row essentially.

So ProcessInput gets called once per buffer. This is why you cache buffer information in PreExecute, so that you get re-use. Querying the information can be quite expensive which is why it is cached to start with.

I'm not sure what you mean by "there is no inpout from the DTSInput90", can you explain?

|||Basically what I am saying is that the second time through there is no data. So if you want to capture all the data from the buffer and write it all out in a file how would you do that?|||

That doesn't sound particularly useful, but maybe how it works. Not sure I've ever stepped through to that degree. It should not cause you any problems however, but are you getting all the data you expect? Is there actually a problem?

|||

I figured it out. The detination component takes the input stream and creates a ZIP file with some delimiter that I have defined. I needed to create a global variable for the string and keep appending until then buffers were complete and then run through the ZIP code.

Thanks

Custom Delivery Extension

Hello Everyone,
I'm in the process of creating a custom delivery extension that will
write to a file share and then email users that the file is available.
I have used the custom printing extension sample as the basis for
creating my FileShareEmail extension.
I think my question is unlike most posted here in that, I have gotten
all the config files to work. So far I have set up a few parameters
(email address to and from). I then send an email with smtp.
I successfully receive the email and I created the notification.Status
= "Status: Email Sent.". This is what i see in the log file:
ReportingServicesService!notification!3ffc!11/02/2005-09:42:06::
Notification 7493a45c-2f0b-431c-bb53-592a99a5977c completed. Success:
False, Status: Email Sent., DeliveryExtension: File Share Email,
Report: 01 - Standard Listing Report, Attempt 1
There are no exceptions and the email was sent. The Report Manager
still has Done: 1 processed of 1 total; 1 errors. in the status column.
I cannot figure out why there was an error generated. Is there a
property that i forgot to set?
Another question I have is: I can see my custom extension in data
driven subscriptions but not in the regular subscriptions. Is this by
design or am I missing a config.
Resources question: I've looked quite a few places for some more
examples (other than the printer example), does anyone know some good
blogs or sites that have some different samples implemented?
The fourth and final question: Since I am trying to replicate some of
the existing functionality of RS, is there a way to send the email
through RS instead of coding my own error checking and such into my
extension?
Thanks for your help!
Regards,
DanI figured out one of the 4 questions. My Deliver() method was
returning the wrong boolean value (i.e. true when failed and false when
succeeded).