SSIS: Retrieving data from Oracle to parameter

#41

Todd,

Thanks for your reply.

Quote:

To_CHAR is not a SQL function or valid statement.
I am executing that statement against Oracle and NOT against SQLServer.

Abba

"Todd C" <ToddC (AT) discussions (DOT) microsoft.com> wrote

Quote:

To_CHAR is not a SQL function or valid statement.

SQL is smart enough to recognize the literal '01/10/2008' as a valid
DateTime value and will do the proper comparison, provided your field upd
is
also DateTime, which it looks like it is.

Try this SQL instead:

select count(*) AS Count
from zipcode_TEST
where upd > '01/10/2008'

If need be, copy and paste it into a SSMS query window to test it out.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"Abba" wrote:

Todd,

Thanks for looking into this. My answers:

Have you tried replacing the reference to the parameter with a literal
value and executing just that step?
[Abba][Execute SQL Task] Error: Executing the query ""select count(*) AS
Count from zipcode_TEST where TO_CHAR(upd,'mm/dd/yyyy') > '01/10/2008'""
failed with the following error: "An error occurred while extracting the
result into a variable of type (DBTYPE_I4)". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters
not set correctly, or connection not established correctly.

What happens if you set the Variable Type to String and execute just
the
step?
[Abba] Same error as above

Is the Result Set property (on the General page) set to Single row?
[Abba] Yes

Is the Result Name (on the Result Set page) set to 0?
[Abba] Yes

Is the Connection type OLE DB, ADO, or ADO.NET? Different Connection
Types
expect different Result Names to make them work.
[Abba] OLE DB

"Todd C" <ToddC (AT) discussions (DOT) microsoft.com> wrote in message
news:E0EAF5C1-F317-4060-BCF0-484F4DF5662F (AT) microsoft (DOT) com...
Have you tried replacing the reference to the parameter with a literal
value
and executing just that step? What happens if you set the Variable Type
to
String and execute just the step?

Is the Result Set property (on the General page) set to Single row?

Is the Result Name (on the Result Set page) set to 0?

Is the Connection type OLE DB, ADO, or ADO.NET? Different Connection
Types
expect different Result Names to make them work.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the
appropriate
answer at the bottom]

"Abba" wrote:

Hello,

I am facing an error while retrieving data from Oracle into a
variable.
This
is the scenario:

1. I have table in Oracle and SQLServer with the same structure as
below.
My
SSIS package is to sync data from Oracle to SQLServer
CREATE TABLE [dbo].[ZipCode_TEST]
(
[ZipCode] [char](5) NOT NULL,
[Upd] [datetime] NOT NULL
)

2. The first "Execute SQL Task" runs against SQLServer and runs the
T-SQL:
SELECT ISNULL(CONVERT(VARCHAR,MAX(upd),101),0) As Upd from
ZipCode_TEST
and
stores the resultset to a parameter [User::sqldtecnt] that has
valuetype
as
'object'

3. The second "Execute SQL Task" runs against Oracle and executes the
command "select count(*) AS Count from datahq_am.am_zipcode_staging
where
TO_CHAR(update_date,'mm/dd/yyyy') > "+ @[User::sqldtecnt] which is
stored
in
a variable. The query parsed correctly. And I have set the resultset
to a
parameter orclcnt which is of valuetype 'single'

4. When I execute the package, it gives me the error - [Execute SQL
Task]
Error: Executing the query ""select count(*) AS Count from
ZipCode_TEST
where TO_CHAR(upd,'mm/dd/yyyy') > "+ @[User::sqldtecnt]" failed with
the
following error: "An error occurred while extracting the result into a
variable of type (DBTYPE_I4)". Possible failure reasons: Problems with
the
query, "ResultSet" property not set correctly, parameters not set
correctly,
or connection not established correctly.

I tried changing variable orclcnt to int32,int64 but all in vain. Can
anyone
help?

TIA,
Abba

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SSIS: Retrieving data from Oracle to parameter

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: SSIS: Retrieving data from Oracle to parameter - 11-23-2008 , 10:35 PM