• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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.
0
jdallen75
Asked:
jdallen75
  • 6
  • 5
  • 5
1 Solution
 
Anthony PerkinsCommented:
>>This might seem like a weird question, but do SSIS packages necessarily have to run within SQL Server<<
You can run SSIS package on any box that has a licensed copy of SQL Server.
0
 
lcohanDatabase AnalystCommented:
Assuming you have the sufficient rights to hit SQL server DBs you can run them from anywhere by using dtexec Utility - http://msdn.microsoft.com/en-us/library/ms162810(v=SQL.90).aspx
0
 
lcohanDatabase AnalystCommented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
>>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.
0
 
Anthony PerkinsCommented:
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.
0
 
jdallen75Author Commented:
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?
0
 
lcohanDatabase AnalystCommented:
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?
0
 
Anthony PerkinsCommented:
>>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.
0
 
jdallen75Author Commented:
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?
0
 
jdallen75Author Commented:
Never mind... according to MS, dtexec can be used with both SQL 2005 and SQL 2008, without converting from SSIS to DTS.
0
 
lcohanDatabase AnalystCommented:
Not really a downgrade if you need to have it as a (DTSX) file on a separate box from SQL server box.
0
 
Anthony PerkinsCommented:
>>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.
 
0
 
jdallen75Author Commented:
This is what I ended up using - we'll see if it avoids the problem we were having with SQL Agent locking up. Thanks!
0
 
lcohanDatabase AnalystCommented:
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.
0
 
jdallen75Author Commented:
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)
0
 
Anthony PerkinsCommented:
You are welcome.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 6
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now