none
Pkg (Server) or Agent to Remotely execute another Pkg on workstation (PC)

    Question

  • Good day, i need help.

    Migrating Servers & SQL from 2005 to 2008.

    We have a package that 1.extracts data & 2.process cognos cubes on the old server, do not want to install Cognos on new server as it will be discontinued in a few months. Old server will also be trashed soon.

    I have been asked to manually check when 1.extracts data pkg has completed & then Manually 2.process cognos cubes on my PC.

    I do not want want this, Is there a way from within the one pkg or agent to Remotly execute another pkg on my PC?

    Please Assist, really would be great if i could do this.

    Friday, May 20, 2011 7:26 AM

Answers

  • Thank You All, i'm going to have to give up on this as it does not seem possible to execute another exe remotly.

    After a week i had little success where only normal extractings work to run remotly but cant seem to exectule exe - but i get no error returned to investigate  - either it runs & waits for response or message but cant seem bypass the message

    • Marked as answer by Ismailc Friday, June 24, 2011 9:08 AM
    Friday, May 27, 2011 2:11 PM

All replies

  • You can try this idea...

    1. Create a job in the server

    2. Add a step to run the extract package

    3. Add next step to start a job (which has the package to process the cognos cube) in your local PC using the code in below link

    http://msdn.microsoft.com/en-us/library/ms403355.aspx

    Or you can do it vice versa also..

    You can create a job in your local PC , with first step as "Run the Extract package in remote server" and second step to "package to process the cube"

    Friday, May 20, 2011 8:01 AM
  • how did you deployed packages?

    did you deployed them on sql server ? if yes, you need to connect the SSMS to run them with DTEXECUI on remote machine.

    you can not connect to remote SSIS server from SSMS.

    but if you want to do it , you can use this guideline to connect to remote SSIS server in SSMS:

    http://msdn.microsoft.com/en-us/library/aa337083.aspx

     

    If you meant anything else please elaborate more.


    http://www.rad.pasfu.com
    Friday, May 20, 2011 2:12 PM
    Moderator
  • Apologies for the late reply,

    Sorna Kumar Muthuraj - i'm struggling with the script (http://msdn.microsoft.com/en-us/library/ms403355.aspx) to execute package on my PC.The pkg needs to run on my PC only, now if i add to the SSIS on server and agent calls it tries to execute on the server, no where in the script does it define the pc location the pkg needs to run on.

    Reza Raad - the script are both similar to Sorna but cant get the service to execute.

    I need the server to execute a job that starts a pkg on my PC

    ?

     

    Monday, May 23, 2011 6:37 AM
  • hi,

    You can execute the package on your machine with the help of batch job instead of SQL SERVER job as below:

    DTEXEC package path--> config file path-->log file path

    Hope this helps !

    Thanks,

    Vaibhav

    Monday, May 23, 2011 8:20 AM
  • 1. I must setup a job to execute a batch file?

    2. Then in Batch file use DTEXEC to trigger pkg on my PC?

    Do you perhaps have an example of the command within the batch, how do i define my PC connection?

    Also does the dtexec open up the pkg or just executes without opening the pkg

    Monday, May 23, 2011 8:48 AM
  • why you insist to locate the package on your PC?

    why you don't deploy the package on the server, and then setup a sql server agent job and run it there?

    do you have any force for this?

     


    http://www.rad.pasfu.com
    Monday, May 23, 2011 9:03 AM
    Moderator
  • The Cognos software is on my PC & not on the server.

    The Pkg fails when on the server due to the software not found to process the cube, the software is not going to be installed on the server due to it being phased out - so i was asked to do it manually untill then.

    But i would like to automate it to run on my PC, instead of me manually excuting when i arrive in the morning & check if data extracted.

    Monday, May 23, 2011 10:22 AM
  • OK,

    you can create a sql server job in sql server on server machine.

    and share the physical folder of package in your local machine.

    and in the job use this path as source of ssis package . ( if you used file system as storage )


    http://www.rad.pasfu.com
    • Marked as answer by Ismailc Monday, May 23, 2011 7:37 PM
    • Unmarked as answer by Ismailc Monday, May 23, 2011 8:08 PM
    Monday, May 23, 2011 10:39 AM
    Moderator
  • Hi ISMAILC,

    1. No. You dont need to create SQL SERVER JOB if this package needs to be executed on demand.

    2. Actually i was using this method very earlier but it goes as follows:

    DTEXEC package path

    Also does the dtexec open up the pkg or just executes without opening the pkg

    --> It just executes the package without opening.

    Hope this helps!

    Regards,

    Vaibhav

    Monday, May 23, 2011 1:11 PM
  • Can you please post an example of the batch containing the dtexec, as i dont know how to insert the code with pkg on PC
    Monday, May 23, 2011 2:57 PM
  • Reza Raad - I tried the sharing pf the pkg on my PC but i get error: cant find the exe on c: to run application
    Monday, May 23, 2011 6:57 PM
  • Thank You All
    Special Thanks Reza Raad - your solution worked for me

    1. Share Package on PC (C:)

    2. Shared Cognos Transformer Exe on PC (\\ZA0019999999FE\Cognos\cer5\bin\trnsfrmr.exe)

    3. Shared Pkg Executable Expression: \\ZA0019999999FE\Cognos\cer5\bin\trnsfrmr.exe

    4. Server SQL Agent Job - SSIS Integration Pkg
                                       - Pkg Source: File System
                                       - Package: \\ZA0019999999FE\Shared\SSIS Cognos Exports\SSIS Cognos Exports Daily.dtsx


    Monday, May 23, 2011 7:37 PM
  • Apologies, i thought it was working but the pkg now runs for longer than an hour & i had to stop it.

    Does not look like its working as no indication just shows under ssis-running pkg

    but pkg runs for 30min on PC but via Agent does not complete

    Monday, May 23, 2011 8:10 PM
  • Plz Help, the pkg executes but never finishes & does not process anything - i had to stop it.

    But on my PC the pkg runs for 30min

    ?

    Tuesday, May 24, 2011 6:24 AM
  • what is the SSIS version in the server? and what is SSIS version  on your local machine?

    maybe they are not compatible


    http://www.rad.pasfu.com
    Tuesday, May 24, 2011 7:58 AM
    Moderator
  • Both using SSIS 2008
    Tuesday, May 24, 2011 8:04 AM
  • Do you receive any error during execution from server?

    also to Note: when you run the package form server, the package will run by SSIS engine on server machine not your local machine, regards to physical dtsx file on your local computer. this means that when you run another application by your package, for example cognosTransformer.exe , this will run by server machine, if server machine couldn't run this process then you may got bad results.


    http://www.rad.pasfu.com
    Tuesday, May 24, 2011 8:16 AM
    Moderator
  • but i get no error, nothing, just runs forever.
    Tuesday, May 24, 2011 8:45 AM
  • does the use who runs the sql server agent service, has appropriate permission to access shared folders?
    http://www.rad.pasfu.com
    Tuesday, May 24, 2011 9:13 AM
    Moderator
  • Yes, i made sure of that (read/write)
    Tuesday, May 24, 2011 9:19 AM
  • Please check this http://blogs.technet.com/b/reeds/archive/2006/09/22/how-to-remotely-execute-an-ssis-package.aspx

     


    http://uk.linkedin.com/in/ramjaddu
    Tuesday, May 24, 2011 9:53 AM
  • RamJaddu - this is to remotly execute a job on SQL server

    The package is on my PC and i need the server to remotely execute on my PC :(

    Any Ideas, not winning?

    Tuesday, May 24, 2011 11:04 AM
  • why don't you try running package remotely using dtexec utility.

    dtexec /f \\ServerName\FolderName\Package.dtsx.

    make sure SQL Agent service account should have enough permissions on both server (ie UNC path)

    Please check for more here http://msdn.microsoft.com/en-us/library/ms162810.aspx

     


    http://uk.linkedin.com/in/ramjaddu
    Tuesday, May 24, 2011 11:23 AM
  • Tried the below on cmd job

    "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "\\ZA0019999999FE\Shared\SSIS Cognos Exports\SSIS Cognos Exports Daily.dtsx"

    Again no failure no error, losing this battle :(

    Tuesday, May 24, 2011 12:50 PM
  • Hi, I removed the process of the cube in the pkg on my PC using : dtexec /f "\\ZA0019999999FE\Shared\SSIS Cognos Exports\SSIS Cognos Exports Daily.dtsx"

    and it works nicely but the process of cube part just does not finish.

    How do i ensure it runs faster or runs - now that i know the pkg does execute

    ?

    Tuesday, May 24, 2011 1:33 PM
  • Unfortunatly it does not work, 3hrs later i had to stop the job with pkg execute the exe on my PC

    but without the exe it runs fine

    ?

    Tuesday, May 24, 2011 6:35 PM
  • Thank You All, i'm going to have to give up on this as it does not seem possible to execute another exe remotly.

    After a week i had little success where only normal extractings work to run remotly but cant seem to exectule exe - but i get no error returned to investigate  - either it runs & waits for response or message but cant seem bypass the message

    • Marked as answer by Ismailc Friday, June 24, 2011 9:08 AM
    Friday, May 27, 2011 2:11 PM