troubleshooting Question

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

Avatar of boomtowncio
boomtowncioFlag for United States of America asked on
Microsoft SQL ServerMicrosoft DynamicsMicrosoft SQL Server 2008
2 Comments1 Solution510 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
moosetracker

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros