Re : Enabling / Disabling Data Flow Tasks

Hello,

I have created around 10 seperate packages for our application data load. Now I am planning to create a master package (or a wrapper package) which will execute all the 10 packages (thru execute package task). Then I have a job which executes the master package at a given date and time.

Question : How can I enable / disable execution of each package within the master package depending upon a flag variable. The reason why I need this mechanism is if the flag = 0 then I don't want all 10 packages within master package to execute and if flag = 1 then master package execution should begin and subsequently execute all packages within that master package.

Thank you

Jatin Shah

I am sure you could find a way to do this using the enable/disable method, however you may find it easier to use precedence constraints. Create a dummy (null-op) task in the parent package that is the first object to run, and connect it to all 10 sub-package tasks using precedence contraints. Double-click each precedence constraint, set the option to 'success and expression' and set the expression to "@.var == 1" (or 0 as the case may be)

Pretty much the same as what you are proposing, except the precedence constraint is being 'disabled' through a supported mechanism, instead of the tasks itself.

|||

Hello Grant,

Thank You for suggesting a way out.

I tried doing what you have explained, but when I try to set the expression as below

"@.flg == 1" it raises an error.

Thank You

Jatin Shah

|||

What's the error message?

You should check this out: http://www.sqlis.com/default.aspx?306

-Jamie

Raw File as Source for Multiple Packages

I have a question regarding Raw Files. I am breaking a large package into more modular components for better processing and debugging.

The process will start with a preparatory dataflow that will create a Raw File(s). This Raw File will then be used as the source in possibly 6 data flows and/or packages.

My question is whether 1 Raw File can be read concurrently by the multiple jobs and how this would affect processing. I'm assuming that this would slow processing.

My other option is to Multicast the writing of the Raw File to 5 other versions of the file. All would be identical except for filename. Obviously this would use more disk space but this is not a concern as we have lots of disk space. Our concern is for speedy processing.

If you have experience with Raw Files, please let me know how you approached this issue. As always, blogs and specific examples are always great!

Thanks in advance.

Why not use a SQL Server table instead?|||

Do you mean to load my candidate items to a dynamically created temp table? And then I can query against this table in successive packages.

What is the process for creating the temp table? I think on my Control Flow palette, I use an ExecuteSQL task to execute a Create Temp table SQL...next a Data Flow Task can then load the table and finally after all Processing packages/flows complete, I use Execute SQL Task to Drop or Truncate the table.

Is this the process or is there a better way?

|||

omegarazor wrote:

Do you mean to load my candidate items to a dynamically created temp table? And then I can query against this table in successive packages.

What is the process for creating the temp table? I think on my Control Flow palette, I use an ExecuteSQL task to execute a Create Temp table SQL...next a Data Flow Task can then load the table and finally after all Processing packages/flows complete, I use Execute SQL Task to Drop or Truncate the table.

Is this the process or is there a better way?

This is the process, but I would like to add two refinements:

If you're going to be dynamically creating and dropping the table, be certain to build the first Execute SQL task so that it has a "IF EXISTS .. DROP TABLE; CREATE TABLE" logic, so it will run correctly regardless of whether the table already exists at run time. (Use the script generation tools in SSMS to build this script.) Set DelayValidation = True for any tasks that rely on the table, so the package can run regardless of whether the table exists.