February 11, 2009 at 1:46 pm
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
February 11, 2009 at 10:20 pm
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
February 12, 2009 at 12:31 pm
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.
February 12, 2009 at 3:58 pm
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
May 6, 2009 at 3:30 am
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
January 25, 2010 at 9:14 pm
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.
July 2, 2010 at 9:34 am
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
July 18, 2010 at 7:52 am
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.
July 21, 2010 at 6:37 am
Thank you for help.
i will try these and let you know.
Thanks once again.
March 2, 2011 at 9:28 am
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
June 27, 2011 at 3:57 pm
This worked for me.
General:
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
Expressions:
"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
June 27, 2011 at 11:12 pm
Do you have a question?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 14, 2011 at 12:26 am
You are awesome, you are the man!!! Cheers.
October 13, 2011 at 11:14 am
"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. :):)
October 13, 2011 at 11:15 am
"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 18 total)
You must be logged in to reply to this topic. Login to reply