Rerunning package with Parallel tasks

Hello,

I have done a search and have read some of the posts, but am left more confused than before. I am fairly new to SSIS. Here is my situation and what i am trying to accomplish.

I have a package that has a sequence container, in which there are multiple SQL tasks (about 20) running in parallel. I have checkpoints enabled, and FailPackageOnFailure enabled as well. If the package fails, when i re-run the package it will run the last task as well as all the other tasks. What I am looking to accomplish is when the package is re-run, have the SQL tasks that failed ran and not the previous successful tasks.

I think the best way would be via disabling tasks on successful completion of a task, where it writes the name of the SQL task to a temp table, but I am skeptical.

Can anyone point me in a direction to help me accomplish what I am looking for please.

Thanks in Advance.

As far as best practices in a situation like this I wouldn't know. Not sure how long each SQL task takes but have you thought of making the sequence containter have the transaction property set to required. If the containter fails then it would rollback and wouldn't matter if it everything ran over again.

|||I thought about this, however, this package takes about a day and half to run. Thus if I have to start over again, then I have lost all that time. It is more feasible to re-start the package at the point the package failed...|||You could use what I'll call "hard" checkpointing. That is, before each phase that you'd like to be able to restart from, you issue an Execute SQL task to write to a table. How you implement it would be up to you, but basically something like this:

TASK1 > TASK2 > TASK3 > TASK4

WOULD BE:

SQL (select step from checkpoint table) > TASK1 > SQL (update checkpoint table with new step value) > TASK2 > SQL (update checkpoint table with new step value) > TASK3 > SQL (update checkpoint table with new step value) > TASK4 ........ > SQL (Reset checkpoint table with default value so that next run starts at beginning)

Basically, when you start the package, you select from the checkpoint table the step number contained within. Store this number in a variable. Then, using precedence constraints right before each TASK, you can evaluate that variable to decide if the task should be skipped or not.

When a task completes successfully, it moves on to an Execute SQL task to update the "step" value in the checkpoint table, so that if the next task fails, when the package is restarted it can resume where it left off.

Make sense?|||

Thanks for the reply Phil.

From what you are proposing above, it looks very similar to running packages in sequence, where you have one task run then update a sql table, then move onto the next task. Basically, isnt that the purpose of checkpoints- to log to a file a restart point, thus if you added checkpoints to all 4 tasks, and it failed on task 3 it would restart at 3?

Your idea did get me to think. Rather than use "TASK" in your diagram, what if I used Sequence containers with tasks in it. But it still raises the issue of what happens if a task in the sequence container fails, and when the package is re-run to only execute those tasks that failed (after they have been modified)..

Rerun only certain containers ?

Hi There

I have a package that has many containers that execute in sequence.

If any container fails the package does not fail as the subsequent containers must run.

However if containers fail i do not want to rerun the entire package. Checkpoint restartibility only allows you to start from where your package failed, however my package will not fail and i do not want to start from where it failed but only rerun the containers that failed.

Is this possible ? Can one maybe run only certain containers in a package through dtsexec or another command line tool?

Thanx

Hi,

