• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

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?
    -- 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 
                  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,

Open in new window

1 Solution
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.


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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now