Hi there, Below is brief description of what we have right now: 1. There are 4 caregories of the clients a. Category 1: i. Client 1 ii. Client 2 iii. Client 3 b. Category 2: i. Client 4 ii. Client 5 iii. Client 6 c. Category 3: i. Client 7 ii. Client 8 iii. Client 9 d. Category 4: i. Client 10 ii. Client 11 iii. Client 12 2. File (Excel/csv/flat) are sent to us via FTS/ or shared locations. Every single client within each category can have their own file format 3. Files come on different schedules from various clients regardless of category 4. As of now, we have created a separate SSIS package for each client and they are all saved on Shared locations and run manually as needed. 5. Many of those packages use some common packages.(which are also saved on shared drives and referenced view SSIS package Task) 6. There is no config files are in use right now so when we migrate dev to prod, admin have to go in and change the connections to point to prod. Now this explains a lot how bad the situation is right now. By no means we are using SSIS package to industry standards. I am planning to re-architect the whole system and do something like explained below: 1. Create Single package for each Category 2. Transform all the Inline sql into SQL Stored procedure for all the good reasons. 3. With in that package I use branching which will determine which client we are running for this package via settings in config file. 4. Packages are saved in SQL Server and then automated from there (Question here is can I change the value of config file variable dynamically when the job kicks off and find out what client Its running for at different schedules) Please suggest/Add other points which you think I should consider while planning this re-architecture. I appreciate your time and help!!!

Well, since you have multiple types of files, here is what i would do. 1. Create a table in sql server storing 'FileType', 'FolderPath', 'LastUpdate'. 2. Create a single SSIS package. Create 3 ForEach Loop Container in it. 3. For 1st container, use ADO Enumerator, loop over 'FolderPath' for FileType=Excel 4. Inside this container, add another ForEach Loop Container, use File Enumerator, loop over all files in the folder. For each file, call the import package for excel files. 5. Similary, use the other two containers, one for CSV file and other for Flat file. 6. In primary package, use XML Configuration to store Sql Server connection information in a xml file. In child packages, don't store anything. Pass information (like conn string) from parent package. etc, etc..Press CTRL-ALT-DEL now for an IQ test.

Hi LiquidLoop, In the above scenario we have 3 files (excel, flat file ,csv). we will get data sometimes like different columns ( like meta data is different.). what will we do at the movement.