Making Decision from within Package- HELP -- sql server dts

Cam
3/30/2007 8:50:03 AM

SQL Server 2005 Integration Services with Visual Studio 'Integration
Services' project.

I need the ability to run a package on "Working/Banking" days. I created a
store procedure to look in a table to determine if the date passed (Today) is
a working day or not. It returns Y or N. I marked all federal holidays and
Columbus Day as non-working days.

I can execute the stored procedure with an OLEDB Source object (within a
dataflow task), but cant figure out how to;
1) Stop the task and send an email if today is not a working day, and
2) Continue with the task if today IS a working day (and then send email).

The single column that is returned from the store procedure is "Workingday"
and as I said, it only returns a Y or N.

How can one do an "IF THEN" from within an integration services package.

HELP, HELP, HELP!
THANKS, THANKS, THANKS!!

Cam

Cam
3/30/2007 9:24:19 AM

Thank you Kent!, I appreciate your feedback

Yes, it makes sense, but begin new to Integration Services Projects, I am
not sure how to do what you suggest.

How exactly do you save the results of a store procedure into a variable?

Cam

[quoted text, click to view]

Kent Tegels
3/30/2007 4:15:03 PM

Hello Cam,

Remember that a precedence constraint can be evaluated for both or either
of state and an expression. You might simply want have an SQL Command that
saves your query result into variable, then use that variable in precedence
constraint that controls the rest of the package execution.

Make sense?

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Cam
4/2/2007 9:38:01 AM

Still need help on this one.

I tried an "ActiveX Script" task, but could not get it to work and read
several posts that we should steer away from those types...

I am focusing on "the OLE DB Source" and dont know how to set up User
variable "IsWorkingDay" to the results of the stored procedure.

I have my OLE DB Source sset to "SQL Command" and here is my command text:
_________________________
DECLARE @RC int
DECLARE @Today datetime

set @Today = GetDate()

EXECUTE @RC = [CSPAY].[dbo].[SP_DetermineWorkingDay] @Today
_________________________

All I want to do is set my stinking variable to "Y" or "N" which is being
returned by the Stored Procedure.

Please help fill in the blanks...Thanks

Cam

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : Making Decision from within Package- HELP