Logical Error: using expressions & package variables

Hi,

I have encountered some strange output when testing an SSIS package I've been working on.

Please see this thread for background information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1590716&SiteID=1. The previous thread was resolved and i have used the tips listed here to create a package with Data Flow Tasks that use expressions to create 'dynamic' queries.

The Data Flow tasks use two package-scope variables to determine the date range that they're operating on - this allows me to script the from / to dates (usually 'last week' range) or, in event of error, 'back date' the job easily without having to update each individual components.

However, something weird is happening - when I have two data flow tasks (DFT) in the same Sequence Container (tasks that are not 'connected' or configured to operate in a flow) only one DFT is picking up the package variable & returning the rows. The other DFT is not returning any rows & seems to be using the default value of the variables.

I have tested this by simply enabling DFT A and disabling DFT B - debugging shows only one DFT executes correctly. The same point is illustrated when I disable DFT A and enable DFT B. However, if I configure DFT A to run after DFT B then both pick up the correct variable values and return the correct row counts!!

I want Data Flow Tasks that pull data from the same remote source to be the same sequence containers so when an error occurs I can get container level transaction support.

Note:

I have thoroughly checked the 'evaluated' expressions in IDE and in Management Studio - this is an SSIS specific quirk (or me!).

Questions:

What's happening here, have I made a conceptual error of how this should work? On the same variable topic: is there a way to set values for package level variables at execute time .e.g via command line execution / dtsexecui?

Let me know if you need any extra explaination / diagrams etc to understand this, the other (closed) thread I have listed above should provide more context for my problem

Thanks for your support!!

You can set any property on any object in the package from the command line, using the same syntax as you see with configurations. See the /SET syntax, e.g.

/SET \package\DataFlowTask.Variables[User::MyVariable].Value;newValue

I'll think about the other bit....

|||

Hi Darren,

Thanks for your response & useful command line syntax. Slight update on my posting, this happens, not only with Data Flow Tasks in a single Sequence Container but accross seperate Sequence Containers.

For example I have 5 sequence containers with 2-3 data flow tasks in each, unless all the sequence containers 'flow'/'connect' to each other (on success) then the variable value never seems to get read properly. I see no reason why the containers should be linked to execute correctly.

Admiteddly, the impact of this is minimal: if one sequence container fails then the whole package has failed & it should backs out & notify an admin - it's still a weird occurrence tho!

Question:

I've seen 'container level' transaction management - my package is 'read only' in the sense that it reads from remote endpoints and writes to a local db, it never alters the remote DB's. Container level transactions aren't an option for me as the package seems to want transaction control over the remote data sources (i guess important if you've done remote db insert and updates). I can't get network access to the remote machine's DTC service.

Is there a global 'on package error' event that I can hook into my package where i can run some sql to truncate all local data that was created created during a failed/partial run?

Thanks for your help