Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

execute a built command at command line from SSIS without using xp_cmdshell? Must I build a .bat? Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 11:06 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 860, Visits: 730
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?
Post #1338685
Posted Wednesday, August 1, 2012 11:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1338686
Posted Wednesday, August 1, 2012 11:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 860, Visits: 730
Got an example GSquared?

Thanks!
Post #1338696
Posted Wednesday, August 1, 2012 10:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1338946
Posted Thursday, August 2, 2012 6:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1339105
Posted Thursday, August 2, 2012 6:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 860, Visits: 730
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!
Post #1339109
Posted Thursday, August 2, 2012 7:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1339160
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse