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.