3
votes

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 ?

1
How many rows are pulled from the source table? - Bill Anton
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

1 Answers

0
votes

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.