Does anyone have any sample ETL advice? We need to create a SQL Server
2005 lab in order to compare and contrast ETL on SQL 2005 vs. SQL 2000.

Hello imani_technology_spam (AT) yahoo (DOT) com,


What do you want to compare and contrast?

Speed?

The problem is that a lot of the things you get in the box now simply do
not exist in 2000.

Allan

I would like to compare speed. I would also like to compare
approaches. For example, is ther a way to simply transfer old DTS jobs
into SSIS? If so, would that be the most prudent approach?

Here's an example: with SQL Server 2000, it was often more efficient
to use stored procedures instead of DTS if both the source and
destination databases were SQL 2000. Would that be the case with SQL
Server 2005?

Hello imani_technology_spam (AT) yahoo (DOT) com,


Speed is hugely different in 2005 compared to 2000.

Your approach will be very different as well. In 2000 you could think of
DTS as ELT and in 2005 it is more ETL. There is no need to land the data
as much as previously.

DTS jobs or packages?

Packages there is a migration wizard, personally I will probably leave my
2K packages AS IS and then redo them from scratch gradually. i can use an
Execute 2000 package task to fire the old packages.

I would still use SPs in the right circumstances. You can just call them
in an ExecuteSQL task.


Allan

That's very interesting. Again, we found that it was much more
efficient and flexible to use T-SQL within stored procedures to
complete our ETL tasks when the source and destination are both SQL
Server 2000. Can I assume that approach might not be necessary with
SQL Server 2005 or that SSIS can get the job done more efficiently that
stored procedures?

Hello imani_technology_spam (AT) yahoo (DOT) com,

I am saying that TSQL will pound for pound be the best option but it may
not be the best thing if you want complex scrubbing. Keep it simple. SSIS
is a hammer but not everything is a nail.

TSQL is very very performant when used properly and well. SSIS is likewise.

Allan

Thanks for the info. Do you know of any sample data that I can use to
play with SSIS?

Hello imani_technology_spam (AT) yahoo (DOT) com,


We have a data generator you can grab which generates meaningless data for
the pipeline

http://www.sqlis.com/default.aspx?82

Allan

Thank you for the data generator. Also, I wonder if it would be
possible to use Northwind or pubs, since neither database seems to be
included with SQL 2005?

Hello imani_technology_spam (AT) yahoo (DOT) com,


Just restore a 2K version to 2K5.


Allan