Integration Services (SSIS) internals and performance tuning

Back in 2014, I created a 214-page deck for a pre-con at SQLSaturday Johannesburg and Cape Town. Since then I’ve presented it at a good number of large and small conferences.
I don’t often work with SSIS anymore, so I thought I’d publish the entire deck here.
The material is quite text-heavy, so it should be readable. I compiled the deck from several sources (see the last four slides). Also, please note it doesn’t cover SSIS 2016 and the buffer optimizations that came in that version.

Integration Services (SSIS) internals and performance tuning

Regional and language settings for Oracle client used with SQL Server linked server

Today, I ran into a problem while migrating a Data Warehouse solution to another server. 
The solution have linked servers for extracting data from an Oracle source system. 
The query would go something like this: 

INSERT INTO dbo.MyTable 
SELECT *
FROM OPENQUERY(UNDPFSP1ICC, 'SELECT
 TO_CHAR(COL1_DT, ''YYYY'') AS Col1Year
 , COL2
 , COL3
 , SUM(AMOUNT) AS Amount
 , SYSDATE
FROM MYTABLE
GROUP BY
 TO_CHAR(COL1_DT, ''YYYY'')
 , COL2
 , COL3
')

I had migrated everything, but while running the extract job, which ran queries like the above, we would get errors like these: 
Conversion failed when converting the nvarchar value '123456,78' to data type int. 
Error converting data type nvarchar to numeric. 
The problem was that instead of using a dot (.) as the decimal symbol, a comma (,) was being returned. This would make the data convert to nvarchar, and an implicit conversion to a NUMERIC or INT would fail. 
Everything was running fine on the old server. The collation of the server and databases were identical (Latin1_General_CI_AS), but the regional and language settings were different. On the old server they were set to English (United States) and on the new server to Danish. Changing the settings on the new server to English (United States) did not help. 
After troubleshooting a bit on #sqlhelp with Amit Banerjee (twitter | blog) and William Durkin (twitter | blog), as well as via Skype with Jørgen Guldmann (twitter | blog), Jørgen ended up pointing me in the right direction. 
The problem as that the NLS_LANG parameter was set to Danish (where we use a comma (,) as the decimal symbol). It is stored in the registry under the following sub-key: 
HKEY_LOCAL_MACHINESOFTWAREORACLEHOMEIDNLS_LANG 
Where ID is the unique number identifying the Oracle home. 
The trick was to change this sub-key from: 
DANISH_DENMARK.WE8MSWIN1252 
To: 
AMERICAN_AMERICA.WE8MSWIN1252 
And then everything worked fine, and the decimal symbol was now a dot (.) instead of a comma (,).

Regional and language settings for Oracle client used with SQL Server linked server

Create Data Warehouse extract tables from SQL Server source system

I have often found that I needed a quick script for giving me CREATE TABLE statements for each table in a source system database, so I could create these tables in my extract schema in my Data Warehouse. This can be done using SQL Server Management Studio, but I found that I would then have to edit each statement to remove keys, indexes, identity columns, and everything else that I rarely want in my extract tables.
Having not being able to find one, I wrote my own, and though I’d share it here. The following script writes a CREATE TABLE statement for each table in a SQL Server source database, so I can use them for creating extract tables in a data warehouse.

DECLARE @ExtractSchema NVARCHAR(100) = 'Extract'
SELECT 'CREATE TABLE [' + @ExtractSchema + '].['
      + SCHEMA_NAME (t.schema_id)
      + '_' + t.name + '] (' + LEFT(cols .column_list, LEN(cols .column_list) - 1) + ')'
FROM sys.tables t
CROSS APPLY (SELECT '[' + c .name + '] ' + UPPER(ty .name)
                    + CASE WHEN ty.name IN ('decimal', 'numeric') THEN
                              '(' + CONVERT(NVARCHAR(10), c.precision)
                              + ', '
                              + CONVERT (NVARCHAR(10), c.scale)
                              + ')'
                      WHEN ty.name = 'float' THEN
                         '(' + CONVERT(NVARCHAR(10), c.precision ) + ')'
                      WHEN ty.name IN ('datetime2', 'datetimeoffset', 'time') THEN
                         '(' + CONVERT(NVARCHAR(10), c.scale) + ')'
                      WHEN ty.name IN ('char', 'nchar', 'varchar', 'nvarchar') THEN
                         '(' + CASE WHEN c.max_length = - 1
                                   THEN 'MAX'
                                   ELSE CAST (c.max_length AS NVARCHAR(10))
                              END + ')'
                      ELSE ''
                      END
                    + CASE WHEN c.is_nullable = 0 THEN ' NOT'
                      ELSE ''
                      END
                    + ' NULL, '
               FROM sys.columns c
               INNER JOIN sys.types ty
               ON c.user_type_id = ty.user_type_id
               WHERE t.object_id = c.object_id
               ORDER BY c.column_id
               FOR XML PATH('')
      ) cols (column_list)
Create Data Warehouse extract tables from SQL Server source system