Passing SSIS Package Variable To Stored Procedure As Parameter

  • fenicon

    Old Hand

    Points: 364


    I've a variable "varProcessDate" which is a TimeStamp that I want to feed into a stored procedure. I'm sure someone already answered this. If this had already been answered here pls send me the link.

    This is what I have so far. I have a SSIS variable defined:

    Name = varProcessDate

    Scope = mypackagename

    Data Type = String

    Value = SELECT convert(varchar(20),getdate(),120) <--- can I do this??

    I have a Execute SQL Task that is using a Native OLE/DB client connection object. I have a SQL Stmt = exec dbo.spDoSomething ?. In Parameter Mapping, I have the following:

    Varibale Name = User::varProcessDate

    Direction = Input

    Data Type = Varchar

    Parameter Name = 0

    Parameter Size = -1

    Resultset Property NOT SET

    When I put the value direclty into the 'SQLStatement' in the EXECUTE SQL TASK EDITOR it works just fine: Exec dbo.spDoSomething '2009-02-11 14:15:45'.

    The package fails with this msg:

    Error: 0xC002F210 at SQL 03_SetProcessedDate, Execute SQL Task: Executing the query "dbo.spDoSomething ?" failed with the following error: "Error converting data type varchar to datetime.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Task failed: SQL 03_SetProcessedDate

    Thanks in advance for any help with this.


  • Tim Mitchell


    Points: 15659

    If I read your post correctly, you're entering SQL code directly into the Value definition of your variable, which you can't do in SSIS. You can, however, modify your code in the SQL Task to read as such:


    SET @dt = GETDATE()

    Exec dbo.spDoSomething @dt



    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant | @Tim_Mitchell |
    ETL Best Practices

  • Robert Ham-354502

    SSC Veteran

    Points: 234

    Tim's solution works and is the easiest for this case. Having said that, here is some info on using SSIS variables to do the same thing, which can come in handy when the parameter value you need has to come from the results of preceding SSIS tasks.

    Your stored procedure expects a datetime input parameter, so you need an SSIS variable of type DateTime. Change your variables data type to DateTime (an SSIS data type) and pick any date in the value box. You will then set the variables value based on an SSIS expression (not a SQL expression - typing a sql command into a string variable does not itself execute that command, it just sets the string variable to the text you enter - though such a variable can be used as the source of the sql code for an execute sql task)

    Name = varProcessDate

    Scope = mypackagename

    Data Type = DateTime

    Value = Pick any date from the date box

    While the variable is highlighted, go to its properties (easiest way is to press F4, or have both the variable and properties windows visible at the same time). Then, set the EvaluateAsExpression property to true, and click on the elipsis in the Expression box to bring up the Expression Builder. You then use the expression builder to define how to set the value of your variable at run time. In your case, the expression GETDATE() serves the purpose, but you could also use various functions and any other variables in scope.

    Your execute sql task is set up correctly, except you should change the Data Type to DATE (NOT DBDATE, nor DBTIMESTAMP)

    Varibale Name = User::varProcessDate

    Direction = Input

    Data Type = DATE

    Parameter Name = 0

    Parameter Size = -1

    Resultset Property NOT SET

    This should also do the trick.

  • fenicon

    Old Hand

    Points: 364

    Thanks Robert!

    Tim's solution worked for me, but I'm going to try your solution also. I have bunch of legacy DTS packages I'm currently converting to SSIS. Your solution will come handy.



  • Sandesh Kadam-335754


    Points: 473

    Hello Guys,

    Thanks for your all the post. They have been really helpful.

    Robert, your last post helped to understand how we can configure the Variable But for me it didn't made clear how can i pass the SSIS variable to the stored procedure.

    I have a EXEC SQL TASK and i am doing "EXEC Populate_something" in the sql statement of it. Now the question arrise is how should i pass that user::variable

    Look forward for your help.



  • Randy Groenhagen-394301

    SSC Journeyman

    Points: 77


    If you wish to pass your variable to your stored procedure simply do the following:

    1. Go to the properties on the sql task and clickon the elipses within the expression builder. the sqlstatementsource property and build an expression.

    From here is pretty simple here is an example:

    "exec spPopStatementBase2FactLoopCln" + "'"+@[User::ZipFilePathConverted]+"'"

    what you are doing here is creating your sql statement during run time. As you can see you are passing the user vaiable to the parameter your sotred proc expects. In this example my proc expects a file path. Now since your proc is expecting a date you will need to convert your date to a string. In this example I'm not concerned about the time on the date, because I don't carry it.

    You would do someting like this:

    "exec dwscrub..SpPopCommissionFileRePost "+"'"+ (DT_WSTR, 30) (DT_DBDATE) @[User::PostDate] +"'"

    Now you can actaully evaluate your expression during design time if you put a date in the variable section and you want to make sure your expression returned, is somthing you can actually past in a sql pane and execute. That would be a good test for you.

    I hope this helps. I'm sure there are othre ways you can do this. But I just wanted to give you an example. Far as documentation goes, I would make sure you put an annotation (note) next to the tasks stating that you are using the expression builder. Simply, so the next guy that comes along and looks at your code. Or you can get some lug ins to VS to highlight tasks when there is code in the sql task. FYI-I'm a sql guy and the expression builder takes some time to get use to. " double quoutes are used around your expressions. Of course the + symbol is basically concatentation jsut like sql, but I often find myself forgetting that. Have a nice day.

  • ssr1606

    Old Hand

    Points: 324


    My question to this Tim's Solution is if the date has to be changed sometime as per the need , i mean it is not getdate() all the time, if that package failed and has to change the date to some old date. how can it be implemented.

    Here is the issue i am facing.

    If you have some time please try to give me a solution.

    Thanks in advance.

    I have a Dataflow task, in which , From source (Sql server table), using Select it will pick some records with some condition like where status_code = ?(variable) and Created Date = ? (variable) and insert those selected records into target table.

    For everyday run the status will be 'abc' , and date parameter will be Getdate() , but some times if that package fails i have to change Status to " xyz" the date to some old date and run the package.

    I am unable to understand 2 things

    1) My created date is of datatype datetime, if i use getdate() in variable how i can compare those two dates. ( I just want to compare only the date part and exclude the mins and seconds).

    2) Changing the date to some other old date.



  • PaVeRa22


    Points: 1965

    1. you can use CONVERT function, to convert date both the sides to same style like this...

    status_code = ?(variable) and convert(varchar(10),[Created Date],112) = convert(varchar(10),?,112) (variable)

    2. to change date / status, use configuration file, which will ready dynamic values, you can modify any time.

    or use one table to store these two values and retrieve into variables and still use the above sql code, if you are not aware of configuration files.

    or you can use txt file too instead configuration file, however you need to write some code to read from txt file...

    or you can supply values in commandline command while running too...

    Please let us know if u need any help.

  • ssr1606

    Old Hand

    Points: 324

    Thank you for help.

    i will try these and let you know.

    Thanks once again.

  • adam spencer

    Hall of Fame

    Points: 3926


    I am trying what was suggested in the post as well and I cannot get the expression to eveluate without an error:

    "exec [dbo].[spGetContractForCMS_ImportEDW]" + "'"@[User::RunDate_EDW] + "'"

    I am using double quotes exept on both sides of the variable I am using double quotes, one single quote, and another double qoute.

    Is this wrong? The run date is a varchar by the way

    thanx in advance.


  • Chad Downey

    SSC Enthusiast

    Points: 126

    This worked for me.


    ResultSet - None

    ConnectionType - ADO.NET

    SQLSourceType - Direct input

    SQL Statement - exec dbo.proc_Name '2011-04-30','2011-04-30','2011-04-30'

    IsQueryStoredProcedure - False

    Parameter Mapping:

    Variable Name Direction Data Type Parameter Name Parameter Size

    User::var_date1 Input Date 0 -1

    User::var_date2 Input Date 0 -1

    User::var_date3 Input Date 0 -1


    "exec dbo.proc_Name " + "'"+ (DT_WSTR, 30) (DT_DBDATE)@[User::var_date1] +"','"+(DT_WSTR, 30) (DT_DBDATE)@[User::var_date2] +"','"+(DT_WSTR, 30) (DT_DBDATE)@[User::var_date3] +"'"

    My code is slightly modified from above. I had a problem with an error of about can't find the stored procedure. When I changed IsQueryStoredProcedure from True to False all worked well.

    Chad E. Downey, CDMP - Certified Data Management Professional
    Consultant - SQL Server, SSIS, SSRS, SSAS

  • Phil Parkin

    SSC Guru

    Points: 244420

    Do you have a question?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See for details of how to post T-SQL code-related questions.

  • rajeshthehotty

    Valued Member

    Points: 59

    You are awesome, you are the man!!! Cheers.

  • Randall.Groenhagen

    SSC Enthusiast

    Points: 141

    "exec [dbo].[spGetContractForCMS_ImportEDW]" + " ' "+@[User::RunDate_EDW]+ " ' "

    This will work. You just needed one more concatenate operator for your first apostorophe before your run date variable. Other than that you had it right. :):)

  • Randall.Groenhagen

    SSC Enthusiast

    Points: 141

    "exec [dbo].[spGetContractForCMS_ImportEDW]" + " ' "+@[User::RunDate_EDW]+ " ' "

    This will work. You just needed one more concatenate operator for your first apostorophe before your run date variable. Other than that you had it right. 🙂

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply