Running SSIS Packages "outside" of SQL Server?
This might seem like a weird question, but do SSIS packages necessarily have to run within SQL Server (ie: under SQL Server service)? I know you can choose to *store* the package inside SQL (msdb) or on the file system, but I'm talking purely in terms of execution.
Reason I ask is I have a client who uses SQL Server (2005) Agent to run an SSIS package every minute, every day. After at least 6 days, the SQL Agent job will "break"... no errors are logged, and when they try to restart SQL Agent, it doesn't do so cleanly. They can try to kick off the job manually, but it won't run. The only solution is to reboot the server, then everything works fine again for another 6-7 days.
They are asking about "externalizing" the SSIS job - use Windows Scheduled Tasks for scheduling, and whatever other method (eg: OSQL?) to execute the package. Up front the 6-7 day lag suggests maybe a memory leak is to blame. But my question is, even if I externalize the triggering and use another method (eg: dtexec or dtexecui), do those methods still cause execution to be run under SQL Server service?
Thanks.
Reason I ask is I have a client who uses SQL Server (2005) Agent to run an SSIS package every minute, every day. After at least 6 days, the SQL Agent job will "break"... no errors are logged, and when they try to restart SQL Agent, it doesn't do so cleanly. They can try to kick off the job manually, but it won't run. The only solution is to reboot the server, then everything works fine again for another 6-7 days.
They are asking about "externalizing" the SSIS job - use Windows Scheduled Tasks for scheduling, and whatever other method (eg: OSQL?) to execute the package. Up front the 6-7 day lag suggests maybe a memory leak is to blame. But my question is, even if I externalize the triggering and use another method (eg: dtexec or dtexecui), do those methods still cause execution to be run under SQL Server service?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry I missed this part - my bad..."But my question is, even if I externalize the triggering and use another method (eg: dtexec or dtexecui), do those methods still cause execution to be run under SQL Server service?"
If you export the SSIS as a DTSX file they don't need SQL Service to run but the dtexec utility so you can put them on a separate computer from SQL box
If you export the SSIS as a DTSX file they don't need SQL Service to run but the dtexec utility so you can put them on a separate computer from SQL box
>>If you export the SSIS as a DTSX file they don't need SQL Service to run but the dtexec utility<<
Actually if you are running the command line untility DTSEXEC it dows require the SQL Server Integration service.
Actually if you are running the command line untility DTSEXEC it dows require the SQL Server Integration service.
Also, keep in mind that when running SSIS on a separate machine any database activity will affect SQL Server.
The big advantage of using a separate machine is where you are doing a lot of importing and/or exporting of text files as well as any process unrelated to SQL Server.
The big advantage of using a separate machine is where you are doing a lot of importing and/or exporting of text files as well as any process unrelated to SQL Server.
ASKER
It seems as this point that the SQL Server Agent itself is the culprit (and not SQL Server itself). That being said, does assigning the triggering to Windows Scheduled Tasks and execution to something like OSQL (assuming I can trigger an SSIS package from OSQL), does this seem to be my best alternative?
You could use "Windows Scheduled Tasks" to run dtexec but you don't have a lots of goodies that SQL agent has built in already (notification, history etc.) and I suspect that not "SQL Server Agent itself is the culprit" anyway. How do did you scheduled the package to run? Do you have it saved under SSIS MSDB data store and right clikec and scheduled it from there?
>>That being said, does assigning the triggering to Windows Scheduled Tasks and execution to something like OSQL (assuming I can trigger an SSIS package from OSQL), <<
Not really, the best way is to execute the DTEXEC command line utility.
Not really, the best way is to execute the DTEXEC command line utility.
ASKER
lcohan: isn't converting the SSIS package to at DTSX package considered a downgrade? Is it not possible to execute at SSIS package "as-is" from a command line?
ASKER
Never mind... according to MS, dtexec can be used with both SQL 2005 and SQL 2008, without converting from SSIS to DTS.
Not really a downgrade if you need to have it as a (DTSX) file on a separate box from SQL server box.
>>Is it not possible to execute at SSIS package "as-is" from a command line? <<
Of course, that is what the DTEXEC command line utility is for.
>>without converting from SSIS to DTS.<<
I have never heard of anyone converting from SSIS to DTS. I would be surprised if it can be done.
Of course, that is what the DTEXEC command line utility is for.
>>without converting from SSIS to DTS.<<
I have never heard of anyone converting from SSIS to DTS. I would be surprised if it can be done.
ASKER
This is what I ended up using - we'll see if it avoids the problem we were having with SQL Agent locking up. Thanks!
Not from SSIS to DTS but DTSX - that is the defualt file extension (XML file type) if you right click the SSIS package and "Export Package" as file.
The good thing I like about this is that in conjunction with an xml config file for the package you can have the same package run on/against different servers where connection values are taken from the config file. And you can VSS or SVN the DTSX file versus just have them in SQL SSIS.
Hope this helps.
The good thing I like about this is that in conjunction with an xml config file for the package you can have the same package run on/against different servers where connection values are taken from the config file. And you can VSS or SVN the DTSX file versus just have them in SQL SSIS.
Hope this helps.
ASKER
Thanks Icohan... it wasn't as much about removing the task from the server itself as much as it was about removing it as much as possible from SQL Agent, if not SQL Server itself (as much as can be done with using an SSIS package)
You are welcome.
You can run SSIS package on any box that has a licensed copy of SQL Server.