December 31, 2008 at 5:11 am
i have a procedure in sql server which executed this way
sp_OracleInterface_InvoiceNoTemp '2008-12-25','2008-12-25'
I wanted to execute it within SSIS package, so went on to define two package level variables StartDate and EndDate.
Now that i have define two variables, i have define another variable
strsql. clicked on its properties and set EvaluateAsExpression property true. Then went on to write the following string in the Expression property
"sp_OracleInterface_InvoiceNoTemp '" + @[user::StartDate] + "','" + @[user::EndDate] + "'"
which gives me the following error
Nonfatal errors occurred while saving the package:
Error at Package: The variable "user::StartDate" was not found in the Variables collection. The variable might not exist in the correct scope.
Error at Package: Attempt to parse the expression ""sp_OracleInterface_InvoiceNoTemp '" + @[user::StartDate] + "','" + @[user::EndDate] + "'"" failed and returned error code 0xC00470A6. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.
December 31, 2008 at 5:56 am
Variables in expressions are case sensitive. You need to capitalize the "U" in "User":
"sp_OracleInterface_InvoiceNoTemp '" + @[User::StartDate] + "','" + @[User::EndDate]
December 31, 2008 at 5:57 am
If you drag the variable from the variables list in the expression builder, you can avoid these problems.
December 31, 2008 at 6:40 am
still it did not work
------------------------------
Nonfatal errors occurred while saving the package:
Error at Package: The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.
Error at Package: Attempt to set the result type of binary operation ""sp_OracleInterface_InvoiceNoTemp '" + @[User::StartDate]" failed with error code 0xC0047080.
Error at Package: The expression for variable "InsertInvoiceTempSQL" failed evaluation. There was an error in the expression.
December 31, 2008 at 6:43 am
Added to the last post, how do i open expression builder for a variable.
i have not ellipsis or any button for the Expression field for the variable.
December 31, 2008 at 7:00 am
You should have in elipsis in the expression property for a variable.
As far as the error, it is because your date variables are of date data types and you cannot add a string to a date.
You have to use a type cast, it will look something like this:
"sp_OracleInterface_InvoiceNoTemp '" + (DT_WSTR,20)@[User::StartDate]
I don't have BIDS on this machine so I cannot find the exact syntax. for you.
December 31, 2008 at 7:18 am
After converting, i did not get any error while building.
However, while running itgot the following error:
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "sp_OracleInterface_InvoiceNoTemp '12/12/2008','31/12/2008 14:14:33'" failed with the following error: "Error converting data type varchar to smalldatetime.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
December 31, 2008 at 7:21 am
You are trying to use a Varchar variable with a smalldatetime paramater, this will not work. you either need to change the varaible data type to smalldatatime, or do a data convsersion as part of your dataflow.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply