Automic Workload Automation

Expand all | Collapse all

UC4 MSSQL Agent & SSIS

Jump to Best Answer
  • 1.  UC4 MSSQL Agent & SSIS

    Posted 11-26-2013 03:28 PM

     

    We recently purchased the SQL agent for MSSQL db's (we already had the SQL agent for Oracle).  I have a user now asking about support for SSIS (SQL server integration services).  I honestly have no idea.  I'm the UC4 Administrator - I install the agent, I get it functional and know how to create a job that uses it and connects to an MSSQL db, but beyond that - not really my area of expertise.

    Is anyone familiar with SSIS and can explain what it is and/or how (or even if) it integrates or can be used with the MSSQL agent?

    Thanks.

     



  • 2.  UC4 MSSQL Agent & SSIS

    Posted 03-11-2015 12:22 PM
    @Stephanie, now I do the same thing with the SSIS packages and SSRS. Works like a charm!!


  • 3.  UC4 MSSQL Agent & SSIS

    Posted 01-15-2014 10:25 AM

    Thanks guys.  The people involved managed to figure it out.  They are running SSIS packages from a SQL job.

    Thank you for the explanations.  Every little bit helps.



  • 4.  UC4 MSSQL Agent & SSIS

    Posted 01-13-2014 10:20 AM

    Hi Laura,

    I know exactly how you feel. I've recently become the point of contact for our Automic instances, but have a long way to go to reach the same level of comprehension as the previous individual.

    To your question, I will second what Matthew stated. We have several teams that leverage a Windows agent to execute SSIS packages from the command line, using dtexec. My understanding is that these packages can be created/exported from MS SQL Server. So far I've not heard/seen anyone directly executing ssis packages from Automic (without dtexec in the middle) with SQL agent. Hope this helps!



  • 5.  UC4 MSSQL Agent & SSIS

    Posted 02-09-2015 12:01 PM
    JGi604607 
    I would invoke the SSRS from a SSIS package.
    Invoke the SSIS package via the DTExec on a Windows server (agent).
    ie.
    "c:\Program Files (x86)\Microsoft SQL Server\example\\Binn\DTExec.exe" /file "\\servername\ssis$\Path\to\dtsxfile\Example.dtsx" /ConfigFile "\\servername\ssis$\Path\to\configfile\Example.dtsConfig"


  • 6.  UC4 MSSQL Agent & SSIS

    Posted 03-10-2015 09:15 AM
    1.  I run SSIS packages using Automic (UC4) with a dtexec command.  We chose to run them in UC4 for several reasons.  One, we can run  packages from several different SQL servers in one place and monitor them all.  We had issues with the error alerts on the SQL server, so having them run in UC4, we know we will get notified if there is an error.
    2. SSRS - I set up subscriptions for the reports, run a query to find the SQL job that runs the subscription, then I set up a UC4 job to run the SQL job.  Works great!


  • 7.  UC4 MSSQL Agent & SSIS
    Best Answer

    Posted 01-13-2014 07:31 AM
    Hi Laura.

    SSIS packages can be executed from the Windows command line using the dtexec utility.  You can find more details in http://technet.microsoft.com/en-us/library/ms162810(v=sql.105).aspx.  Since you executing a command line utility, you will be using the Automic Windows Agent instead of the SQL Agent.

     



  • 8.  UC4 MSSQL Agent & SSIS

    Posted 02-09-2015 12:07 PM
    How do I do that if the SSRS is in .rdl format?


  • 9.  UC4 MSSQL Agent & SSIS

    Posted 02-09-2015 11:43 AM
    I have a similar question, but I am working with SSRS. Does anyone know how to set it up in UC4?


  • 10.  UC4 MSSQL Agent & SSIS

    Posted 02-09-2015 12:09 PM
    Please don't open the attachment. I am not sure what it is. I didn't attach anything, but it showed up in my post.


  • 11.  UC4 MSSQL Agent & SSIS

    Posted 02-09-2015 02:02 PM
    This is from an oldder blog, so there may be updated info available. 
    https://msbiblog.wordpress.com/2009/05/19/run-and-export-ssrs-reports-from-ssis-sql-server-2005/