Post reply

DTS to Non-Standard Flat File

  • March 30, 2004 at 12:58 pm

    #179159

    I'm pulling my hair out on this one (not that there's much left!!)

    I have a stored procedure that extracts data from a database based on parameters to be exported to a flat file. The customer refuses to use the standard delimiters supported by the Output Text File connection object (i.e. comma, semicolon, pipe), but rather wants fixed-length fields with a space between each field.

    So I figure I'll use an Execute SQL task, dump the rowset (never more than 5-10 rows with 3 columns) into a rowset global variable, pick that up in an ActiveX task (saw this code on MSDN), format it in a string and write it to a file using FSO.

    So I set it up and when it gets to the ActiveX task it tells me the recordset object doesn't exist.

    I scratch my head, pull out a few more hairs, and decide to just execute the SP as an ADO command in the ActiveX task, loop through the recordset writing to FSO and ta-da!

    I set up the ActiveX task and when I get to the "While (Not RS.eof)" statement it tells me the recordset is closed! I can copy the SQL straight from the ActiveX task back to Query Analyzer and it runs fine! I stuck in a msgbox to display the state of the connection and it was open. I then moved the msgbox to display the state of the RS after the Open and it's closed!

    I'm stumped. So there are two questions:

    1. Why is this code acting this way? (See posted code below)
    2. Does anyone have a better solution?

    Thanks.

    --------------------------------------------------------

    '**********************************************************************

    '  Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

     dim countr

     dim conn

     dim RS

     set conn = CreateObject("ADODB.Connection")

     cnString = "Provider=SQLOLEDB.1;Data source=CORPSQL001;Initial Catalog=ETSBatch;Integrated Security=SSPI;"

     conn.open cnString

     set RS = CreateObject("ADODB.Recordset")

     sqlString = "EXEC dbo.Mainframe_Extract_LateStart 'HCO' " <= works in QA!!

     RS.Open sqlString, conn

     msgbox "RS State: "&cstr(rs.state)&":"  <== Indicates RS is closed

     While (NOT RS.eof)

         MsgBox "The JobName is " & RS.Fields("JobName").value

         RS.MoveNext

     Wend

     RS.Close

    Main = DTSTaskExecResult_Success

    End Function

     

  • March 31, 2004 at 5:55 am

    #501255

    I changed the sqlString variable to a simple "Select JobName from Requests" (lists all jobs) and the code worked.

    Is there a problem with executing a Stored Procedure that returns a table in an ActiveX module???

     

  • March 31, 2004 at 7:39 am

    #501276

    I think you need to use a command objec tin order to execute a stored procedure in ADODB. Try adding this:

    Dim cmd

    Set cmd = CreateObject("adodb.command")

    cmd.ActiveConnection = cn

        cmd.CommandType = adCmdText

        cmd.CommandText = [sp]

        

        Set rs = cmd.Execute()

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • April 2, 2004 at 2:32 pm

    #501675

    Quite often we have to create fixed length files to communicate with an IBM mainframe. We do this by creating fixed length columns outputted out of sql code. For example:

    Select substring(name,1,60),substring(address_line1,1,120),substring(city,1,30),substring(state,1,2)

    from address_table

Viewing 4 posts - 1 through 3 (of 3 total)

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