execute a built command at command line from SSIS without using xp_cmdshell? Must I build a .bat?

  • I'm hoping I haven't developed my way into a corner. I'm tasked with deleting a file from a remote server using a command line plink.exe command...

    > plink -i g:\keypath\iamsql.id_rsa.ppk myuser@211.999.999.999 "rm reports/tsfile_20120731.csv"

    The command will always "rm", but the file name in question will change on every run. I've already built and tested the string that I want executed, but I can't figure out how to actually execute it from within SSIS.

    I'm trying to avoid using xp_cmdshell.

    I started down the path of building a .bat file, but I'm having trouble getting the commands in there without a column header that would probably confuse things.

    Suggestions?

  • Script object is your best bet.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Got an example GSquared?

    Thanks!

  • An Execute Process Task could work as well. I have done similar things by building my command line arguments dynamically in a variable using an Expression and then mapping the variable to the Arguments property of the Execute Process Task. The Task can be frustrating before getting the properties set correctly, but once you get it the technique is reliable. Make sure to set Working Dir to plink's home dir or fully qualify the path to plink.exe in the Executable property.

    If you go with a Script Task Google for examples on how to use the .NET class System.Diagnostics.Process.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Greg J (8/1/2012)


    Got an example GSquared?

    Thanks!

    It's been 10 years since I last used VB with any intensity. I just look things up online and then modify/fiddle till it does what I need. Used to be good at it, but I'm beyond rusty these days.

    Sorry I can't help more on that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I've gotten this command to work inside a script object:

    Public Sub Main()

    Shell("c:\documents and settings\sqladmin\desktop\plink -i g:\sshkeys\sqluser.id_rsa.ppk myuser@211.999.999.999 ""rm myfile_20120727.csv""")

    Dts.TaskResult = Dts.Results.Success

    End Sub

    Not sure if it's a hack. Not sure if it'll still work when scheduled inside a job, but it's doing what I want for now. Thanks for the help gents!

  • I hadn't known of it before but Shell looks promising. Please let us know how it goes. I see there are some challenges in deployments when running unattended.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Viewing 7 posts - 1 through 7 (of 7 total)

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