Basic package 101 - using evalution of data

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?
DECLARE @DT DATETIME, @TimeKey INT
SET @Dt = Convert(DateTime,Convert(VarChar,GetDate(),101))
SET @TimeKey = (SELECT TimeKey FROM CommonDW.dbo.dimTime  
                WHERE  FullDateAlternateKey = @Dt)
 
SELECT @TimeKey
-- Test to be sure it is still the current month. MonthInd should be 0.
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
--  Update the table 
UPDATE M
SET MonthInd = -1		
FROM tblMonth_Quarter M 
WHERE @TimeKey > (SELECT MAX(MaxMonthKey) FROM  
                  tblMonth_Quarter WHERE MonthInd = -1)	   
END

Open in new window

Laura2112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HoggZillaCommented:
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?
0
HoggZillaCommented:
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.bmp
SSIS-Populate-Variable.pdf
0
HoggZillaCommented:
Then, if you pass your expression and you want to continue - add another Execute SQL Task to perform the update. Here is some great instructions for working with the Execute SQL Task.
http://www.sqlis.com/post/The-Execute-SQL-Task.aspx 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Laura2112Author Commented:
Cool, let me play for a bit and I will be back it touch, thanks HoggZilla!
0
Laura2112Author Commented:
Holy Makrel! This is some good stuff, Steve!   Do you actually teach SSIS?
0
jbauer22Commented:
This is Steve's boss.  Steve, get back to work.
0
HoggZillaCommented:
Yep, that's my boss. He still thinks I work all day.
I love helping people with SSIS, for the points :-). Unless I get fired, then I might start soliciting.
Thanks for the compliment.
0
Laura2112Author Commented:
That is so funny!  Well, I just figured out how to get to the Variables.. Full stream ahead!  When I entered my logic into the SQL Statement, I got the error that The Declare cursor SQL construct or statement is not supported. So, I am not sure where to go now.
0
HoggZillaCommented:
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.
0
Laura2112Author Commented:
I could do all of the above in a simple proc and just run the that proc from the Execute SQL Task.
Then my basic task is just 'Is it the first day of the month and do I need to run my procs and other packages? If no, end the package processing. If yes, pass my date variable to the procs and run.
0
HoggZillaCommented:
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_MONTH] == 1
That will solve that step. Next?
0
Laura2112Author Commented:
Ok, proc is done. I have it as my first SQL task. How do I default my variable to use the current date?
Variable Name = Today  Datetype = DateTime Value = current date. I clicked on todaysdate but I want that to be the default, like I would for GETDATE()
0
Laura2112Author Commented:
Found Expression Builder in your documentation :)
0
Laura2112Author Commented:
Ok, On the Result Set tab map Column 0 to your variable and then you have the means for a precedence constraint.
When I go to the Result Set, I do not see where there are columns. I see ResultName and Variable Name. I don't see how to get back to the variable I created in Expression Builder.
0
Laura2112Author Commented:
[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". Can't get it to execute. This comes back though from my SQLStatementSource from my expression. 2008-11-06 18:47:27.619000000. As much as I hate to, I have to leave for the evening, 7:00 pm.. I hope you may be available tomorrow. This is fun!

0
HoggZillaCommented:
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::YourVariable]
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.
0
nmcdermaidCommented:
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.
0
HoggZillaCommented:
Laura, how are you doing with your SSIS?
0
Laura2112Author Commented:
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(VarChar,GetDate(),101))
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

