How can I pass data into a boolean variable in SSIS from Oracle?

[greenmtnsun]

I have data in a table in Oracle that I want to query tp simply find out if
something is true or false. Then, I want to tell SSIS to evaluate whether
its true or false and decide if it should execute a certain step.

So far, the only way I have been able to do that is as follows:
1. In oracle, create a view that basically does something like this select *
from table1.
2. In SQL Server I do the following:

declare @ARE_THERE_DUP_ACTIVE_ACCOUNTS as BIT

SELECT @ARE_THERE_DUP_ACTIVE_ACCOUNTS = (CASE WHEN
SUM(ARE_THERE_DUP_ACTIVE_ACCOUNTS)>0 THEN 1
ELSE 0
END
)
FROM
(
SELECT Count(*) as ARE_THERE_DUP_ACTIVE_ACCOUNTS from
LINKED_SERVER..SCHEMA.table1
UNION ALL
SELECT 0
) sub_select

select @are_there_dup_active_accounts as Are_There_Dup_Active_Accounts

3. Then I create an EXECUTE SQL step in SSIS.
4. I create a variable, in this case it's name is
Are_There_Dup_Active_Accounts as boolean
5. I create a step after the execute that I want to execute conditionally
and I link the first step into the second.
6. Then I create a precedence and evaluate on a Expression like this...
@[User::ARE_THERE_INC_BILL_STAGE_RUNS]

From there, the second step will run conditionally, depending on the results
of the SQL query. When this is SQL server data, its easy, but when its
Oracle, I am left with doing extra steps like creating a useless view and
granting privileges for the linked server just so that I can use SQL server's
TSQL which is compatible with SSIS's boolean data type.

Does anyone know of a better way?

[Todd C]

Why can't your Execute SQL Task go against Oracle directly and return a 0 or
1 as appropriate, and use an Integer variable instead of boolean?
Set your precedence constraint as "@[User::...]==1"
--
Todd C

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

Post by greenmtnsun
I have data in a table in Oracle that I want to query tp simply find out if
something is true or false. Then, I want to tell SSIS to evaluate whether
its true or false and decide if it should execute a certain step.
1. In oracle, create a view that basically does something like this select *
from table1.
SUM(ARE_THERE_DUP_ACTIVE_ACCOUNTS)>0 THEN 1
ELSE 0
END
)
FROM
(
SELECT Count(*) as ARE_THERE_DUP_ACTIVE_ACCOUNTS from
LINKED_SERVER..SCHEMA.table1
UNION ALL
SELECT 0
) sub_select
3. Then I create an EXECUTE SQL step in SSIS.
4. I create a variable, in this case it's name is
Are_There_Dup_Active_Accounts as boolean
5. I create a step after the execute that I want to execute conditionally
and I link the first step into the second.
6. Then I create a precedence and evaluate on a Expression like this...
@[User::ARE_THERE_INC_BILL_STAGE_RUNS]
From there, the second step will run conditionally, depending on the results
of the SQL query. When this is SQL server data, its easy, but when its
Oracle, I am left with doing extra steps like creating a useless view and
granting privileges for the linked server just so that I can use SQL server's
TSQL which is compatible with SSIS's boolean data type.
Does anyone know of a better way?

[greenmtnsun]

Because I wasn't previously able to do that until someone on the forum said
to use a double equal sign. == I was using = which wasn't working.

I was only able to get a boolean through this thing because of that.

Thanks. I should have said that someone said the same thing over in the
MSDN forum.

Post by Todd C
Why can't your Execute SQL Task go against Oracle directly and return a 0 or
1 as appropriate, and use an Integer variable instead of boolean?
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by greenmtnsun
I have data in a table in Oracle that I want to query tp simply find out if
something is true or false. Then, I want to tell SSIS to evaluate whether
its true or false and decide if it should execute a certain step.
1. In oracle, create a view that basically does something like this select *
from table1.
SUM(ARE_THERE_DUP_ACTIVE_ACCOUNTS)>0 THEN 1
ELSE 0
END
)
FROM
(
SELECT Count(*) as ARE_THERE_DUP_ACTIVE_ACCOUNTS from
LINKED_SERVER..SCHEMA.table1
UNION ALL
SELECT 0
) sub_select
3. Then I create an EXECUTE SQL step in SSIS.
4. I create a variable, in this case it's name is
Are_There_Dup_Active_Accounts as boolean
5. I create a step after the execute that I want to execute conditionally
and I link the first step into the second.
6. Then I create a precedence and evaluate on a Expression like this...
@[User::ARE_THERE_INC_BILL_STAGE_RUNS]
From there, the second step will run conditionally, depending on the results
of the SQL query. When this is SQL server data, its easy, but when its
Oracle, I am left with doing extra steps like creating a useless view and
granting privileges for the linked server just so that I can use SQL server's
TSQL which is compatible with SSIS's boolean data type.
Does anyone know of a better way?