3

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?

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
Moo
  • 2,225
  • 19
  • 23

4 Answers4

2

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.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
0

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.

Fabian
  • 83
  • 1
  • 1
  • 9
  • you should double check that the creator of the csv file doesn't just create the file and then dump data to it for the next couple of hours... if so this method would find the csv file existing but locked or having partial data. – Knox Aug 14 '09 at 10:58
  • In our case, the cvs files always exist and we rsync the nightly diffs using them as the destination. – Moo Aug 14 '09 at 12:32
0

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.

ConcernedOfTunbridgeWells
  • 8,810
  • 2
  • 31
  • 52
  • 1
    I do something similar to the first option just because it's so simple and easy. Once the CSV file is copied over to the remote server, I rename it from original.csv to original_copyfinished.csv and I have a Scheduled Task to look for _copyfinished files and act on those. – David Aug 15 '09 at 23:04
0

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.

K. Brian Kelley
  • 9,004
  • 31
  • 33