0
HoggZillaCommented:
>>DECLARE @DT DATETIME, @MonthStart DATETIME, @MonthEnd DATETIME
SET @Dt = Convert(DateTime,Convert(VarChar,GetDate(),101))
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.
0
Laura2112Author Commented:
I created two variables one called Yes the other No. Both strings, values set at Yes and No.      
In my task editor, I have a single result set. When I go to ResultSet, I have two variable names User::Yes and User::No. Do I set each to 0 for the index under Result Name?
0
HoggZillaCommented:
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.
precedence-constraint.bmp
0
Laura2112Author Commented:
Shouldn't I be able to create just one variable that allows either yes or no? Can I leave it blank for the value?
0
Laura2112Author Commented:
I added the precedence contraint between the SQL Task and a sequesnce container. Not seeing where I get to the precedent Contraint editor like above, I have to go to a meeting but should be back relatively soon, hopefully less than 30 minutes
0
HoggZillaCommented:
Right click on the connecting arrow and select Edit. Or you can double click.
0
Laura2112Author Commented:
I am referencing a variable that I have since deleted. I changed the variablew name to Answer,  leaving the value blank. This is telling me that it failed to lock variable User::No
0
Laura2112Author Commented:
It was sucessful. Next?  It now has the blue line pointed to the sequence container.
0
HoggZillaCommented:
Have you verified the Answer variable is populated? And your constraint has the little Fn box next to it that indicates an Expression is part of the constraint?
0
Laura2112Author Commented:
I created the variables for MonthStart and MonthEnd to pass to the proc when the answer = "Yes". Do I put an SQL task inside of the Sequence Container?  How to I test that the variables are set for the code?
0
Laura2112Author Commented:
The expression validated sucessfully. The little fx is part of the precedent constraint.
0
HoggZillaCommented:
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-edit.bmp
breakpoint-select.bmp
breakpoint-locals.bmp
0
Laura2112Author Commented:
I have to leave.  Not getting my "Yes" or the MonthStart or MonthEnd in locals, all of which I declared in
the Evaluated Current Date SQL Task and made variables of each. I may try to log on later. Thanks for everything so far!
111008---Breakpoints.doc
0
Laura2112Author Commented:
You roaming around out there Hoggzilla?
0
Laura2112Author Commented:
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(VarChar,GetDate(),101))
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
0
HoggZillaCommented:
Roaming? LOL!!!
Do you have the variables defined in the "official" variables window for SSIS?
Menu, SSIS, Variables

variables.bmp
0
Laura2112Author Commented:
0
HoggZillaCommented:
You are not seeing the breakpoint (locals) window because of the error. What does the Output window display when the task turns RED?
0
Laura2112Author Commented:
I got it to work since that last screen shot. It did not like the names NewResultName for all of the variables. Imaging that. Now the result Names are named accordingly.
111108---Breakpoints---success.doc
0
HoggZillaCommented:
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.
breakpoint-yellow.bmp
0
Laura2112Author Commented:
Still nothing in locals. It went to green pretty fast.
0
Laura2112Author Commented:
see attached. Should the next task be set up in any way,? for now I just dragged a Dataflow taks over so I had something at the other end of the constraint.
111108---Breakpoints----still-no.doc
0
HoggZillaCommented:
Screen shot the Breakpoint setup. And are you running the entire package, using the Green Start arrow up top?
0
Laura2112Author Commented:
I disabled the first SQL task. Once it is out of Debug, I right in the SQL Task, and click Execute Task
111108---Breakpoints----First-SQ.doc
0
HoggZillaCommented:
You have to run the entire package to instantiate breaks. The Execute Task option wont trigger the break.
0
Laura2112Author Commented:
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?
0
HoggZillaCommented:
I am in ........... Florida! I know, the weather is absolutely awesome! Lakeland, Florida to be exact.
Control flow is what you will need, just using an Execute SQL Task to execute.
0
Laura2112Author Commented:
Hi Hoggzilla,
Me still. I am passing the variables to the proc now. I set a breakpoint to break when the container receives the OnPreExecute event. Is that the wrong choice for the breakpoint?
My SQLStatement is exec spPartD_DrugCard @MonthStart, @MonthEnd.
111208---Breakpoints----running-.doc
0
Laura2112Author Commented:
Also, how do I take the time off of  User::MonthStart      {9/28/2008 12:00:00 AM}      DateTime
0
HoggZillaCommented:
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),@vDateVariable,110)

DECLARE @MonthStart as datetime;
DECLARE @MonthEnd as datetime;
 
SET @MonthStart = ?;
SET @MonthEnd = ?;
 
Exec spPartD_DrugCard @MonthStart, @MonthEnd

Open in new window

