Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Passing SSIS Package Variable To Stored Procedure As Parameter Expand / Collapse
Author
Message
Posted Wednesday, February 11, 2009 1:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 8, 2009 1:16 PM
Points: 8, Visits: 66
Hi,

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.


-Baaul
Post #655216
Posted Wednesday, February 11, 2009 10:20 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:46 PM
Points: 1,045, Visits: 2,725
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:

DECLARE @dt DATETIME
SET @dt = GETDATE()
Exec dbo.spDoSomething @dt

hth,
Tim




Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #655393
Posted Thursday, February 12, 2009 12:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 22, 2010 12:45 PM
Points: 16, Visits: 103
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.
Post #656019
Posted Thursday, February 12, 2009 3:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 8, 2009 1:16 PM
Points: 8, Visits: 66
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.

Regards,

Fenicon
Post #656181
Posted Wednesday, May 6, 2009 3:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 10, 2014 9:34 AM
Points: 11, Visits: 38
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.

Regards
Sandesh
Post #710897
Posted Monday, January 25, 2010 9:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:30 PM
Points: 1, Visits: 88
Greetings

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.
2.select 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.

Post #853410
Posted Friday, July 2, 2010 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 8, 2010 7:36 PM
Points: 8, Visits: 59
Hi

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.

Thanks
ssr



Post #946938
Posted Sunday, July 18, 2010 7:52 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 11, 2013 8:10 PM
Points: 73, Visits: 183
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.
Post #954495
Posted Wednesday, July 21, 2010 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 8, 2010 7:36 PM
Points: 8, Visits: 59
Thank you for help.
i will try these and let you know.
Thanks once again.
Post #956297
Posted Wednesday, March 2, 2011 9:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 4:16 PM
Points: 224, Visits: 304
Hi,

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.
Adam
Post #1072022
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse