Should SSIS be used for the following?

[jonwolds]

Hi,

Can SSIS be used to implement the following:

1. Each company will supply a monthly text file.

2. A companys file format will be the same each month.

3. No two companies will use the same file format and column orders/number of columns as each other.

4. I want to poll a windows folder for the text files and import them into a staging table, ready for validation.

I can do this using a stored procedure by creating a format file for each client and storing the name of it in a table. The client name is included in the file name and this can be used to look up the appropriate format file from a table which is added dynamicaly to the Bulk Insert command.

Is it practical to create this solution in SSIS or am I better of sticking with stored procs?

Thanks

Jon

 

[mrdenny]

I would stick with the stored procs.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jonwolds]

Thanks for your response. Please could you elaborate?

Jon

 

[mrdenny]

Writting something this complex in SSIS will be tough to say the least. If the solution you have now is working and it's working correctly why change it.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com