Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
pl/sql stored procedure

已答覆 pl/sql stored procedure

  • Monday, February 11, 2013 12:24 AM
     
     
    Sign In to Vote
    0

    I created a package which loads all the data from the files coming to source and load it to the oracle destination.

    for this i have used execute sql task ( for setting batch ID) followed by foreach loop in that i've script task (created file parameters for looping ) followed by dataflow task. In data flow task i had taken the flat file source and map the columns followed by derived column for some conditions and changing the data types followed by oledb command ( which inserts the data into specified columns) -- i have done all these steps because the files has thousands of records which comes daily.

    my question is " all the data which are loaded into oracle tables should meet company business rules/specifications mentioned if not i have to create other tables which loads the exceptions in that package/data" for this someone created pl/sql stored procedure in that three parameters like errorid,error description, something like this. I used this pl/sql stored procedure and write a script in script task and exceuted it is runing succesfully, but where should i have to put this stored procedure calling script  task in that above mentioned package to create exceptions. shall i place it in control flow below the foreach loop? please i need suggestions, whatever you understand here please share with me. that would b very helpfull. Thanks


     

All Replies

  • Monday, February 11, 2013 12:31 AM
     
     
    Sign In to Vote
    0

    If you've implemented some of the ETL processing using PL/SQL stored procedures, then a control flow execute task can be used to execute the procs after loading the data into staging tables.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

     
  • Monday, February 11, 2013 1:30 AM
     
     
    Sign In to Vote
    0

    so i have to place that execute sql task right right after forachloop in control flow. thats what i understood from your reply. am  i correct dan?

    thanks alot for this help

     
  • Monday, February 11, 2013 3:52 AM
     
     
    Sign In to Vote
    0

    If you will execute PL/SQL for each row of the data flow, you will kill the performance.

    You need to execute this procedure only once.

    If you are loading data from Oracle to MS SQL: you first execute the SP to somehow mark the rows in the Oracle database and the use Conditional Split transformation in SSIS to send it to different tables.

    From MS SQL to Oracle: rewrite procedure to T-SQL or first load data into Oracle stagin table and then us stored procedure to move data from staging table to destination tables.

     
  • Monday, February 11, 2013 4:07 AM
     
     
    Sign In to Vote
    0

    Hi palka,

    thanks for your response..!

    i'm loading the data from flat file to oracle database, during this load if i need to load exceptions into the another oracle table we've created a pl/sql stored procedure. In order to execute this stored procedure and load the excetions in to oracle db, where should i call the procedure. i used script task to call the procedure, in that script task i created the parameters which is given in pl/sql stored procedure. but i'm confused where should i call this storedprocedure (scripttask ) in package? I'm new to ETL please let me understand clearly on this.

    Thanks for your patience and your attention on my question.. i really appreciate it if your answer works for me.. :)

     
  • Monday, February 11, 2013 4:15 AM
     
     
    Sign In to Vote
    0

    What kind of these exceptions? can you give some examples with data that will help us in understanding the issue?

    What is the procedure does?

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

     
  • Monday, February 11, 2013 5:14 AM
     
     Answered
    Sign In to Vote
    0

    As long as your are executing the proc for each batch (in the control flow flow) rather than for each row (in the data flow), I would expect decent performance, assuming you have appropriate indexes in place.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked As Answer by fresher-geek Wednesday, February 13, 2013 1:30 AM
    •  
     
  • Monday, February 11, 2013 2:15 PM
     
     
    Sign In to Vote
    0
    If insurance number is not received then system creates member not found..exception record..like these kind of exceptions
     
  • Tuesday, February 12, 2013 4:49 AM
     
     Answered
    Sign In to Vote
    0

    That kind of exceptions (i.e. not depended on some reference data) you should implement in the SSIS without using any external stored procedures.

    Use conditional split or script component and implement all logic in .NET.

    You can load all data also in one staging table and the use PL/SQL to process it after loading.

    • Marked As Answer by fresher-geek Wednesday, February 13, 2013 1:30 AM
    •