I am brand new to this. I would like a package to do the following. In the DataFlow task, I would like to evaluate if the table has the most recent indicator set for the month. If yes, I want to exit the entire package. If no, I want to update the table, run several procedures and then execute a package task at month end. I think I need to add Failure and Success contraints but am unsure of where to evaluate them .
My query is doing the following: If I get a row back, I will do nothing. That means my current month is set to 0 as it should be. But I want to evaluate if @@ROWCOUNT > 0 THEN update the table, run the procs, run the package for the end of the month.
Once my SQL runs in the DataFlow task, what do I do then in the package?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Here is an example of a precedence constraint. You will want to evaluate your indicator and populate a variable. I am also attaching my instructions on how to populate a variable. You will want to use the one designed to work with an Execute SQL Task.
Precedence Constraint
I don't think a cursor is supported in the Execute SQL Task, so ... reading back up top to your original question. What is the minimal SQL Statement you need to run in order to return the indicator. But just to be clear, what is the SQL Statement you were trying to execute? Cant create a recordset in the SQL Task either.
Exactly. So you create a variable, DAY_OF_MONTH, Int32 Data Type. You run an Execute SQL Task with the Result Set to Single Row. The following SQL or whatever you need.
SELECT DATEPART(dd,GETDATE())
On the Result Set tab map Column 0 to your variable and then you have the means for a precedence constraint.
@[User::DAY_OF_
That will solve that step. Next?
[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Variables:
You created the variable in the Variables window. You defined it's value by Expression. When you use that variable as part of a precedence constraint you have to type it in manually, no drowpdown or intelligent assist.
@[User::YourVariabl
We can work on the SQL Task Error tomorrow. Paste your SQL Statement here. Verify it does not return more than 1 row of data.
In your original script, all this:
DROP TABLE #CurrentMonth
SELECT * INTO #CurrentMonth
FROM
(
SELECT MonthStart, MonthEnd, MonthInd
FROM tblMonth_Quarter M
WHERE @TimeKey BETWEEN MinMonthKey AND MaxMonthKey AND MonthInd = -1
)t
-- if it is not the curent month any longer, I want to set the MonthInd to -1.
IF @@ROWCOUNT = 0
BEGIN
can be replaced with
IF EXISTS ( SELECT 1 FROM tblMonth_Quarter M
WHERE @TimeKey BETWEEN MinMonthKey AND MaxMonthKey AND MonthInd = -1
)
BEGIN
unless you aer using #CurrentMonth elsewhere
and just FYI it is not necessary to do this in a package, it can all be done in T-SQL.
Have fun anyway.
Hi Hoggzilla! Just getting back to it. Not far, to answer your question, I was out Friday. I have the first SQL Task executing. It updates tables if necessary. I didn't figure out the variable though. What I want to do now is have the second SQL task look to see if it is the first day of the period by coding. if so, execute the three procs.
DECLARE @DT DATETIME, @MonthStart DATETIME, @MonthEnd DATETIME
SET @Dt = Convert(DateTime,Convert(V
SET @MonthStart =
(SELECT MAX(MonthStart) FROM tblMonth_Quarter M WHERE MonthInd = -1)
SET @MonthEnd =
(SELECT MAX(MonthEnd) FROM tblMonth_Quarter M WHERE MonthInd = -1)
IF @DT = @MonthEnd + 1
BEGIN
EXEC spPartD_DrugCard @MonthStart , @MonthEnd
EXEC spPartD_FirstOrder
EXEC spPartD_Unique_Bucket @MonthStart , @MonthEnd
END
Then go on to run other packages.
GO
>>DECLARE @DT DATETIME, @MonthStart DATETIME, @MonthEnd DATETIME
SET @Dt = Convert(DateTime,Convert(V
SET @MonthStart =
(SELECT MAX(MonthStart) FROM tblMonth_Quarter M WHERE MonthInd = -1)
SET @MonthEnd =
(SELECT MAX(MonthEnd) FROM tblMonth_Quarter M WHERE MonthInd = -1)
IF @DT = @MonthEnd + 1
What you will want to do is fill a variable with a Yes or No to continue. So in your execute SQL Task you would have something like this:
SELECT CASE WHEN @DT = @MonthEnd + 1 THEN 'YES' ELSE 'NO' END
Then in your result mapping, add a variable to receive this result. I have information about this in the doc you have. Let me know if you have trouble.
Then you create a Precedence Constraint between this Execute SQL Task and a Sequence Container which contains your Execute SQL Task or Tasks for DrugCard, FirstOrde and Unique_bucket sp's.
You actually only need one variable, something like User::Answer. Then you will populate it with a Yes or No value. That variable is then the determining factor for your precedence cosntraint.
Sample Precedence Constraint
You don't have to use a sequence container, but you can. If you do, then yes, put the Execute SQL Task inside.
To check on variable values, add a breakpoint and when it breaks, go to the locals window. It will be available when the package breaks.
I choose the task to break on, right click and select edit breakpoints. On Post Execute.
Breakpoint
Select the breakpoint option
Locals window on Break
Here is wher I am at. I am not seeng my variables in the local window. The 2nd Execute task processed. Even when I reversed the logic, I should have seen something. My variables are @MonthStart, @MonthEnd and @Answer.
DECLARE @DT DATETIME, @MonthStart DATETIME, @MonthEnd DATETIME
SET @Dt = Convert(DateTime,Convert(V
SET @MonthStart = (SELECT MAX(MonthStart) FROM tblMonth_Quarter M WHERE MonthInd = -1)
SET @MonthEnd = (SELECT MAX(MonthEnd) FROM tblMonth_Quarter M WHERE MonthInd = -1)
SELECT @MonthStart MonthStart, @MonthEnd MonthEnd, CASE WHEN @DT > @MonthEnd + 1 THEN 'YES' ELSE 'NO' END Answer
I expect to see 2008-09-28 00:00:00.000 2008-10-25 00:00:00.000 YES
Roaming? LOL!!!
Do you have the variables defined in the "official" variables window for SSIS?
Menu, SSIS, Variables
Variables
Ok, so to get it to break you have to execute the entire package, not just the task. If you don't want to run the first step, remove the constraint and disable it. Right Click, Disable. The break will look like this.
Break
SWEET! ! I hate to leave and go to the dentist but I have to. Where are you located? We have different hours. I am in Florida. Yes, it's gorgeous! Thanks for everything so far! Quick question, since I am going to execute three procs, should I stay in the Control Flow or go to Data Flow for the DataFlow task? How does SSIS know to continue the processing over there? What are the guidelines when to use each, other than Control Flow can only process a task at a time?
In the Execute SQL Task you need to define the input parameters. It would look someting like this in your script.
In the Parameters tab you need to setup the mapping for this question marks. The first one is 0, the second is 1.
You need to set BypassPrepare to True since you have parameters.
As for taking the time off the datetime, couple ways. Probably best to select the values into the variables without time. Of course you also need to change the variable data type to String. You could also create two new variables, and define them by expressions. MonthStartNoTime, MonthEndNoTime. Evaluate as expression = True, define the expression to drop the time. Here is a SQL statement to select the value as a String value without time.
convert(char(12),@vDa
in SQLTask -
DECLARE @MonthStart as datetime;
DECLARE @MonthEnd as datetime;
SET @MonthStart = ?;
SET @MonthEnd = ?;
DECLARE @MonthStartNoTime as varchar(12);
DECLARE @MonthEndNoTime as varchar(12);
SET @MonthStartNoTime = convert(char(12),@MonthSta
SE
exec
I'm earning my points on this one.:-)
parms
In your picture the precedence constraint prevented the execution of your next task with the break. As far as seeing the variable values, only the variables defined in the Variables Window are available in the locals window. So your "declared" variables in the SQL Task will not show up.
If you want to validate the variable values, use the Post Execute break
Business Accounts
Answer for Membership
by: HoggZillaPosted on 2008-11-06 at 12:51:17ID: 22899407
You should put an Execute SQL Task in the Control Flow window and that is where you evaluate the indicator. Then, you connect the arrow from that to the next task. Right click on the arrow and set a constraint. Would you like an example?