none
Rework SSIS job - call existing job twice RRS feed

  • Question

  • I've created an SSIS job which downloads data from a system and transfers it to SQL.  It works fine and is in Production.

    I want to rework this system to upload data also.  The plan is to call the download routine to refresh the data, run a new upload routine, and then call the download routine again to fetch any updates carried out in the upload routine.

    I wouldn't consider myself an expert in SSIS and thinking about how to do this?  Ideally I would want to avoid creating any new dtsx files as deployment them is a bit painful.

    TIA

    Monday, April 15, 2019 2:38 PM

All replies

  • Ok, If I understand correctly. 

    Task A Download data from SystemPippo to TableSQL

    Task B Upload data from TableSQL to SystemPippo

    Task C Download data from SystemPippo to TableSQLTemp 

    Task D Compare TableSQL and TableSQLTemp to see if there was some update in SystemPippo during the task A and B.

    It looks to me as an infinite loop. 

    Monday, April 15, 2019 3:07 PM
  • The upload data is coming from another SQL system so it's:

    Task A Download data from SystemPippo to TableSQL

    Task B Upload data from System2 to SystemPippo

    Task C Download data from SystemPippo to TableSQLTemp 

    Monday, April 15, 2019 3:44 PM
  • Are you going to use SSIS to trigger the Task B? If not I don't see any reason to compare, just overwrite the latest version of SystemPippo to TableSQL completes the requirement. 

    Unless you want to manipulate the data or you want to keep a log of the data flow. 

    If it yes (you use SSIS to trigger the Task B) I'd suggest you to transform the Task B to the task A and after the Task A trigger the Task B (download the dataset to the TableSQL). 

     


    Monday, April 15, 2019 3:51 PM
  • Hi,

    Thanks for your help - the logic isn't an issue. 

    I just want to know how to run an existing job twice in succession (with another job in between).  The existing job is in a dtsx file.

    Thanks

    Tuesday, April 16, 2019 9:48 AM
  • You don't need to. 

    You can use a dtsx and a job to do all the work. In your package you should see three data flow on sequence. 

    Wednesday, April 17, 2019 8:31 AM
  • I'm not sure what you mean.  

    I have an existing dtsx which is the download action.

    I want to run the current dtsx, (some new actions), then the current dtsx again

    Wednesday, April 17, 2019 9:24 AM
  • You can achieve this either "playing around" with the job (setting three steps) or encapsulating the data flow in one dtsx. 

    Wednesday, April 17, 2019 10:59 AM