SSIS - Export multiple SQL Server tables to multiple text files -

SSIS - Export multiple SQL Server tables to multiple text files -

i have move info between 2 sql server dbs. task export info text (.dat) files, move files , import destination. have migrate on 200 tables.

this tried

1) used execute sql task fetch tables. 2) used each loop loop through table names collection. 3) used script task within each loop build text file destination path. 4) called dft table name in variable source ole db , path name in variable destination flat file.

first table extracts fine sec table bombs synchronization error. see numerous posts not find 1 matches scenario. hence posting here.

even if bundle work multiple dfts, sec table sec dft not export columns because flat file connection manager still remembers first table columns. there way forget columns?

any thoughts on how can export multiple tables multiple text files using 1 dft using dynamic source , destination variable?

thanks , appreciate help.

unfortunately mass import task enable utilize format files map columns between source , destinations. mass import task uses mass insert tsql command import data, execute user should have bulkadmin server privilege.

most of companies not allow bulkadmin server privilege enable due security reasons. hence using script task build bcp statements , simple alternative export. not require build .bat file script can execute dos commands runs under .net security account.

ssis