SSIS or MSSQL Calling an Oracle Package Procedure and Select


prefix: don't have control on oracle side change or add code!

need connect oracle db, call init procedure (format: package_name.procedure_name), run select statement.
these 2 actions have done in sequence within same oracle session .

tips on how either in siss or directly on mssql db?

regards

jacco

can describe need results select statement?

in ssis, use execute sql task 2 commands imagine, , connect either oledb source or ado.net source oracle. provider used to connect oracle partially dictates if procedures can run or not. procedure return results, or prep work within database? execute sql task can handle results sets , put them variables, won't insert server/destination.

there property retainsameconnection = true on connection manager, means when 1 connection open, connection remains open , reused later component, , stays open until end of package.  scenario described @ http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx

it helpful in kind of sample reused same connection:
1. control flow - execute sql task against oracle connection (connection 1) run procedure
2. succeess precedence constraint next step (don't run else in parallel against connection)
3. dataflow task containing source against same kind of connection manager connection 1. run select there.

the theory that the connection used first connection opened in 3 technically same previous connection step 1. works, test make sure same connection.

even retainsameconnection spin many open connections in pool of connection parallel actions particular connection manager. in above example if had two sources in dataflow same connection manager, 1 of 2 reused connection, , 1 brand new connection, , both connections stay open life of package. reassignment of open connection non deterministic, can't sure 1 of 2 in future if need use same connection later.

hope helps, thx, jason
didn't enough here? submit case microsoft customer support team deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance


SQL Server  >  SQL Server Integration Services



Comments

Post a Comment

Popular posts from this blog

Reindexing

hi     using sql server 2005 ee sp4. our database size 150 gb. weekly once schedule reindex job. ran 4 5 hours maximum. but recent days getting more 13 hours. the possibilities there? ragu thangavel how perform reindex? maint plan? or check fragmentation first , reorganize fragmented? if later, quite more data nowadays fragmented before. if not, consider doing "smart" defrag (ola.hallengren.com). tibor karaszi, sql server mvp | web | blog SQL Server  >  SQL Server Database Engine
Read more

Users List

hello everyone, i"m builing tool tfs , want user list form tfs "assigned to" field. if 1 can post code review i"ll gled.   thanks hello, to imports users tfs, can try this: using microsoft.teamfoundation.server; using microsoft.teamfoundation.client; using microsoft.teamfoundation.workitemtracking.client; icredentialsprovider provider = new uicredentialsprovider (); teamfoundationserver tfs = teamfoundationserverfactory .getserver(“your_server_name”, provider); tfs.authenticate(); igroupsecurityservice gss = ( igroupsecurityservice )tfs.getservice( typeof ( igroupsecurityservice )); identity idsid = gss.readidentity( searchfactor .accountname, "team foundation valid users" , querymembership .expanded); identity [] idusername = gss.readidentities( searchfactor .sid, idsid.members, querymembership .none); foreach ( identity id in idusername) {       if (id != null && id.type == identitytype .windowsuser) ...
Read more

List of charts

Image
i'm trying build report show activity for reservations placed and satisfied month branch. the report has parameter the item category and dataset has fields month category branch reservations placed reservations satisfied on front sheet i've created system wide figures 2 columns per month and a matrix broken down category (e.g. child fiction, child non fiction, adult fiction, adult non fiction) has row , column totals. i want have page each branch in system. what's best way of approaching this?  add parent group based on branch field in row groups  and add page breaks repeat matrix report every branch. please mark answer if post solved problem. milan das SQL Server  >  SQL Server Reporting Services, Power View ...
Read more