Design Diary: Dependencies in List Segment Detail.sql ‘Hard’ to Remove

This SQL command joins the [Log] table with external tables (in the GenericWeb database). This t-SQL join operation cannot be efficiently decoupled in SSIS with something like the Merge Join Data Flow Transformation—this would require selecting all of the [Log] data (millions of rows) and then sorting it with GenericWeb Segment data.

The Segment data can be selected first and its grouped Document data can be used as a constraint against the [Log] data. This would require looping through the Document groups and querying the [Log] data multiple times. This approach seems to require dynamic SQL. This is explained in “SSIS: Using dynamic SQL in an OLE DB Source component.” But once this programmatic editing procedure is allowed then why not just edit the original SQL join such that references to [W``ebKinteSpac``e] are replaced with another GenericWeb database?

Another Way?

This cross-database, join operation fills the target [Log] report table in one pass. It should be possible to fill this table in two passes in SSIS. The first pass makes the ‘external’ connection to the GenericWeb database and inserts rows. The second pass updates with ‘internal’ Log stats. Once this design is in place, evidently what is next is to look into “SQL Server Integration Services SSIS Package Configuration”—or “Using XML Package Configurations with SQL Server Integration Services SSIS.” When we right-click on the SSIS Package design surface, we see the Package Configurations… command. This configuration stuff should help me remove any hard references to GenericWeb databases.

Also…

I am using ADO.NET connections too liberally. When parameters are not an issue, I should be using “faster” OLE DB connections—according to Dave Fackler.

rasx()