SSIS on 64-bit platforms

[dmcmunn]

For those of you moving to the wonderful world of 64-bit SQL Server 2005 and SSIS. I want to share a few of the items I have learned along the way.

1. First, make sure your 64-bit SQL 2005 is updates with SP1 and all the hotfixes for SSIS _BEFORE_ deployment of your packages and definitely before testing. There are some rather interesting SSIS bugs that have been corrected in these hotfixes.

2. SSIS really hauls when taking advantage of the flat address space available in the 64-bit environment. Disk access speeds are still the limiting factor of performance, but SSIS' memory and multi-processor utilization is impressive.

3. As of today (20-Dec-2006) SSIS packages executing in Visual Studio 2005 on the 64-bit platform will not experience the complete peformance boost of the flat address space (and baby, SSIS loves memory!) because VS2005 is only available as a Win32 app. As a result it VS2005 runs in WOW (Windows 32 on Windows 64) session and so does your SSIS script. Check it out in Task Manager when you launch it.

4. If you develop VBScript components in a Win32 environment, but must deploy them to a Win64 environment, to take advantage of the 64-bit runtime, you must perform two steps: 1. Set each script to Compile to Binary; 2. Open and save EACH VBScript component in EACH package in order to have them recompiled. This can be a large pain in your day, so be prepared to spend the necessary time. In a recent migration I had 150 SSIS packages with a single VBScript component in each for configurations and variable management...so it took a coupla hours to peform this highly manual task...Come on MS help a brother out! How about a little automation help here!

5. You may have difficulty executing an SSIS package via the DTEXEC OS command from a SQL 2005 Agent scheduled job. I did. You should use the built-in SSIS Package to execute the SSIS package as a job step instead of the DTEXEC command line utility. Remember to set any configuration strings or variables you will not be configuring with other means.

6. If your SSIS packages must be run as part of a SQL 2005 Agent job and use NT Authentication to connect to SQL 2000 or other sources make sure the credentials for your SQL 2005 Agent Account have the necessary file system or database access on the remote systems. (This applies for Win32 or Win64, but is a good check list item for deployment.)

From here on out there are some general comments about SSIS...

7. DO NOT USE the Slowly Changing Dimension component on any large (100K+ row) data warehouse dimension tables. The genius of this SSIS component is it slaps a "select * from dimension_table"-driven _CURSOR_ and processes each row! Slowy Changing is RIGHT! Geez... You will be much better off writing your own SCD stored procedure. MS needs to go back to the drawing board here.

8. There is a handy trick I stole from somewhere on the Net to make the sluggard performance of the Lookup component work much better. If you "prime" the lookup with a SELECT statement in the initiation step, it seems to make much better use of the lookup tables indexes thereby increasing performance dramatically. I can provide an example of the initialization code if someone is interested.

9. Deploying SSIS packages to multiple environments on different domains can be a challenge. Setting DelayValdation=True on all SSIS packages will help. However, if you have to modify these packages after deployment (as you will if you dev on Win32 and deploy on Win64), modifications made on a server set for low priority foreground processing can be VERY slow and tedious while SSIS VALIDATES (yes I know you set DelayValidation=True), but it still validates the package when you attempt to make changes.

Happy SSIS'ing!
"The meaning of life is...it ends

 

[MDXer]

Very intrested in the method for for Item 8. Having a few large dimensions but not excesssivley large this may prove helpful.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dmcmunn]

Here is a code snippet to make the Lookup component perform a bit better...

Imports System
Imports System.Data
Imports System.Math
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.SqlClient
Imports System.Text

Public Class ScriptMain
    Inherits UserComponent
    Dim _dim_table As DataTable
    Dim _counter As Int32

    Public Overrides Sub PreExecute()
        Dim ds_dim As New DataSet
        Dim connString As String = Me.Connections.DWCONN.ConnectionString()
        Dim conn As New OleDb.OleDbConnection(connString)
        Dim adapter As New OleDb.OleDbDataAdapter("Select dw_id, business_key, date_updated, dw_create_date, dw_expired_date from view_dim_table", conn)
        conn.Open()
        Try
            adapter.Fill(ds_dim)
            _dim_table = ds_dim.Tables(0)

            ' the values used here only need to match in data type...the specific value does not seem to matter

            _dim_table.Select("business_key = 'whatever' and date_updated = '01/01/1900' and dw_expired_date = '01/01/1900'")
        Finally
            conn.Close()
        End Try
        Me.ComponentMetaData.FireInformation(1, "Lookup Cache", "Lookup cached: " + _dim_table.Rows.Count.ToString(), "", 0, True)
        MyBase.PreExecute()
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Dim sb As New StringBuilder()
        sb.Append("business_key = '")
        sb.Append(Row.business_key.ToString() + "'")
        sb.Append(" AND '" + Row.date_updated.ToShortDateString() + "' >= date_updated and '" + Row.date_updated.ToShortDateString() + "' <= dw_expired_date")

        Dim sql As String
        sql = sb.ToString()
        Dim rows() As DataRow = _dim_table.Select(sql)

        Select Case rows.Length
            Case Is > 0
                Row.dw_id = CType(rows(0)("dw_id"), ULong)
                Row.DirectRowTodimlookupresult()   ' the name of the output (dimlookupresult) for a match


            Case Else
                Row.ErrorCode = 1
                Row.DirectRowTodimlookuperror()    ' the name of the output (dimlookuperror) for an miss

        End Select
        

    End Sub

End Class

Of course you will have to adjust the SQL and VBScript to your table name, column names and data types. I have seen a dramatic performance improvement when priming the component with a SELECT statement in this manner.

Good luck and Enjoy SSIS!

 

[steitel1]

is ssis' scripting language actually VBScript or is it really VB .net? The reason I ask is that VBScript is supposed to be more limited/complicated than VB .net in setting up asynchronous threads for things like emails etc.

 

[dmcmunn]

You have what are called "VBScript components" which you can use in numerous ways to manipulate and configure other SSIS components or perform processing at runtime or even on events. However, the language used by "VBScript components" is VB.NET, not VBScript as in classic ASP, VBScript or VB.

Hope this helps.