Post reply

DataFlow Script component input source vs transformation and buffering. Why did the transform script work better than the input script?

  • December 28, 2018 at 6:26 am

    #409401

    Recently, thanks to a comma delimited file that had random numbers of delimiters/columns, I decided to try my hand at writing a Script component as a data source in my Data flow.

    the script worked fine, but the package could not be used. Seems that during the attempt to process some 80000 lines of input, the package started generating mass amounts of 2K files in the temp folder of the server, until it clogged and choked the server.

    Upon doing some limited searching, I found a note indicating that SSIS would create these temporary files to buffer the input and would not flow to the next component/task in the data flow until all the input had been read. So, I re-engineered the process. I redefined the file as a flat file ragged right, of some 800 byte records, and then passed that into a transform script component. Problem solved.

    I am still not entirely sure why that seemed to fix the problem. I am guessing,  that once loaded as a big flat record, the dataflow then buffered the records internally as they were passed to my script component. This either did not require a temporary file, or required fewer and smaller temporary files that allowed the package to run cleanly without choking the server.

    I'm curious to know if my theory is correct, or if there is another reason behind why the transform worked better than the input script. 

    Thanks.
    Luther

  • December 28, 2018 at 3:28 pm

    #2017563

    If you are reading one row at a time in the CreateNewOutputRows method, and passing the data from each one to the appropriate ScriptBuffer.AddRow method, then it should not spool the entire file.  Unless it thinks it is supposed to operate in asynchronous mode.

    Is the SynchronousInputId value set ( <> 0 ) for the source component output?  Is the downstream component set to the same SynchronousInputId value?  An asynchronous script component will buffer all input before generating output, such as sort, merge, or aggregate transformations.

Viewing 2 posts - 1 through 1 (of 1 total)

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