0
Laura2112Author Commented:
Thanks. I already have the dates set up as variables, do I still have to declare new ones? They populate in the local window after the Evaluate current Date SQLTask. I'd have thought I could just pass those??
0
Laura2112Author Commented:
Not sure where I set it up as 0 and 1
111208---variables-and-parametes.doc
0
HoggZillaCommented:
Parameter Name. The first ? is 0, the second is 1. Each type of OleDb has their own way of doing it. For SQL Server, zero based indexes.
0
Laura2112Author Commented:
Not working. Getting tired of this yet??
in SQLTask -
DECLARE @MonthStart as datetime;
DECLARE @MonthEnd as datetime;
SET @MonthStart = 0;
SET @MonthEnd = 1;
exec spPartD_DrugCard  
convert(char(12),@MonthStart,110), convert(char(12),@MonthEnd,110)
0
HoggZillaCommented:
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),@MonthStart,110);
SET @MonthEndNoTime = convert(char(12),@MonthEnd,110);
exec spPartD_DrugCard  @MonthStartNoTime, @MonthEndNoTime
I'm earning my points on this one.:-)

parameters-mapping-zero.bmp
0
Laura2112Author Commented:
You are not kidding about that! Am I allowed to award more than the 500??
I get the error 'Faild to lock variable 0 for read access with error... variable cannnot be found
111208---failed-to-lock-variable.doc
0
HoggZillaCommented:
Send me a screen shot of the Parameter Mapping page. Something aint right.
500 is max :-) Thanks though. Maybe I can help on your next question.
My sign reads: Will Work For Points!
Subtitle: Which have no value whatsoever anywhere in the world but I don't care.
0
Laura2112Author Commented:
I am close now, but am not seeing the last column Parmaeter... with the -1. What does the -1 mean anyway?
111208---where-is-the--1-column.doc
0
HoggZillaCommented:
It is a little something extra you get with SP2. -1 is the default. I don't use it. :-)
0
HoggZillaCommented:
Where are you in Florida? Company? Position?
0
Laura2112Author Commented:
Port St. Lucie. Medco Health Solutions (Liberty Medical) Business Systems Analyst III. Programming 15 + years. Use to be a mainframe programmer until I took this position almost 6 years ago. Would very much like to become SQL Server 2005 BI certified. Very few Datawarehouse positions here tho. You?
0
HoggZillaCommented:
Marriott Vacation Club, Sr Manger, Finance and Accounting Development. Primarily SQL Server now, still some Oracle. VB.net, and whatever else.
Go Rays!!!
0
Laura2112Author Commented:
Still not seeing my variables or parameters to be sure that the dates are being fed to the proc spPartD_DrugCard.I set the breakpoint to OnPreExecute event. Suggestion?
111208---OnPreExecute.doc
0
HoggZillaCommented:
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
0
Laura2112Author Commented:
How long playing with SSIS?  Do you do much with SSAS?
0
Laura2112Author Commented:
Hmmm. I have no break points any longer. When I looked at he success, the next proc is validated.
But nothing is happening.
111208---No-Breakpoints.doc
0
HoggZillaCommented:
Save the package with a .txt extension and post it here. Let me look.
0
Laura2112Author Commented:
Oh , *********, I think I lost it!! I do not see it in my Solution Explorer any more! Help! Can I convert this back to a dtsx file?

SSIS-for-Pharmacy-Statistics-Tex.txt
0
Laura2112Author Commented:
I'm cool, I do have it :)
0
HoggZillaCommented:
I have it as well. I will look at it tonight.
0
Laura2112Author Commented:
You're the BEST Expert ever! TY
0
HoggZillaCommented:
Those kinds of compliments will get you everywhere and anything! :-)
0
Laura2112Author Commented:
SSIS classes, please!  Do you tutor?
0
Laura2112Author Commented:
Hi'ya Hoggzilla!  Any luck with why the SQLTask isn't getting kicked off when my constraint = yes?
0
HoggZillaCommented:
Yes, you need to change your Evaluate Operatin to "Expression and Constraint". You currently have it set to Expression only and that means it evalutes before the Answer variable is populated.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HoggZillaCommented:
I think it is safe for you to close this question and open a new one. You can select the Precedence Constraint as the answer for this one and open a new question for more help which I will be more than happy to continue. Thanks!
0
Laura2112Author Commented:
HoggZilla was amazing in his patience and perseverence in assisting me with this. Thank you so much!!!
0
HoggZillaCommented:
Please let me know if I can assist. That did resolve your issue, correct? What's next?
0
Laura2112Author Commented:
Hi Hoggzilla, I have anotehr open question there now.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.