connection manager time of day configuration

Question

connection manager time of day configuration

jonathan.matt

Say Hey Kid

Points: 708
More actions
February 1, 2017 at 8:08 am

#340098

Microsoft Visual Studio Premium 2012 Version 11.0.61219.00 Update 5
Microsoft .NET Framework Version 4.6.01055
Microsoft SQL Server Integration Services Designer Version 11.0.3369.0
Microsoft SQL Server Data Tools 11.1.50717.0
We run our warehaouse load twice a day at noon and midnight. One server we pull from is not available at midnight so I wrapped all data flow and execute pakage calls that use that server
in a sequence container. I put an expression to check if it's noon and if not do not run the sequence container.
This did not have the desired outcome ... it appears that the connection manager to the offline server is still validated even though it is not used at midnight and the load fails.
Is it possible to tell SSIS not to validate or try a connection manager based on the time of day the package is run?
Thanks
Jonathan

Viewing 14 posts - 1 through 13 (of 13 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

Change the value of the Connection managers DelayValidation property to TRUE. This will stop pre package execution validation on it, and instead it will be validated when/if it is required.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Jonathan

What happens if you set the DelayValidation property of the connection manager to True? If that doesn't work, you could set the connection string property with an expression that chooses one server at midday and a dummy server (maybe the destination server - shouldn't matter since you're not actually going to use it) at midnight.

John

jonathan.matt Say Hey Kid Points: 708 More actions · Answer 3

Thanks everyone. I should have mentioned this is a Native OLE DB/SQL Server Native Client 11.0 connection. I don't see "DelayValidation" as an option in the designer. Can i set it in the code?

Thom A SSC Guru Points: 99133 More actions · Answer 4

Click your Connection manager in your Connectin manager pane and hit F4. This'll display the Connection Manager's Property pane. On there, DelayValidation will be one of the properties. From recollection, it's near the top.

Edit Added image for aid:

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jonathan.matt Say Hey Kid Points: 708 More actions · Answer 5

There are only two properties visible
Full Name
Full Path

I am googling this now, I will let you know if i find out why the property is not there.

jonathan.matt Say Hey Kid Points: 708 More actions · Answer 6

Sorry my mistake!!! I was not looking from the connection manager pane as you told me! Once I did i found it and set it. Thanks again everyone and i will let you know how it goes tonight.

jonathan.matt Say Hey Kid Points: 708 More actions · Answer 7

jonathan.matt

Say Hey Kid

Points: 708

February 1, 2017 at 8:49 am

#1926123

Thom thanks for the visual! It was what i needed.

jonathan.matt Say Hey Kid Points: 708 More actions · Answer 8

SSIS still tried to validate the connection even with DelayValidation set to true and no control flow or data flow calls to the connection.

jonathan.matt Say Hey Kid Points: 708 More actions · Answer 9

John Mitchell or anybody, could you elaborate on this?

"If that doesn't work, you could set the connection string property with an expression that chooses one server at midday and a dummy server (maybe the destination server - shouldn't matter since you're not actually going to use it) at midnight."

I get the concept but how do i implement an expression for the connection string. I have configured environments for prod, dev etc. with parameters for connection strings but i don't see how i could have an expression like

DATEPART( "Hh", GETDATE() ) >= 6 && DATEPART( "Hh", GETDATE() ) <= 20

which is from my precedence constraint so that the package only runs if its between 6 a.m. and 9 p.m. If i could i would have it hit the destination server for validation outside those hours.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

jonathan.matt - Tuesday, February 7, 2017 8:54 AM
John Mitchell or anybody, could you elaborate on this?
"If that doesn't work, you could set the connection string property with an expression that chooses one server at midday and a dummy server (maybe the destination server - shouldn't matter since you're not actually going to use it) at midnight."
I get the concept but how do i implement an expression for the connection string. I have configured environments for prod, dev etc. with parameters for connection strings but i don't see how i could have an expression like
DATEPART( "Hh", GETDATE() ) >= 6 && DATEPART( "Hh", GETDATE() ) <= 20
which is from my precedence constraint so that the package only runs if its between 6 a.m. and 9 p.m. If i could i would have it hit the destination server for validation outside those hours.

You need conditional logic in your expression. The example below is purely illustrative and probably not 100% syntactically correct, but it should point you in the right direction.

DATEPART( "Hh", GETDATE() ) >= 6 && DATEPART( "Hh", GETDATE() ) <= 20 ? "ActualServer" : "DummyServer"

John

jonathan.matt Say Hey Kid Points: 708 More actions · Answer 11

Would the actual connection strings go in the "ActualServer" and "DummyServer" quotes?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

Spot on, yes... or another expression that evaluates to the connection strings, just in case the server names are already set by variables!

John

jonathan.matt Say Hey Kid Points: 708 More actions · Answer 13

Wanted to give an update on the final resolution of this. John's suggestion of a connection string expression worked well but as i was poking around for another reason, i realized that sequence containers also have a delay validation setting. I set this to true (in addition to the connection manager) and removed the connection string expression. That also worked. So i needed to either make the connection string an expression or set both the connection manager and the sequence container to delay validation.

Thnaks again everyone.

Jonathan