As for as I know, there is no direct method to achieve this. But, as always, there are some indirect methods. What you can do is, whenever a container completes successfully, in its 'onPostExecute' event, write the container name to a text file. (Note even for a failed job this event will be fired. So make sure you are writing the name only for successful containers'). In the 'onPostExecute' event of your package, if the whole job is successful, clear the contents of the text file.

Whenever you are executing the package, read the contents of the data to a variable. In your package, before each container, put a script task, which should check whether the container name is there in the variable. If it is there, then don't execute it. Otherwise run that package.

|||

Interesting solution, Thiru. I have done something similar in the past, but without using script tasks before each task.

You can create a variable which hold a bitmask as an integer. Each container when it succeeds should add a power of 2 to the variable.

Use a script task at the end of the package to write out the variable to, say, an environment variable. (If you use an environment variable you can easily set the value of the variable again using a configuration.)

Each task then needs a property expression on its Disable property, using a bitwise AND (&) to check if its power of 2 has been added to the variable, for example: (@.MyVariable & 64) == 64

In this way, you only need to manage one variable, and no need to use scripts.

Donald

|||

Wow. That's simple and easy. That's why you are guru and I am a learner.

Thx for your solution.

|||

Hi Donald

Very interesting, i really like this solution.

For now i simply disable all successful contrainers in my config file before i re-run it , but this solution is much more dynamic, i will implement it in the future.

Thanx a million

|||

Donald Farmer wrote:

Interesting solution, Thiru. I have done something similar in the past, but without using script tasks before each task.

You can create a variable which hold a bitmask as an integer. Each container when it succeeds should add a power of 2 to the variable.

Use a script task at the end of the package to write out the variable to, say, an environment variable. (If you use an environment variable you can easily set the value of the variable again using a configuration.)

Each task then needs a property expression on its Disable property, using a bitwise AND (&) to check if its power of 2 has been added to the variable, for example: (@.MyVariable & 64) == 64

In this way, you only need to manage one variable, and no need to use scripts.

Donald

Donald,

I don't know if you're still watching this thread but if you are, perhaps you could explain why your solution here will work in spite of the problem I have reported here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1875701&SiteID=1

[Note that at the time of writing there has been no reply from Microsoft to this thread. By the time you come to read it there may have been]

-Jamie

|||It appears that the configuration is loaded first, then the Disable property is evaluated, then the package is run. In your test package, if you set the variable to 1 at design time, the expression does evaluate correctly, and the task doesn't run. If you do the same thing via a configuration on your test package, you get the same result. I believe the expression is being evaluated, but only once.

Rerun only certain containers ?

Hi There

I have a package that has many containers that execute in sequence.

If any container fails the package does not fail as the subsequent containers must run.

However if containers fail i do not want to rerun the entire package. Checkpoint restartibility only allows you to start from where your package failed, however my package will not fail and i do not want to start from where it failed but only rerun the containers that failed.

Is this possible ? Can one maybe run only certain containers in a package through dtsexec or another command line tool?

Thanx

Hi,

As for as I know, there is no direct method to achieve this. But, as always, there are some indirect methods. What you can do is, whenever a container completes successfully, in its 'onPostExecute' event, write the container name to a text file. (Note even for a failed job this event will be fired. So make sure you are writing the name only for successful containers'). In the 'onPostExecute' event of your package, if the whole job is successful, clear the contents of the text file.

Whenever you are executing the package, read the contents of the data to a variable. In your package, before each container, put a script task, which should check whether the container name is there in the variable. If it is there, then don't execute it. Otherwise run that package.

|||

Interesting solution, Thiru. I have done something similar in the past, but without using script tasks before each task.

You can create a variable which hold a bitmask as an integer. Each container when it succeeds should add a power of 2 to the variable.

Use a script task at the end of the package to write out the variable to, say, an environment variable. (If you use an environment variable you can easily set the value of the variable again using a configuration.)

Each task then needs a property expression on its Disable property, using a bitwise AND (&) to check if its power of 2 has been added to the variable, for example: (@.MyVariable & 64) == 64

In this way, you only need to manage one variable, and no need to use scripts.

Donald

|||

Wow. That's simple and easy. That's why you are guru and I am a learner.

Thx for your solution.

|||

Hi Donald

Very interesting, i really like this solution.

For now i simply disable all successful contrainers in my config file before i re-run it , but this solution is much more dynamic, i will implement it in the future.

Thanx a million

|||

Donald Farmer wrote:

Interesting solution, Thiru. I have done something similar in the past, but without using script tasks before each task.

You can create a variable which hold a bitmask as an integer. Each container when it succeeds should add a power of 2 to the variable.

Use a script task at the end of the package to write out the variable to, say, an environment variable. (If you use an environment variable you can easily set the value of the variable again using a configuration.)

Each task then needs a property expression on its Disable property, using a bitwise AND (&) to check if its power of 2 has been added to the variable, for example: (@.MyVariable & 64) == 64

In this way, you only need to manage one variable, and no need to use scripts.

Donald

Donald,

I don't know if you're still watching this thread but if you are, perhaps you could explain why your solution here will work in spite of the problem I have reported here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1875701&SiteID=1

[Note that at the time of writing there has been no reply from Microsoft to this thread. By the time you come to read it there may have been]

-Jamie

|||It appears that the configuration is loaded first, then the Disable property is evaluated, then the package is run. In your test package, if you set the variable to 1 at design time, the expression does evaluate correctly, and the task doesn't run. If you do the same thing via a configuration on your test package, you get the same result. I believe the expression is being evaluated, but only once.

Required DLLs for SQL7 DTS

I've written a VB6 application that executes a DTS package on SQL7.

The application works perfectly in both Develop and Runtime environments from my system. But when deployed (with default DLLs)to the intended client, the application raises an exception when creating an instance of the DTS object, "Set oPkg = New DTS.Package". The exception says "it can't create and instance of the object" or something to that effect.

While trying to figure out the problem, I installed the application ( without the default DLLs ) on the SQL Server machine. Again, it ran without a problem.

Unfortunately, the SQL machine is not the intended client. All systems here are running W2K(SP2).

Can someone offer some advice?

Thanks,

PaulThere are a couple of rll's you need like dtspkg.rll - the following page should answer your questions:

page (http://www.dbforums.com/archive/43/2002/04/3/356646)|||Originally posted by rnealejr
There are a couple of rll's you need like dtspkg.rll - the following page should answer your questions:

page (http://www.dbforums.com/archive/43/2002/04/3/356646)

Thanks for your response. However I am using a DSN-less connection. So the page above really does not apply.

Any other ideas. My only other option is to incorporate the function of the DTS package into the VB application. I would really like to aviod this option if possible.

Thanks again,

Paul|||Have you included the reference that you are using within your vb project when you install on another machine ? There are probably x number of files that have been left out and/or have not been registered on the deployed machine. I wanted you to see the top half of the article I posted (not the bottom half - about the dsn connection). The top half focused on required files for deployment.

Let me know.

Request for Change form

Does anyone have a sample of this form I could use?
We're moving to a custom CRM package written in .NET and I'll be handing the
maintanence of the CRM. I need to start developing a procedure for request
for changes because currently it's all informal.
The requests would be anything from something small like changing the font
of a field on a page to medium like adding a new field to high of error
messages.
Should all requests be written? in what format?
any direction is appreciated.Hi
Examples of this sort of thing might be found (say) in a book or course
notes for whatever methodology you are using, or possibly if you are using a
bug tracking system it would be a specific status type and a given report
that would produce this.
HTH
John
"ngan" wrote:
> Does anyone have a sample of this form I could use?
> We're moving to a custom CRM package written in .NET and I'll be handing the
> maintanence of the CRM. I need to start developing a procedure for request
> for changes because currently it's all informal.
> The requests would be anything from something small like changing the font
> of a field on a page to medium like adding a new field to high of error
> messages.
> Should all requests be written? in what format?
> any direction is appreciated.

Request for Change form

Does anyone have a sample of this form I could use?
We're moving to a custom CRM package written in .NET and I'll be handing the
maintanence of the CRM. I need to start developing a procedure for request
for changes because currently it's all informal.
The requests would be anything from something small like changing the font
of a field on a page to medium like adding a new field to high of error
messages.
Should all requests be written? in what format?
any direction is appreciated.Hi
Examples of this sort of thing might be found (say) in a book or course
notes for whatever methodology you are using, or possibly if you are using a
bug tracking system it would be a specific status type and a given report
that would produce this.
HTH
John
"ngan" wrote:

> Does anyone have a sample of this form I could use?
> We're moving to a custom CRM package written in .NET and I'll be handing t
he
> maintanence of the CRM. I need to start developing a procedure for reques
t
> for changes because currently it's all informal.
> The requests would be anything from something small like changing the font
> of a field on a page to medium like adding a new field to high of error
> messages.
> Should all requests be written? in what format?
> any direction is appreciated.

Reprocessing Question

I have 5 cubes. All use shared dimensions in the same Analysis Services
database. In a DTS package, I first fully rebuild the dimensions and the
structure, and, no dimension has the changing dimension property.
After all the dimensions are fully reprocessed, I fully reprocess each cube,
but leave off the 'Incrementally Update Dimensions' so it is not checked. The
question is, will the way I'm doing this still allow for the dimensional
changes to be reflected into the reprocessed cubes? I think it will, but I
need to know for sure...
Thanks!
Yup. That will do it. When you do a full process of a non-changing
dimension, all cubes and partitions which use that dimension are reset to
the "unprocessed" state and remain there until you reprocess them. When
reprocessing happens on the cubes and partitions, then the dimension changes
are reflected in the updated cubes.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:1200CE19-A967-41B9-827D-B3EB52AD45FF@.microsoft.com...
>I have 5 cubes. All use shared dimensions in the same Analysis Services
> database. In a DTS package, I first fully rebuild the dimensions and the
> structure, and, no dimension has the changing dimension property.
> After all the dimensions are fully reprocessed, I fully reprocess each
> cube,
> but leave off the 'Incrementally Update Dimensions' so it is not checked.
> The
> question is, will the way I'm doing this still allow for the dimensional
> changes to be reflected into the reprocessed cubes? I think it will, but I
> need to know for sure...
> Thanks!
|||Thanks for the response Dave. That's what I thought, I just needed a sanity
check from someone...
Tom
"Dave Wickert [MSFT]" wrote:

> Yup. That will do it. When you do a full process of a non-changing
> dimension, all cubes and partitions which use that dimension are reset to
> the "unprocessed" state and remain there until you reprocess them. When
> reprocessing happens on the cubes and partitions, then the dimension changes
> are reflected in the updated cubes.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:1200CE19-A967-41B9-827D-B3EB52AD45FF@.microsoft.com...
>
>

Reprocessing Question

I have 5 cubes. All use shared dimensions in the same Analysis Services
database. In a DTS package, I first fully rebuild the dimensions and the
structure, and, no dimension has the changing dimension property.
After all the dimensions are fully reprocessed, I fully reprocess each cube,
but leave off the 'Incrementally Update Dimensions' so it is not checked. Th
e
question is, will the way I'm doing this still allow for the dimensional
changes to be reflected into the reprocessed cubes? I think it will, but I
need to know for sure...
Thanks!Yup. That will do it. When you do a full process of a non-changing
dimension, all cubes and partitions which use that dimension are reset to
the "unprocessed" state and remain there until you reprocess them. When
reprocessing happens on the cubes and partitions, then the dimension changes
are reflected in the updated cubes.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:1200CE19-A967-41B9-827D-B3EB52AD45FF@.microsoft.com...
>I have 5 cubes. All use shared dimensions in the same Analysis Services
> database. In a DTS package, I first fully rebuild the dimensions and the
> structure, and, no dimension has the changing dimension property.
> After all the dimensions are fully reprocessed, I fully reprocess each
> cube,
> but leave off the 'Incrementally Update Dimensions' so it is not checked.
> The
> question is, will the way I'm doing this still allow for the dimensional
> changes to be reflected into the reprocessed cubes? I think it will, but I
> need to know for sure...
> Thanks!|||Thanks for the response Dave. That's what I thought, I just needed a sanity
check from someone...
Tom
"Dave Wickert [MSFT]" wrote:

> Yup. That will do it. When you do a full process of a non-changing
> dimension, all cubes and partitions which use that dimension are reset to
> the "unprocessed" state and remain there until you reprocess them. When
> reprocessing happens on the cubes and partitions, then the dimension chang
es
> are reflected in the updated cubes.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:1200CE19-A967-41B9-827D-B3EB52AD45FF@.microsoft.com...
>
>