Then, if we want to place 1000 rows on each batch/file, the source query will look like:With CTE_DimCustomer_MaximumRowsPerPartition (GroupRowNumber ,[CustomerKey] ,[GeographyKey] -- )As(Select ((ROW_NUMBER() over(order by CustomerKey)-1)/1000)+1, [CustomerKey] ,[GeographyKey] --from dbo.DimCustomer)Select * from CTE_DimCustomer_MaximumRowsPerPartitionAnd as in the previous post, we use the power of SSIS expressions to parameterize this query. The expression references a second variable called ‘MaximumRowsPerFile’. Is this variable the one you would change at run time to accommodate a different number of rows per file.

Now, we still have an issue to solve. We have to tell the package how many files are going to be generated, and since the number of files would depend on the number for rows per file we choose, we need to calculate it upfront. The way I did I was using an execute sql task to:

-- Query via expression-- Find out how many group of rows (files) are -- required based on the maximum numbers of -- rows each group (file) should have