Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
xpandre
Posting Yak Master
212 Posts |
 Posted - 2009-01-16 : 07:58:19
|
Hi Guys,I have a database which is on a 2000 server.Now we need to do something which can be done very easily using SSIS or DTS, but DTS is out right away. So we are left with just SSIS, but now SSIS package would run on a 2005 server on the same box, but doing follwoing stuff: - FTP from a remote location
- Load data into staging table (DB on 2000 server)
- Move data from the staging table, depending upon rules set in stored procedures (Procs in DB in 2000 server), into destination tables (DB on 2000 server)
I am not really sure of this approach where in, I assume SSIS would be talking to the 2000 server via linked server.( Please correct me if I am wrong).the 2nd approach would be to write procedures in 2000 and do all the stuff (the 3 bullet points above).Now, staging part does a funny thing. It loads each field in the FTPed flat file into multiple columns. Like that, we have around 20 fields. This can be done easily in SSIS with copy fields. In 2000, using bulk insert, I would have to load that field into 1 column, fire a trigger to load the same data into all other columns.Adn the staging table is never truncated after processing; a flag is set; So we can assume around couple of million rows in it at a time.Database migration from 2000 to 2005 : It won't happen in near future.Which approach (SSIS in 2005 or stored procedures in 2000) would be best suited for such a scenario?I have always liked the procedure part, but it seems I am fighting a lone battle with SSIS junkies. ThanksSam |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-01-16 : 09:59:14
|
| ETL. Extract, Transform, and Load.Use SSIS for the E and the L. That is what it is good at.Use Stored Procedures and Staging Tables for the "T". That is what they are good at.Avoid putting business logic or data rules in your SSIS packages.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
 |
|
|
xpandre
Posting Yak Master
212 Posts |
Posted - 2009-01-16 : 13:28:58
|
| Thanks Blindman.I was just wondering if doing all the Extraction, loading and transformation on a 2000 server from a SSIS package on a 2005 server, would this hamper the performance a lot?Hence should I opt for procedures itself in 2000, the entire ETL process?ThanksSam |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-01-16 : 15:32:31
|
| I don't see why SSIS would be any less efficient. At least for transferring data.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
|
|
|
|
SSIS Vs Stored Procedures