Conditional Branching???

Question

Post reply

Conditional Branching???

John-79567

SSCommitted

Points: 1892
More actions
October 13, 2006 at 7:26 am

#114734

I have a question for you guys and gals. The answer may be obvious, so be gentle with this SSIS Nub.

Scenario:

Create a package that connects to several Sql database servers and queries the job history data.

Facts:

The sql server environment is a mix of 2000 sp4 and 2005 sp1.

I can query both versions for job history data, but the query is slightly different based on sql version.

Package uses a ForEach loop to dynamically change the connection string based on an config file.

Solution needed:

I would like to add a conditional branch that directs the process to the correct data flow (query for sql 2000 job history or query for sql 2005 job history).

I guess pseudo code would look like this.

Select Server Name, Connection String, Version from config file

Connect to Server Name using Connection String

If Version = 2000 then run DataFlow SQL 2000

Else Run DataFlow SQL 2005

Thanks in advance.
October 16, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 1

I have to be kind of vague because I haven't done this before. However, a coworker has and this is what I've been told.

Create your ForEach Container with all the steps inside to search for the version. Create a Global package variable and use the container's steps to set it to either 2000 or 2005, depending on the version (or use Yes/No, 0/1, etc.). Edit the precedence constraint leading from the ForEach Container to the next two tasks. Set it to be either Expression or Expression And Constraint. Note: We use the later. It has to be a Success AND the Expression has to = 1 or it has to be a Success AND the Expression has to = 0. It cannot be a Fail in either step.

Anyway, your Expression is going to be @Var = 2000 or @Var = 2005 (or whatever value you're using). Essentially, you're doing a Boolean comparison along with your Success/Fail step.

Does this help?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

John-79567 SSCommitted Points: 1892 More actions · Answer 2

John-79567

SSCommitted

Points: 1892

October 18, 2006 at 7:22 am

#666482

Thanks for the reply. I was able to accomplish the conditional branching by using an empty script task and setting the constraint precedence to Success and @var < 2001 or Success and @var > 2001.

For some reason I couldnt do a comparison like @var = 2005 because @var was a string value. /shrug

Thanks again.

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 3

You're welcome and I'm glad you could get it to work.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.