SSIS: Retrieving data from Oracle to parameter

[Abba]

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

[Todd C]

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]

Post by Abba
Hello,
I am facing an error while retrieving data from Oracle into a variable. This
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
)
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
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
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

[Abba]

Todd,

Post by Todd C

Post by Todd C
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.

Post by Todd C

Post by Todd C
What happens if you set the Variable Type to String and execute just the
step?

[Abba] Same error as above

Post by Todd C

Post by Todd C
Is the Result Set property (on the General page) set to Single row?

[Abba] Yes

Post by Todd C

Post by Todd C
Is the Result Name (on the Result Set page) set to 0?

[Abba] Yes

Post by Todd C

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

[Abba] OLE DB

Post by Todd C
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]

Post by Todd C
Hello,
I am facing an error while retrieving data from Oracle into a variable. This
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
)
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
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
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

[Todd C]

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]

Post by Abba
Todd,

Post by Todd C

Post by Todd C
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
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.

Post by Todd C

Post by Todd C
What happens if you set the Variable Type to String and execute just the
step?

[Abba] Same error as above

Post by Todd C

Post by Todd C
Is the Result Set property (on the General page) set to Single row?

[Abba] Yes

Post by Todd C

Post by Todd C
Is the Result Name (on the Result Set page) set to 0?

[Abba] Yes

Post by Todd C

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

[Abba] OLE DB

Post by Todd C
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]

Post by Todd C
Hello,
I am facing an error while retrieving data from Oracle into a variable. This
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
)
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
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
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

[Abba]

Todd,

Thanks for your reply.

Post by Todd C

Post by Todd C
To_CHAR is not a SQL function or valid statement.

I am executing that statement against Oracle and NOT against SQLServer.


Abba

Post by Todd C
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.
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]

Post by Todd C
Todd,

Post by Todd C

Post by Todd C
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
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.

Post by Todd C

Post by Todd C
What happens if you set the Variable Type to String and execute just the
step?

[Abba] Same error as above

Post by Todd C

Post by Todd C
Is the Result Set property (on the General page) set to Single row?

[Abba] Yes

Post by Todd C

Post by Todd C
Is the Result Name (on the Result Set page) set to 0?

[Abba] Yes

Post by Todd C

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

[Abba] OLE DB

Post by Todd C
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]

Post by Todd C
Hello,
I am facing an error while retrieving data from Oracle into a
variable.
This
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
)
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
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
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

[Jon Waterhouse]

Won't help you with your immediate error, but....

Is Nov 10 2007 before or after Jan 10 2008? Most people would say before,
but
as strings '11/10/2007' is greater than '01/19/2008'

You should be converting your string to a date, not your date to a string.

Post by Abba
Todd,

Post by Todd C

Post by Todd C
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
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.

Post by Todd C

Post by Todd C
What happens if you set the Variable Type to String and execute just the
step?

[Abba] Same error as above

Post by Todd C

Post by Todd C
Is the Result Set property (on the General page) set to Single row?

[Abba] Yes

Post by Todd C

Post by Todd C
Is the Result Name (on the Result Set page) set to 0?

[Abba] Yes

Post by Todd C

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

[Abba] OLE DB

Post by Todd C
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]

Post by Todd C
Hello,
I am facing an error while retrieving data from Oracle into a variable. This
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
)
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
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
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