Post reply

Launching a package from Stored Proc?

  • February 6, 2009 at 8:49 am

    #128462

    Hi,

    I'm new to SSIS, and I was told it was easy to export data in text files.

    I built a small test package to select 10k rows, and drop them in a text file, it works nicely.

    Now, what I'm looking forward to do, is to execute that test package from a procedure, to be able to export certain data on user commands. So in the client application, when they click on export, I want a procedure to launch the package, and send the parameters to it.

    Is that possible, or am I completely out of the way here?

    If it's not possible, I'd like to know how you would manage to do that kind of exportation.

    Thanks in advance,

    Cheers,

    J-F

  • February 6, 2009 at 10:17 am

    #939662

    "dtexec.exe" can be used to execute an SSIS package from command prompt. If you want to execute an SSIS package from a stored procedure, one option is to create a command string using "dtexec" and execute it using xp_cmdshell.

    .

  • February 6, 2009 at 10:47 am

    #939677

    another option would be to create a job to execute the package, but not schedule that job. Then you could build a command string (@sql) within the proc to execute msdb.dbo.sp_start_job and issue that command with EXEC (@sql).

    "Got no time for the jibba jabba!"
    -B.A. Baracus

  • February 6, 2009 at 11:45 am

    #939708

    Nice! Thanks for those quick answers!

    Another question I have is how do you extract a package from the Business intelligence studio, or, is it like in VB, just build the solution with the release option, and get the outgoing package?

    And, how do you deploy this package on another server, copy locally on the server, and then attach as a package? Or is there another way I do not know.

    Thanks in advance guys,

    Cheers,

    J-F

  • February 9, 2009 at 9:54 am

    #940611

    On way is to use the MS recommended method (see "deploying packages in BOL) and build an Integration Services project in BIDS to create a package depoyment utility that specifies where to deploy the package.

    An alternative is to save the package from BIDS to the server, either to SQL Server or to the file system, where it will run.

    Greg

  • February 9, 2009 at 10:02 am

    #940618

    Greg Charles (2/9/2009)

    On way is to use the MS recommended method (see "deploying packages in BOL) and build an Integration Services project in BIDS to create a package depoyment utility that specifies where to deploy the package.

    An alternative is to save the package from BIDS to the server, either to SQL Server or to the file system, where it will run.

    Thanks Greg, I will look into deploying packages with an Integration Services Project. For now, I'm completely stuck on my other post : http://www.sqlservercentral.com/Forums/Topic652930-148-1.aspx

    Am I doing the right thing there? Man, that sounds easy, simply export the SP resultset to a file...

    Thanks in advance,

    Cheers,

    J-F

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply