SSIS vs Bulk Load

Based on 2000 experience with DTS, a lot of people prefer set operations or bulk load over transformations. One big benefit of SSIS transformations is that you can execute several operations in one pass--which you can't do with set operations. And if you use the SQL Server Destination adapter (and native SQL connection manager), you get speeds much faster than OLEDB--in fact you can get virtually identical speed to bulk load. One thing to watch out for is that when using the SQL Server Destination, the package must run on same machine as destination database.

Reed

When is Ragged Right right for Fixed-length files?

It's natural to expect that if you have a Fixed Length file, that you should choose the SSIS Fixed Length file type. But Fixed Length is really for binary files, where there are no CRLF characters at the end of the lines. If you have a fixed length text file, and want to eliminate the delimiter, use Ragged Right flat file, and for the final column, specify CRLF as the delimiter.

- Reed

Diagrams are Back!

One of the most (ahem) active threads in the SQL 2005 beta newsgroups was discussion around the loss of the trusty database diagramming tool, formerly found in SQL Enterprise Manager. I am happy to report that as of the April Community Technical Preview build, diagrams are back in the SQL Management Studio. Even though Microsoft has stated that the primary emphasis for diagramming will be with both Whidbey and with Visio Enterprise Architect, it’s nice to have this often used functionality returned to the tool-set that many of us use day to day to create or understand Star or Snowflake Schemas. While there are many tools that offer more extensive diagramming feature sets, the integration and ubiquity of the Database Diagramming Tool makes it invaluable. Personally, I use it to visually understand the layout of Star and Snowflake schemas that I am becoming familiar with. I also use it to create prototype relational databases for proofs of concepts.

Feature-wise, it seems to be much the same as the previous version. I’ve noticed two differences worth mentioning (so far):

1)  The procedure for adding related tables is slightly different. In the SQL 2000 version this was accomplished through the add-table dialogue; now it is accomplished by selecting a table in the diagram and clicking the “Add Related Tables toolbar icon”

2)  The ability to open tables is no longer part of the diagramming tools. That functionality is available as part of SMS, just not directly from within the Diagramming Tool as in the SQL 2000 version.

Kudos to the SQL Server Development Team for incorporating our feedback around the Diagramming Tool.

- Tom Huguelet