Date Type issues with Variables in SSIS
-
Hello,
This question is about the use of Variables in Integration Services in SQL Server 2005.
I’m using Variables in Integration Services, and the Variables support Data Types like: Double, Int32, Int64, Object etc. I wonder how it will be possible to read the values of the variables in SQL statements. When I e.g. want to use a Derived Column task, it’s not possible to use the value of the desired variable in an expression. The error I receive is: the data type of variable “User::TotalPrices” is not supported in an expression. So I wonder how I can read the values of my Variables for further querying.
Has anyone some experience using Variables like this? Is there a way of converting these variables to a SQL-friendly data type? Can I e.g. convert a Variable of data type 'Object' (which contains numeric values like amounts of money) to a ‘Numeric’ or 'Money' data type?
-
Hi Reynoud,
Most of the variable types are automatically converted to types that work in expressions. The only exceptions, I think, are DBNull and Object. For your variables of type object, you could use a script component to copy the data to a different variable of a supported type, or to put the values into a column in the dataflow that the derived column would consume.
Hope that helps,
Mark
-
Thanks, Marc.
Using a script component should be possible I think. But my plan is actually to get this done without using scripts, as Microsoft claims that almost all transformations can be done without scripting. And it seems to me that using variables cannot be that difficult, so that you can only use scripts, right?
Maybe I think wrong? Maybe there's another way of putting 'money'-values into variables, for later access and update?
-
Hey Reynoud,
what is it precisely what you want to do with "for later access and update"'?
Grtz,
Albert
-
Sorry for my poor language knowlegde. Let me try to clarify my point once again.
I have declared a Variable within SSIS. By executing an SQL task, I want to assign a value (money type) to this Variable. The Variable is of type Object. I want to use the value of my Variable in an other task, e.g. Derived Column. This is because I want to do some calculations with the Variable. After this, the Variable will be overwritten by the result of this calculation.
So, what's my problem then? Well, after the value has been initially assigned to the Variable, it seems impossible to get the value of the variable in the right data type. For example, in the Derived Columnn I want to use my Variable within an expression, but somehow it needs to be converted (preferable to a Money data type), otherwise it's simply impossible to do some calculations with it. But I don't know how to fix this.
I hope I've made my problem clear now, otherwise just ask for more details
Reynoud
-
You will not be able to get a variable of type object into the derived column. Is it possible to put the output of the Sql Task into a string variable? If so, you could then cast that variable to a money type in the derived column expression where you want to use it:
(DT_CY)@[User::TotalPrices]
If the Sql Task does not support putting the result into a string, then I don't see an alternative to cracking the object variable in a script and either creating a new variable or a column with a supported type.
-
Similar difficulties..
In an execute sql task I am trying to set my result set values (which is a single row with 3 columns) to 3 variables. It errors on assigning the date value in my resultset to a variable that is of data type "datetime". The error is:
Error: 0xC002F309 at Set Client ID, FromDate and ToDate, Execute SQL Task: An error occurred while assigning a value to variable "gdtHoursFromDate": "Unsupported data type on result set binding 1.".
They're both dates. What date datatypes are incompatible with each other? Some help here?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply