Trading in xp_cmdshell for SQLCLR (Part 2) - Export Data to Flat Files

• volox4games

  Valued Member

  Points: 58

  More actions

  May 16, 2011 at 8:32 pm

  #1325558

  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

• pesche_h

  SSC-Addicted

  Points: 407

  More actions

  June 18, 2011 at 12:46 pm

  #1340812

  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 ...

• pesche_h

  SSC-Addicted

  Points: 407

  More actions

  June 18, 2011 at 1:01 pm

  #1340813

  Yet another late reply:

  We mostly use xp_cmdshell + BCP in combination with format files. I'd be very interested in seeing a CLR assembly that would be able to read a format file and export the data accordingly.

  (of course, if you can actually execute BCP from CLR, this problem is automatically solved).

  Thank you for considering this!

  Kind regards

  - Pesche

• marek.konecki

  Newbie

  Points: 9

  More actions

  July 11, 2012 at 1:49 pm

  #1511775

  Dear Jonathan,

  Can I run this SP (a bit changed to create XML file and return 0 if file is exorted w/o errors or 1 if not) in my transaction to check if the file was successfully exported and update Export flag if yes? The code would look like this:

  BEGIN TRAN

  some sql selects & inserts preparing the data to export and placing in @XML variable

  DECLARE @ID INT

  INSERT TrackingTable (XML, Export) VALUES (@XML,0)

  SELECT @ID = SCOPE_IDENTITY()

  DECLARE @CMD VARCHAR(50), @Result BIT

  SET @CMD = 'SELECT XML FROM TrackingTable WHERE ID = ' + CAST(@ID AS VARCHAR)

  EXECUTE @Result = [dbo].[WriteResultsToCsvFile] @CMD , 'SomeFileName.xml'

  IF(@Result = 1)

  BEGIN

  ROLLBACK TRAN

  RETURN 1 -- error code

  END

  -- file is exported so update the status

  UPDTE TrackingTable SET EXPORT = 1 WHERE ID = ' + CAST(@ID AS VARCHAR)

  COMMIT TRAN

  Are there any disadvantages of this approach?

  Thanks in advance,

  Marek