Clever Geek Handbook

Balanced SSIS Data Distributor with Script Component

We have a small data flow task that exports rows from a table to a flat file.

we have added a script component for the conversion operation (Converting Varbinary to String).

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

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

while the task is running, it seems like BBD is not sharing the workload and doesnt

work in parallel.

Do you have any idea why?

+3


source to share


1 answer


Have you tried using NTILE and creating multiple OLE DB sources on a data stream?

An example below is how to do this for 2 groups. You could, of course, split your source as many as you like:



-- 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), you can create 10 OLD DB Sources ahead of time.

0


source






More articles:


All Articles