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 01, 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: Thursday, March 27, 2014 10:44 AM
Points: 860, Visits: 727
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 01, 2012 11:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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 01, 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: Thursday, March 27, 2014 10:44 AM
Points: 860, Visits: 727
Got an example GSquared?

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1338946
Posted Thursday, August 02, 2012 6:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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 02, 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: Thursday, March 27, 2014 10:44 AM
Points: 860, Visits: 727
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 02, 2012 7:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1339160
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse