SSIS Balanced Data Distributor with Script Component

Question

We've a small Data Flow Task which exports rows from a table to a flat file .

we added a script component for transformation operation (Converting Varbinary to String ) .

since the script component takes a while we decided to use the new Integration Services

Balanced Data Distributor and divided the export task into two more flat files .

while executing the task , it seems that the BBD isnt dividing the workcload and doesnt

work in parallel mode .

do you have any idea why ?

Can the data conversion not be accomplished using the existing Data Conversion Transformation? Also, could you post an image to what your data flow looks like? — billinkc
Hi , we're are pulling 100 rows (In real life it will be 1M rows). The data transformation is crucial (using .Net Code to convert Binary Data to string ) . I've prepared images but i dont have a clue how to upload them . — TheRunningDBA
I've tried to post the imapges but this the error i'm getting : •We're sorry, but as a spam prevention mechanism, new users aren't allowed to post images. Earn more than 10 reputation to post images. — TheRunningDBA
@tamirt if you drop the image urls in the comments, I'll try to edit your post to include them. — booyaa

Jon Jaussi Jon Jaussi · Accepted Answer · 2013-12-14T22:36:55

Have you tried using NTILE and creating multiple OLE DB sources in your Data Flow?

Example below for how to do that for 2 groups. You could of course split your source into as many as you need:

-- SQL Command text for OLE DB Source #1 named "MyGroup NTILE 1"
SELECT v.*
FROM
  (SELECT t.* ,
          NTILE(2) OVER(
                        ORDER BY t.my_key) AS MyGroup
   FROM my_schema.my_table t) v
WHERE v.MyGroup = 1;


-- SQL Command text for OLE DB Source #2 named "MyGroup NTILE 2"
SELECT v.*
FROM
  (SELECT t.* ,
          NTILE(2) OVER(
                        ORDER BY t.my_key) AS MyGroup
   FROM my_schema.my_table t) v
WHERE v.MyGroup = 2;

If you have a good idea in advance about the maximum number of NTILEs you need (say 10) then you could create 10 OLD DB Sources in advance.

SSIS Balanced Data Distributor with Script Component

1 Answers