July 2, 2008 at 5:39 pm
We have a process consisting of
a) user creates two .csv files
b) SSIS solution performs some conversions but essentially is a Data Flow task that loads the csv contents into SQL Server temp tables
c) SQL Server sproc (with parameters) processes the table contents into the final end product
I can create an Agent task out of the SSIS bit, but there is no set schedule, so it has to be run "upon demand" - with the requisite files already created and waiting.
What would be a good method of encapsulating this into a front-end app that would
allow user to browse and select the two csv files, then click on a command button(?) which would pass the file paths to the SSIS project and trigger it to run...??
I guess my key hangup is that I can run an SSIS task from within SSIS; I can get one scheduled to run via Agent, but how do you fire off an SSIS task external to either SSIS or SQL Server?
Appreciate any hints!
-GGG
July 3, 2008 at 5:44 am
It's fairly simple, actually.
You have two methods. The easiest way is to run the DTExec command line utility. This gives you the same abilities you have from the job agent - you can set values, pass in configurations, etc. Look it up in BOL.
You can also use the SSIS object model. This is a bit more complicated, but a .Net developer would find it pretty easy to deal with. One of the big advantages is you can manipulate the package before running it. You can also get all of the feedback you would be able to get by running it in BIDS if you really wanted a flashy application. There is information in books online about this and you will find sample code if you search on Google.
Here is the catch. SSIS packages must be run by SSIS Servers. So, if you have a windows forms application being distributed to individual workstations, the application will be running on the workstations and therefore running the package on the workstations. Meaning you would have to license and install SSIS on the workstations. So, if you have a distributed application and you want to run an SSIS package from your SSIS server, you will need to wrap it in a service or web service that can be called remotely by your application. Web services work pretty well for this.
July 3, 2008 at 9:28 am
Great! Thanks for the direction~
-G
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply