Welcome to EMC Consulting Blogs

Jamie,

I added some comments to the end of that thread that will help users with Oracle 8i or any objects imported from 8i. The DataReader Source has been a savior in that situation...

Scott Barrett

Sybase ASA has been problematic in using from SSIS also. If the machine is on the same subnet it connects okay; but if the source is on a different subnet (and cannot be seen by the Sybase tool "dblocate") you need to add commlinks=tcpip to the connect string. We have been unable to add this parameter. This isn't new to 2005, the same holds true for 2003, so it's NOT a beta bug. And this one may truly be operator error. We temporarily resolved the issue by changing direction, and we are using Sybase Direct Connect straight into SQL Server into a holding area, and then we are using SSIS (or some tool to be picked very soon) to migrate the data from the holding area into the star schema. But surely there is a way to get that commlinks parameter into the connect string. We were hoping that connect strings could be captured in .udl files and properly managed, passing them into packages as required based on a token; but we have yet to physically locate them - and data sources and data source views don't seem to do what they are expect to do... all the work appears to be done by the connection managers.

We will see what is delivered next Monday. It isn't expected to be bug free; but it would be awfully nice if it worked.

What about retrieving data from Oracle by using x64 MSSQL SSIS on any x64 Windows OS? Did anyone accomplish this task? :) Try if you like adventures.

Worked like a charm, I appreciate the heads up, as I was going from an AS400 OLE DB provider to SQL 2005 server.  Easy enough.

UPLOADTEMPLATE ...

Hi,

Can somebody explain how or where to set this property. I am not a DB expert.

Set AlwaysUseDefaultCodePage=TRUE

Thanks

MRS

Hi M.R.S,

to set the "AlwaysUseDefaultCodePage" property, click on data flow source or destination object, (if not already enabled) click View->Properties Window, navigate to Custom Properties section in that window. There you'll find AlwaysUseDefaultCodePage property.

Hope it helps,

M

Hi

WE are moving data from one oracle table into other oracle table for learning SSIS .

We have Created the Soruce and destination conenction using OLE db and it worked fine in the designer. We then add tha package in the SQL Server agent job. when we run the job it fails and not displaying the error message properlt , it just display check the history, in history nothing much is there

Can anyone help how to slove this issue??

Thanks in Advancd

I am trying to build Integration service package between Oracle and SQL server 2005 using Microsoft OLEDB Provider for Oracle.

01. I want to execute a set of PL/SQL scripts with parameters against Oracle 9i using Microsoft OLEDB Provider for Oracle as connection manager. How can I do this?(Note: I do not have option to build stored procedure in Oracle. I have to run multiple scripts as a set against Oracle using PL/SQL.)

02.  I have created a connection manager for Oracle using Microsoft OLEDB Provider for Oracle. How do I use this connection manager in script task. I want to write VB.net function inside the script task and instantiate an object of type defined in connection manager in the designer. Basically I want to use designer defined oledb connection manager inside code in script component.

Thanks Jamie!

Can you direct me to site  or show example where some PL/SQL are executed with parameters against Oracle using Execute SQL task. In fact I have big PL/SQL script with lot of variables defined to hold intermediate values and finally script returns some value which I have to store in the variable inside package for subsequent use.

Thanks again for your quick response and help.

Hi Jamie,

I'm calling Oracle stored Procedure from SSIS using Execute SQL Task.

Here is the syntax for SQL Statement:

{call RS2_RealProject_ETL.TransformLoadAction ?, ?, ?, OUTPUT ?}

My connection manager is using OLE DB connecting to Oracle 10g database.

I also mapped the input variables that to ordinal number of paramteres (i.e. 0, 1, 2 etc)

Stored procedure parameters are all of type "NUMBER" and I use "NUMERIC" data type in the mapping. Three of the parameters are "input" parameter and the last one is "output" parameter.

I've set the "ResutSet" property to "None" as well. and Task knows that it's running a stored procedure.

When I execute the task, I get the following error.

Error: 0xC002F210 at Validate and Transfer Actuals, Execute SQL Task: Executing the query "call RS2_RealProject_ETL.TransformLoadAction ?, ?, ?, OUTPUT ? " failed with the following error: "ORA-06576: not a valid function or procedure name". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Validate and Transfer Actuals

SSIS package "ActualAdaptor.dtsx" finished: Success

I'd appreciate it if you could help me figuring out what might be the issue.

--Reza

Hi Jamie,

Thanks for reply.

I've read quite some documentation on SSIS, however could you confirm that the syntax that I'm using, is valid per SSIS method?

Thanks,

Reza

Jamie,

I think I had to find another way around for it since the syntax

{call RS2_RealProject_ETL.TransformLoadAction ?, ?, ?, OUTPUT ?}

never worked, couldn't figure out why though.

This time I've used a query to call stored proc-now function- to get the result and it worked.

I guess using SSIS for interaction with Oracle stored procedures, somehow dictates how they have to be written.

Thanks,

Reza

It work.

thanks a lot.

Hi Jamie,

I am trying to update Oracle database with Execute SQL Task. I am reading Client Name into Package variable (varClientName) from SQL Server database and using this variable in WHERE clause to update the Oracle database.

eg

Update Project Set ProjectName = "ABC"  Where ClientName = ?

(with OLEDB provider)

? corresponds to varClientName - Package level variable defined in Execute SQL Task mapping. When I assign the ClientName in varClientName variable , It adds some spaces after the ClientName. Due to this WHERE clause condition does not match. How do I remove the Trailing spaces attached to ClientName stored in varClientName variable before assigning it into where clause?

eg. In debug Mode, I see the value of  varClientName = 'Microsoft          '  before it gets assigned to SQL Statement. Note blank spaces after Microsoft which fails the WHERE clause in Query condition.

I can add Script task and TRIM the leading/traling spaces before assigning it to SQL statement in Execute SQL task.

I want to know if there is any simple method to trim trailing spaces of variable before assignment.

Thanks for your help and support

Kuhsu

Thanks a lot Jamie!

Thanks a lot Jamie!

Hello Jamie,

I had question on executing multiple sql statements in single Execute SQL Task. I have multiple select , update statements and lot of variables in between to populate their values and use these variables in subsequent SQL statements. Can we do this in single Execute SQL task?

Thanks

Himanshu

Thanks Jamie!

I have currency data type in SQL server. I am using Execute SQL task to select Currency value and store it in side user variable. I have defined the datatype of user variable as double. I get an error that the datatypes do not match. What should be the data type of User Variable for storing currency or decimal values and corresponding parameter data type for SQL statement as I want to use this variable as parameter for SQL statement and define the mapping in parameter section. I am using OLEDB provider for connecting to Oracle

Thannks aggain for all your help.

Kushu

Thanks Jamie!

Following error I am getting when executing the package.

[Execute SQL Task] Error: An error occurred while assigning a value to variable "Amount": "The type of the value being assigned to variable "User::Amount" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

User::Amount" if type double and Amount in database is currency. I tried to cast to decimal but same error.

Thanks

Thanks for your help Jamie!

I got the solution for above mentioned question. I converted the Amount to string and defined the variable of type string. While executing the SQL , I defined variable mapping to double and everything worked fine.

Thanks

Himanshu