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

  • volox4games

    Valued Member

    Points: 58

    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

    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

    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

    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

Viewing 4 posts - 46 through 49 (of 49 total)

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