SQL Server Performance

SSIS Package to automate another SSIS Package.

Discussion in 'SQL Server 2005 Integration Services' started by tempdb, Jul 17, 2009.

  1. tempdb New Member

    Hi ,
    I will try to be short and clear.
    We have SSIS job to transfer data from Oracle to SQL Server that runs daily in the morning at 9:00 am.
    This package should run after the jobs on the Oracle database are complete,That is as soon as my Oracle database gets data my SSIS package (Moving Data from Oracle to SQL Server) should start.
    I do not know what jobs are running on the Oracle,The only thing I have is a Query that Shows the Status of the jobs,If my Query shows that Status of all the Oracle jobs are Finished then I can run my SSIS package for dataflow between oracle and SQL Server.
    The Query that is in PL-SQL returns 2 Columns and 40 rows: Status Column and the Name of the job Column.
    Any Ideas How we can do this?
    My approach:
    I wrote a package having a Execute SQL Task and it executes my Oracle query and populates the result set into a variable in SSIS,but I dont know how to use the result set and Check the condition for if status = finished ,then execute my package i.e Oracle to SQL.
    Hope you all understood.
    Pls Help Me Regarding This.
    Anand.
  2. johnson_ef Member

    HiAnand,Its bit interesting..the idea, which is coming in mind to automate this task is, you can call the Oracle Job( using some script or executable, if possible ) and add this task as first step in SSIS, and on success of failure, start the Orcale to SQL data transfer using SSIS.Doing this way, you can you can achieve like, monitor from one place, and no need to run another script to verify the first Oracle job is completed or not. Because, for verifying this job, you need to schedule or configure an alert to achieve this, and this is an overhead.I strongly recommend that, script out the Oracle task and execute it as Command Line executable in SSIS, and add the Orcale to SQL data transfer as second step. Think about it.Regards-Johnson
  3. ghemant Moderator

    Anand,How about, capturing the output to some staging table and then compare the status by looping through record and then move to step 2 (executing your SSIS Package)
  4. tempdb New Member

    Thanks for the ideas Johnson and Hemanth,
    This weekend I worked on it and made it work,
    Here is my strategy,Its similar to what Hemantgiri Said:
    1) I took an execute sql task to execute the PL-SQL code.(I got the Jobs that were finished and that were pending)
    2)I took a dataflow task .(I put the result set of PL-SQL code into a SQL Server Table )
    3) I used an execute SQL Task that had a Variable which says select status from table.
    4) I put expresiions in the precedence constraints saying @variable == "finished " then go and execute my package(get data from oracle to SQl)
    or if @variable !== "finished " then again go to an execute SQl task that has waitfor delay command that waits for 30 mins and then executes my package.
    But now I am aving an other issue,
    After me waiting for 30 mins I do not want to directly execute the package,I want to go back to my Step 1 check the query and come from there.
    SO ANY IDEAS HOW TO LOOPBACK TO STEP 1 EVERY TIME I WAIT FOR 30 MINS??
    Thanks in Advance,
    Anand.
  5. DreTheDBA New Member

    There is a third party tool from SQLSentry (www.sqlsentry.net) called Event Manager. This is a tool that allows you to "chain" jobs and packages together to create a workflow that determines when a given package should or should not run. This is a pretty slick tool that we use pretty heavily. We only use it for SQL Server, but they have an Oracle component as well. Check it out, it might have what you're looking for.

Share This Page