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 «««23456»»

Trading in xp_cmdshell for SQLCLR (Part 2) - Export Data to Flat Files Expand / Collapse
Author
Message
Posted Friday, May 6, 2011 1:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:56 PM
Points: 58, Visits: 260
I just finished a project to eliminate all the xp_cmdshell used in our code in one of our more sensitive databases. Sensitive information was hard coded all over the place and the code was just as bad as it can get and then some (result of contractors that were hire to get 6 months of work in just 3 weeks, the funny part of this is that all of their sloppy code was replaced with just one afternoon's worth of C# coding!). We no longer allow the use of xp_cmdshell in our enterprise.

Anyway, I can contribute my code for a future article if anyone would be interested in it.

We ended up with the following CLR procs:

1. clrFileExists - checks if a file exists
2. clrFileMove - moves a file
3. clrFolderCreate - creates a folder
4. clrFolderListFiles - list files in a folder, because the development team needed a way to not check for 100+ files one by one
5. clrFtpDeleteFile - deletes a file from an FTP server
6. clrFtpGet - gets a file from an FTP server. Originally this would import the contents of the file directly from the FTP server and into a SQL table without an intermediate file, but the developers started getting all sorts of crazy ideas and I had to make them settle down and ended up coding it to download the file from the FTP server and saving it locally.
7. clrFtpListFiles - lists files in an FTP server
8. clrFtpPutFromQuery - sends the results of a query to a file in an FTP server

We also coded something extremely similar to the delimited file export ones discussed in the article.
Post #1104865
Posted Friday, May 6, 2011 1:53 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
mendesm (5/6/2011)
Anyway, I can contribute my code for a future article if anyone would be interested in it.


I would be interested in your article and the CLR code when you get a chance. Thanks!
Post #1104869
Posted Monday, May 9, 2011 10:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 4, 2014 9:29 AM
Points: 22, Visits: 53
Jonathan:

Frequently we encounter implementations by third party vendors that utilize xp_cmdshell for the importing of data as well as the exporting of data. Unfortunately, their developers take the "easy" way out and want sysadmin rights for their application credentials. We need to educate developers in good coding practices when accessing SQL Server. Time and time again we run into problems and vulnerabilities with these applications.

Regards,

Andrew Smith
Post #1105541
Posted Monday, May 9, 2011 11:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 8:33 AM
Points: 1, Visits: 15
While this is all kinds of fun, you could simply use SSIS and be done with it in about 2 minutes.
Post #1105635
Posted Wednesday, May 11, 2011 7:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 7:39 PM
Points: 2, Visits: 6
Thanks for the great example.
One thing I am also very interested in is being able to produce a vs.net report in a pdf file and sending an email from sql server. Can this be done using SQLCLR (or SQLCLR with sql server reporting service)?
Post #1107431
Posted Thursday, May 12, 2011 7:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 27, 2013 8:58 AM
Points: 4, Visits: 26
UMG Developer (3/16/2009)
Jonathan Kehayias (3/13/2009)
The correct way to do this is to create a SQL Agent Job that runs the SSIS/DTS package, and then start that job using sp_start_job. SQLCLR is not needed for this kind of task. You can use the builtin MSDB roles to control security around this, and or impersonation to escalate priviledges as necessary.


I know about that option, but I don't like having to create a SQL Agent Job for every SSIS package just to be able to run it on the server, and the other problem is that we usually need to wait in the procedure until the SSIS package completes. I suppose we could setup a loop calling sp_help_job to find out when it finished and if it errored. Is there something I have missed, an option or something, with sp_start_job that will have it not continue the procedure until the job finishes?

Using xp_cmdshell works well for this without any extra code, but it does open up a big hole.


I agree. We have processes that need to call out to launch SSIS packages and wait for them to complete. Calling to xp_cmdshell is the easy way to syncronously launch and wait for a SSIS package. If there were a SQLCLR way to do it that avoided xp_cmdshell, then that would be great.

... And I'm not too interested in the solution that was mentioned that involves loading up every DLL under the sun so if someone has an alternative that is simpler... maybe...?
Post #1108154
Posted Friday, May 13, 2011 7:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 4, 2014 9:29 AM
Points: 22, Visits: 53
Why not create a table that has the name of the SSIS package you want to run. Retrieve the package name and start it with the SQL Agent.
Post #1108400
Posted Monday, May 16, 2011 8:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 27, 2013 8:58 AM
Points: 4, Visits: 26
Because having an exectuble / code call a job that looks up a package in a table and calls a package...
a) Is like asking the guy on your left to scratch the back of the guy on on your right
b) Doesn't provide the feedback to the application regarding errors / failures of the SSIS execution
c) Requires SQL agent to be running
d) Requires the executable to poll for job completion rather than being a syncronous operation
Post #1109914
Posted Tuesday, May 17, 2011 9:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 4, 2014 9:29 AM
Points: 22, Visits: 53
a. It's more secure.
b. If an application needs feedback, the results and status may be logged to a table.
c. In every instance I have administered, the SQL Agent is always running.
d. Yes, but that should not be a problem, especially if the results and status are logged to a table.
Post #1110332
Posted Saturday, June 18, 2011 12:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 2:13 AM
Points: 9, Visits: 132
noeld (3/4/2009)
-- Cranfield (3/3/2009)
How does the performance compare to native BCP OUT? should it be the same.

cheers

BCP should OUTPERFORM this by far.

The problem is that it is NOT as flexible and controlled as when you know what the code can or cannot do.


Sorry, a bit late, I know. I am just wondering: is it possible to start BCP using CLR instead of xp_cmdshell? That would combine the best of both worlds ...
Post #1127786
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse