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