Is this a silly way to use SSIS?

I have typically done any ETL style manipulations I needed to do to data stored in SQL Server in VB.NET. I would use the IMPORT?EXPORT DTS wizard to import flat files, or mabe something from ACCESS every now and then.

I am looking at a situation in my current contract where I will be pulling flat files from a mainframe and quasi relational stuff from a DB2 instance via an ODBC connection. I will be using this stuff to build a datawarehouse for a manufacturing client.

My question is this. Is there really enough good stuff in SSIS for what I will be doing to justify my learning it or if I'm comfortable doing the manipulations in VB.NET will that work just as well for my client? After all, I can schedule VB.NET apps to write results to log files and to run at specified times, etc. It just sort of always seemed to me that DTS was for people that needed to manipulate data without necessarily having to know a lot about programming per se.

I'm looking for opinions from people that know SSIS well. Is there enough meat to make cooking the SSIS meal worth the trouble?

Thanks in advance for any info.

I'm a bit biased but in my opinion if you're moving data about then you should use SSIS. There's no point in building an application when you have a product available to do the work for you!

-Jamie

|||

Thanks for the feedback Jamie, I've read a lot of your posts so I will take what you say very seriously. I guess about the only thing I can think of to reply is that if my client decided tomorrow to scrap SQL Server for MySQL or Oracle or DB2 (all of which they have and run at various locales in the organization) then a properly written VB app could be modified slightly and keep on ticking. But if they scrap SQL server (and hey, with the way acquisitions, etc. work these days it's not beyond the realm of possibility) then they would have to start from scratch with their ETL process. But, I guess that would really argue more for a 3rd party ETL tool than for a special purpose written app.

Thanks,

Steve

|||

Note that your client's company could choose to use other database storage engines, but continue using SQL Server Integration Services, especially in light of the favorable pricing (for SQL). We knew of companies that purchased SQL licenses in the past just to get DTS...given the increased power of SSIS, this may become even more common (although we'd love to see everyone taking advantage of the entire SQL package, of course).

|||

Absolutely, SSIS has no reliance on SQL Server.

Its also worth saying that if you were to change your RDBMS its unlikely that your SSIS packages would be too affected - assuming the metadata of the tables is the same. In certain (lucky) scenarios the only thing you might have to change is a connection string. And if you're using best practice of storing connection strings external to your package then that is incredibly easy

-Jamie