Set Value for a Variable Inside a SQL Server Integration Service (SSIS) Package

I have developed a Query in SQL Server 2008 which I am using with the SQL Im/Export Wizard.   My SQL Query works fine and the SSIS Package I created works fine as well.  However, every time I run the package, I want to change the date that is set in the WHERE Clause of my Query.  For instance, my package SELECTS the name, wages & check number WHERE checkdate = 2010-03-05 and this query runs fine.  Now when I run the saved SQL Server Integration Service (SSIS) package, it allows me to "Set Value" for a variable inside the Package.  There is a special syntax which needs to be observed in entering the path (i.e. it is not enough to say: C:\Data\Package Name\Variable Name). The syntax to run the package with the new value for the Variable is like this:
 
"dtexec /f mypackage.dtsx /set \package.variables[myvariable].Value;myvalue"

This is not working. What am I missing?
SELECT
    -- SUM(case Type_
                 -- When 'E' then CAST(round(T.Qty * P.STDUNITRATE*100,0)
				 -- AS int)    
                 -- WHEN 'EB' THEN LEFT(CAST((T.TranAmt * 100) AS
                 -- VARCHAR(20)),10) 
                 -- Else 0
              --End) as Wages,
     SUM(case T.Type_
                  When 'DE' then LEFT(CAST((T.Tranamt*100) AS 
                  VARCHAR(20)),10)
                  Else 0
              End) as Benefits,
              LEFT(P.ChkNbr,10) AS CHK
              
 FROM GLSetup G, employee E
	  JOIN PRDoc P ON E.EmpId = P.EmpId
		AND P.DocType = 'CK'
	  JOIN W2EmpName W ON E.EmpId = W.EmpId
	  --JOIN PayPeriod A ON A.LUpd_DateTime = P.ChkDate
	  JOIN PayPeriod A ON A.PayPerNbr = P.PayPerNbr
	  JOIN PRTran T ON P.BatNbr = T.BatNbr
		AND P.ChkNbr = T.RefNbr
		AND T.TranType = 'CK'
		AND T.Type_ IN ('DE','E','EB')
		AND P.Status <> 'V'
	 
WHERE P.ChkDate = '2010-03-12'
GROUP BY W.NameFirst, W.NameMiddle, W.NameLast,W.NameSuffix, E.SSN, 
 E.Addr1, E.Addr2, E.City, E.State, E.Zip, G.Country, G.EmplId, G.Addr1,
 G.Addr2, G.City, G.State, G.Zip, G.Country, 
 A.PayPerStrtDate, A.PayPerEndDate, E.StrtDate, E.EndDate, E.PayType,
 P.StdUnitRate, P.ChkNbr,P.EmpId,P.BatNbr,P.ChkDate,A.PayPerNbr,
 T.ProjectID

Open in new window

boomtowncioAsked:
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.

moosetrackerCommented:
Wow, I set variables all the time between SQL & SSIS.. But I go about it totally different.

My Query is within a Stored procedure... Which pulls in the variable using the

Create Procedure NameOfStoredProcedure  @PassedDate datetime AS

if :
   WHERE P.ChkDate = '2010-03-12'  
is what you want for your variating date parameter then change that to :
   WHERE P.ChkDate = @PassedDate



Then I process the Stored procedure using an Execute SQL task set the connection to point to the database you placed the SP


Here is a link to show you how to use the  Execute SQL task. (Of course in the SSIS you have to set your variable to the date you want to pass in.

http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parameters 

I may be way off from what you are trying to do.. but it just seems so much easier then what you are doing.
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
boomtowncioAuthor Commented:
Yes I do need to get smarter on using stored procedures. That will make this much less complex. Thanks for the help!
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.