I’m sitting in an SSIS session at PASS and the following issues came to mind:
- SSIS and all of its manifestations needs to deal with common file formats like CSV without using JET, OLE DB or ODBC. There must be a way to create a data provider for delimited or other flat file formats without having to deal with the eccentricities of these ancient DAIs. So I’m told there is a CSV provider accessible in SSIS but why does it appear that it’s using OLE DB behind the scenes?
- SqlBulkCopy needs to have a mechanism to log records that are non-compliant or can’t be imported and have a threshold for failure.
- SSIS needs to be better understood. This means better documentation, more conference sessions and more examples of doing typical and atypical operations. Some of these tasks can be done by Microsoft while others can or should be done by third parties.
- Too many people are using the wrong tool to import or export data. Microsoft needs to be far more proactive to get developers informed of the right way to move data into and out of SQL Server. This means walkthroughs and examples of building server-side temp holding tables and subsequently validating these rows to be imported into live databases.
- Once the data arrives at the server, developers need additional functionality in the area of data validation post update.
- The SSIS team needs to realize that there are more than just “enterprise” customers. SQL Server Express is an important player for many companies—even “enterprise” companies. SQL Compact is also becoming more important in integrated data strategies. Do not discount the “free” versions as they are the seeds of learning as well as the k
I'll admit I have more experience with DTS than SSIS. But my biggest pet peeve with DTS was that it was very problematic to move a package between servers. As a freelance consultant, I work on a lot of things from home and then email them to my customers for deployment. With DTS, I always had to go to the customer's site for deployment because they couldn't simply update the connections. There was always "stuff" embedded in the package that didn't like when you switched servers. I could always figure it out eventually, but it took a while.
My understanding is that, for the most part, this has not improved in SQL Server 2005 SSIS. I haven't heard anything about SQL Server 2008 SSIS as it relates to this problem.
I also agree on the documentation/training front. I've had a hard time finding useful information on SSIS for people getting started, especially for people making the switch from DTS to SSIS.