Overview: I am running SSIS package to copy data from SQL Server 2016 to Flat file pipe delimited with quoted identifiers and handling escape sequences. I am using Script component in Data flow task as source to write data to flat file as I have to copy data from more than 100 tables with different schema so this is the only dynamic approach worked for me. Also the data is huge in amount most of the tables have 100+ columns and 5 million+ records. I have setup my master package is calling the same package 12 times in parallel for different tables (managed through SQL tables and parameters). On top of it my child package is creating file in batches implemented using For Loop containers and parameters.
Problem Statement: When I am running my package from SSDT it starts writing the data to file in immediately once it process the records but when I am running the same package via SQL Server Agent Job it is taking lot longer time and writing the data to file once all records are processed.
Example: Lets table 'a' is having 4 million records and I am generating 4 files each of 1 million, on same parameter SSDT start writing rows in file ~50K-60k (may be depend on buffer size) as soon as they processed but the same package with same configuration when I run from SQL server agent job it process all 1 million records at and try to write all at a time.
Issue: Because it is writing 1 million records at a time, file creation is taking lot of time just to write 1 million records ~5-10 mins varies based on number of columns in table but from SSDT it is much quicker ~2-5 mins for same table.
Can anybody suggest me what settings I have to check to make it work more faster. The table from which it is selecting data is well indexed. Same query for 1 million records when run against database in SSMS took ~2-4 mins.