May 28, 2008 at 8:36 am
Not sure if this is the right place to post this, but here goes.
I've recently migrated a bunch of DTS2000 packages to SQL Server 2005, and because some of the data comes from Oracle, I've found some issues with Oracle datatype incompatibility with SQL Server 2005 datatypes. I've figured out how to fix these issues in Data Flow tasks, however I'm stuck on how to fix it in an Execute SQL Task.
Here's the issue: in the Execute SQL Task, I'm connecting to the Oracle db using an OLEDB connection. The query is simple: Select Count(*) From TableX. I have a global variable (type Int32) that I want the count to be assigned to, so I've set that in the Result Set tab. What the problem is, however, is that the data type returned by Oracle is causing errors: [Execute SQL Task] Error: An error occurred while assigning a value to variable X: "Unsupported data type on result set binding 0.". Upon converting the package, the data type for this task was set to DBNULL in the properties for that task, but that didn't work in SQL Server 2005. I found that resetting it to Int32 worked fine for the one of the Execute SQL Tasks that queried a SQL Server db, but not for Oracle.
I'm sure someone has figured this out, but I've tried all the data types listed in the properties (ForcedExecutionValueType), and I'm still getting the same error.
I also tried using an ADO.NET connection but I still ended up with the same errors.
Please help if you know the solution to this issue.
nat
May 28, 2008 at 8:51 am
Have you tried using Cast/Convert on the sql statement to force the query to return an INT? I don't know ORACLE but perhaps the Count function returns what in SQL Server would be a BIGINT?
BTW - I would have posted in an SSIS forum.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 28, 2008 at 10:09 am
Tried that earlier; doesn't work.
May 28, 2008 at 2:06 pm
Use CAST to make it a VARCHAR and then convert it in SSIS with a derived column or data conversion component.
The Oracle Clients (at least all of them through 10g) are not 100% OLE DB compliant and they can return numerics with precisions that the SSIS components cannot understand. It's much easier to deal with them as character data and then convert afterwards.
November 3, 2008 at 12:06 pm
I am having a more basic problem.
I have a simple SSIS package that is connecting to an Oracle database. I want to run a script on the database that contains multiple truncates, followed by multiple inserts. The script runs fine from within oracle, but when i try and run it from the Execute SQL Task in SSIS it errors. It will only allow me to run 1 command at a time (so its not a connection issue). Is there anyway for it to run multiple?
I have reduced the script to just have 2 single truncate table commands and the error returned is (from oracle) "Invalid character", which i'm pretty sure refers to the semi colon i have between the two commands. Removing the semi colon gives different errors. I have tried separating then with a commit etc. Cant get the 2 commands to work in one task!
Help would be much appreciated!
Thanks
November 4, 2008 at 8:05 am
I'm not really sure but you can try to add the backslah caracter like this :
truncate table table1
truncate table table2
etc...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply