Hi everyone,
We're trying to migrate a varchar field from Sql2k to varbinary in a sql25k through a dtsx package. We get an error which tell us: "data will be lost".
Does anyone have any idea about that?
Thanks for your time and inputs,
Do it in the extract query, T-SQL supports explicit casts between those two types
DECLARE @.v varchar(10)
SET @.v = '0123456789'
SELECT CAST(@.v AS varbinary(10))
Hi,
How to translate oracle Decode without changed code I mean:
I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I
Wrote this function
select dbo.fnDecode( 1 , 2 , 3 )
first parameter always int, and the others parameters could be char, int or float.
I would like to test first value and return second or third value
-> sql_variant for all parameters, ok
but I can use + or -
I can't do this
select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 )
If I put cast ok, but I is there another way, overload this call?
With clr doesn't work, because Sql Server doesn't accept function overload calls from C#
Any ideia?
cheers,
Any ideia?
Use explicit cast/convert for both operands?
|||There is no easy way to do what you are trying i.e., replacing Oracle's built-in DECODE function with TSQL/SQLCLR equivalent. The sql_variant approach will work to some extent but you will run into issues when you try to use operators on the value as you experienced. It will require explicit CASTING in lot of cases. And using TSQL UDF will introduce performance problems especially in DMLs if used incorrectly. SQLCLR approach doesn't work because we don't support overloaded methods. Best is to convert uses of DECODE to CASE expression. Note that CASE expression is actually ANSI SQL standard and more portable way to write SQL code. In fact, Oracle supports it from 7.3 or 8i so there is no reason to use DECODE in Oracle too. As stop gap measure, you could write different set of TSQL UDFs (decode, decode1, decode2 etc) each handling different data types. And when you hit performance problems with your queries/DML that uses these UDFs you can convert to CASE expression i.e., inline the code.|||I can't use cast/convert, because I never know what kind of value will be, so I dont know what kind of cast if to char or to int,float ....Hi everyone,
My current dutie is translate Vb code into SSIS. The following structure is getting me totally crazy:
sql = "SELECT * FROM TABLE1 WHERE FIELD = XXXX"
rs.execute sql
WHILE NOT RS.EOF
STUFF
ANOTHER SELECT AND ANOTHER LOOP
...
...
INSERT AND DELETE STUFF
END WHILE
Which is the best method in order to reach this goal? I'm trying to by means of OLEDB Source Editor connected to Script Component Task on data flow layer but I'm stuck with this.
I don't want to do cursors or something like that with T-SQL.
Thanks in advance for your inputs/help and regards,
Could you use nested FOREACH containers? Store your initial resultset in a variable, then iterate through that, executing the second SQL statement, storing the results in a variable, and using a second FOREACH to iterate that.|||hi,
using ForEach Loop container on control flow, you mean?
should I use ForEach ADO enumerator, shouldn't?
tia
|||Yes, control flow. Yes, ADO recordset.|||Thank you. I'll test it|||It's easier than I though. I'll use a Script Task on Control Flow task. Just that task,
I don't need nothing else
|||Could you share the script? Stripped of anything specific to your business, of course.
Hi everyone,
My current dutie is translate Vb code into SSIS. The following structure is getting me totally crazy:
sql = "SELECT * FROM TABLE1 WHERE FIELD = XXXX"
rs.execute sql
WHILE NOT RS.EOF
STUFF
ANOTHER SELECT AND ANOTHER LOOP
...
...
INSERT AND DELETE STUFF
END WHILE
Which is the best method in order to reach this goal? I'm trying to by means of OLEDB Source Editor connected to Script Component Task on data flow layer but I'm stuck with this.
I don't want to do cursors or something like that with T-SQL.
Thanks in advance for your inputs/help and regards,
Could you use nested FOREACH containers? Store your initial resultset in a variable, then iterate through that, executing the second SQL statement, storing the results in a variable, and using a second FOREACH to iterate that.|||hi,
using ForEach Loop container on control flow, you mean?
should I use ForEach ADO enumerator, shouldn't?
tia
|||Yes, control flow. Yes, ADO recordset.|||Thank you. I'll test it|||It's easier than I though. I'll use a Script Task on Control Flow task. Just that task,
I don't need nothing else
|||Could you share the script? Stripped of anything specific to your business, of course.Hi everyone,
We're struggling ourselves with this and we are stuck.
Which is the equivalent for a SSIS in a Script Task component on CONTROL FLOW layer?
Main = DTSStepScriptResult_ExecuteTask
Thanks for your input and regards,
Set the script result to a variable and then use that variable in a precedence constraint on the Execute XXX Task.|||To try and expand on Phil's reply. The constant you mention does not exist, because you no longer have workflow scripts as you did in DTS. What you do have is a much richer set of precedence constraint functionality. As well as having the normal succes and failure stype stuff, you can also set an expression on the constraint. The expression uses a new syntax, which is quite simple, but maybe you could replace your script logic with an expression. Your logic may be too complicated for this, so you would have to use an Script Task and from that your result in variable. The variable can easily be referenced in an expressionon the constraint.
I would avoid any ActiveX Script in SSIS, use the new features, including the Script Task if required. The support for ActiveX is not good, things like error information are very poor now.
