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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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]


"greenmtnsun" wrote:

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?
.

Relevant Pages

  • desc: invalid sql statement, answered
    ... It is not part of the Oracle SQL language. ... I believe a describe statement is Oracle DDL, but I still failed for both of the ways that I know of to execute sql. ...
    (perl.dbi.users)
  • How can I pass data into a boolean variable in SSIS from Oracle?
    ... In oracle, create a view that basically does something like this select * ... Then I create an EXECUTE SQL step in SSIS. ... of the SQL query. ...
    (microsoft.public.sqlserver.dts)
  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... Hard code WHERE clauses. ...
    (comp.lang.cobol)
  • Re: SQL Injection- Bypassing magic_quotes
    ... Because i was trying to execute: ... Don't terminate the query and you most ... Or try something like bobcat or one of the other SQL injection tools out ... Chief Information Security Officer ...
    (Pen-Test)
  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... explicitly code dummy words as bind variable placekeepers, put the host variables on the ...
    (comp.lang.cobol)