Date Type issues with Variables in SSIS

Question

Date Type issues with Variables in SSIS

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mark Durley Valued Member Points: 74 More actions · Answer 1

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

Reynoud Deen Old Hand Points: 334 More actions · Answer 2

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?

Albert-269665 SSC Enthusiast Points: 108 More actions · Answer 3

Hey Reynoud,

what is it precisely what you want to do with "for later access and update"'?

Grtz,

Albert

Reynoud Deen Old Hand Points: 334 More actions · Answer 4

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

Mark Durley Valued Member Points: 74 More actions · Answer 5

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.

Marjorie Bareng Valued Member Points: 71 More actions · Answer 6

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?