Home / server / Questions / 54131
Accepted
Moo
Asked: 2009-08-15 01:02:32 +0800 CST

Unix and Triggering MS SQL Server Integration Services Jobs

  • 772

We have an AIX server which contains a lot of data, and once a night we dump that data to CSV for import into MS SQL Server for reporting reasons.

At the moment, each step in the process is unlinked, and triggered simply on a timed basis - for example, the CSV dump processes on the AIX system start at 3am and take $x amount of time to complete, and then the MS SQL Server Integration Services job starts at 6am.

As such, there is a lot of 'dead time' built into the process, which it would be nice to eliminate.

So, is there any way to trigger an SQL Server Integration Services job from a Unix system?

sql-server

4 Answers

  1. Best Answer

    you can use freeTDS (or jTDS if you use that thing called java) to give you access to your sql server from Unix/AIX.

    From FreeTDS you can authenticate against SQL Server & run any T-SQL you like. You can "trigger" the SSIS job by calling the sproc sp_start_job which resides in MSDB to kick off the job by name or job_id.

    • 2

  2. An easy way is polling. If you add an elemnt "if csv files exists, import csv files" to the beginning of your ssis packet, you can schedule the packet to run every quarter hour.

    • 0

  3. SSIS does not directly support this type of functionality in the base system. Two options are:

    • Build a poller on the Windows side that looks for some sign that the unix job has finished and then runs the SSIS package. If the file takes some time to generate you should wrap the job in something that updates the timestamp on another file when it's finished. Another option is to have the poller scan a directory and look for new control files that are created on each run. If the poller runs (say) every 5 minutes between midnight and 4AM it will pick up the job with minimal latency. If it doesn't pick up the file in a reasonable window you can assume something went wrong while it was being generated and fail the task.

    • Buy a cross-platform scheduling tool like Control-M. This type of system is designed to coordinate these jobs across multiple platforms. However, enterprise schedulers are quite expensive and probably overkill for a single job. You might consider this type of system if you have a lot of this kind of issue going on but for a single task it's really cracking a walnut with a sledgehammer.

    • 0

  4. SSIS packages are run using dtexec. Therefore, if you have any way of already triggering a process on the Windows side from AIX, you can use the same technique to start an SSIS package on the SQL Server.

    • 0