How can I pass data into a boolean variable in SSIS from Oracle?
- From: greenmtnsun <greenmtnsun@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 30 Jan 2009 15:28:01 -0800
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 resultsof 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?
.
- Prev by Date: "String data trunc.." error on first SSIS run and no error on 2nd
- Previous by thread: "String data trunc.." error on first SSIS run and no error on 2nd
- Index(es):
Relevant Pages
|
