SQL Server 2016 CTP 2 Preview Now Available

Back in the beginning of May, Microsoft announced SQL Server 2016 and the massive amount of new features that are going to be released. Combining this with all of the Power BI enhancements and Azure enhancements that are being released, it is a great time to be a Microsoft BI professional. This afternoon the SQL Server team has now released the first publicly available preview for SQL Server 2016, as referenced in this blog post! The download is available here.

If you’ve forgotten all of the amazing new features that are scheduled for release with this version, be sure to check out the data sheet.

What are you waiting for? Download your copy today!

SQL Server 2016 is coming

Yesterday at the keynote for the Microsoft Ignite conference Satya Nadella announced the next version of SQL Server, which will be available next year and in public preview later this summer. Rather than hitting all the marketing jumbo that’s been repeated elsewhere, I’d like to focus on the three biggest enhancements for BI and why you should already start thinking about upgrading to the next version.

R Integration directly into SQL Server

Much like we’ve had the ability to write DMX queries against SSAS Multidimensional models, having the ability to write R code directly into SQL Server is going to open SQL Server up to a much broader user base, and empower more people to analyze their data in different ways.

Enhanced SSIS

A feature I myself overlooked the first time I read the data sheet yesterday, but the ability to select the specific version of SSIS you want to build to and support for Power Query is going to be some serious awesome-sauce. Chris Webb (b|t) is showing us some awesome stuff that M can do, and giving SSIS the ability to capitalize will make for some very efficient ETL, especially when it’s combined with the ability to pull data from cloud sources via Azure Data Factory. The ability to select what version of SSIS you want to build for is a feature that’s long overdue, and as a consultant I couldn’t be happier.

Polybase into SQL Server

This feature alone justifies the upgrade. As a consultant, I hear a lot of companies talking about Hadoop and what to do with their unstructured data. Should we invest in Hadoop? What are the big barriers to entry? One of the biggest has been cost, especially since to date Polybase has only been available in APS (Analytic Platform System), but by putting Polybase into SQL Server directly Microsoft is really going to be able to step ahead of the entire market. Imagine being able to run this query:

SELECT SUM(salesAmount), h.CustomerInfo

FROM MyFactTable

JOIN HDFS.dbo.Customers

No more heavy loading of needing to write MapReduce jobs and complicated logic to get unstructured data and structured data together for analysis. Now, it just works. This combined with Azure SQL Data Warehouse announced last week at Build has me extremely excited about the rest of the year and 2016!

The complete data sheet is available for download here.

SSIS: Password Policy Rules Not Met When Creating Catalog

Last week I was at a client site showing them how to set up the Integration Services Catalog. We were walking through the steps in how to create it and we input the password into the Create Catalog configuration screen. It was a strong password, that adhered to all domain requirements. However, when we clicked Ok, we received the following error:

TITLE: Microsoft SQL Server Management Studio

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Management.IntegrationServices)

——————————

ADDITIONAL INFORMATION:

The transaction ended in the trigger. The batch has been aborted.

========================

   PASSWORD POLICY RULES NOT MET

========================

Login: “##MS_SSISServerCleanupJobLogin##” either has “Enforce password policy” check set to OFF

OR

has “Enforce password expiration” check set to ON.

[Correct Values: “Enforce password policy” = Checked; “Enforce password expiration”= Unchecked ]

Rolling back…

===============================================

(Microsoft SQL Server, Error: 3609)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3000&EvtSrc=MSSQLServer&EvtID=3609&LinkId=20476

——————————

BUTTONS:

OK

——————————

Ok, let’s check our password again. Type a stronger password, try it again. Still no luck. Going out to Bingoogle didn’t provide much assistance, apart from some old SQL Server 2000 issues with this from the database engine when creating a login. So that’s not it.

Let’s go check the local security policy and the domain policy. Yep, password meets requirements. Don’t see anything that’s terribly out of wack. So my next step was to go check the event viewer and application logs to look for an error, but there was nothing there. This server was created by another team off-site, so let’s go check the OS locale settings and the collation level for SQL Server to see if there are some settings off somewhere. still nothing! At this point I’m starting to sweat a little. In this environment, we’d created two servers (one for SSIS and the DB Engine and one for SSAS), so let’s switch over to the other server and see if it happens there too. yep, same problem. My next step was to create an inbound rule in the firewall settings, thinking that maybe there was some port blocking happening somewhere. A twitter out reach on #sqlhelp didn’t yield any results either. Although I do want to throw a quick thanks out to Robert Davis and Denny Cherry for the assistance!

When that still failed, I figured I’d just try and create the Catalog with Powershell. Fortunately, the code to do this is readily available on MSDN:

# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

# Provision a new SSIS Catalog
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "P@assword1")
$catalog.Create()

I ran it locally on the server, and it failed with the same issue. There has to be something in the internal scripts causing this issue. So my next step was to go back to the basics. We do know that the issue is being caused by this specific login as a part of the process: “##MS_SSISServerCleanupJobLogin##”. Well, CleanupJobLogin sounds like the account that’s used to run the SSIS Server Maintenance Job. If we can get everything to create except for the job, that gives us the Catalog and the main SSISDB. All that login does is serve as the job owner for the “SSIS Server Maintenance Job”, which runs two procedures in the SSISDB: [internal].[cleanup_server_retention_window] and [internal].[cleanup_server_project_version].

Fire up Profiler, and run a trace. Go back in to “Create Catalog”, type the password, and click Ok. I know it’s going to fail, but that’s not what I’m after here. Go into the trace and capture all of the SQL scripts that the Catalog is running.

select SERVERPROPERTY(N’servername’)
DECLARE @edition sysname;
SET @edition = cast(SERVERPROPERTY(N’EDITION’) as sysname);
select case when @edition = N’SQL Azure’ then 2 else 1 end as ‘DatabaseEngineType’

exec sp_executesql N’SELECT
”IntegrationServices[@Name=” + quotename(CAST(SERVERPROPERTY(N”Servername”) AS sysname),””””) + ”]” AS [Urn],
CAST(SERVERPROPERTY(N”Servername”) AS sysname) AS [Name]
WHERE
(CAST(SERVERPROPERTY(N”Servername”) AS sysname)=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’PWIRTPTPSP3W12′

SELECT ISNULL(HAS_PERMS_BY_NAME(null, null, ‘ALTER SETTINGS’), 0)
SELECT ISNULL(IS_SRVROLEMEMBER (‘sysadmin’), 0)
GO
————————————————————–

USE [master]
select SERVERPROPERTY(N’servername’)
DECLARE @edition sysname;
SET @edition = cast(SERVERPROPERTY(N’EDITION’) as sysname); select case when @edition = N’SQL Azure’ then 2 else 1 end as ‘DatabaseEngineType’

exec sp_executesql N’SELECT
”IntegrationServices[@Name=” + quotename(CAST(SERVERPROPERTY(N”Servername”) AS sysname),””””) + ”]” AS [Urn],
CAST(SERVERPROPERTY(N”Servername”) AS sysname) AS [Name]
WHERE
(CAST(SERVERPROPERTY(N”Servername”) AS sysname)=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’PWIRTPTPSP3W12′
GO

———————————————————————————–

USE [master]
–exec sp_reset_connection
DECLARE @edition sysname;
SET @edition = cast(SERVERPROPERTY(N’EDITION’) as sysname); select case when @edition = N’SQL Azure’ then 2 else 1 end as ‘DatabaseEngineType’
SELECT
cfg.name AS [Name],
cfg.configuration_id AS [Number],
cfg.minimum AS [Minimum],
cfg.maximum AS [Maximum],
cfg.is_dynamic AS [Dynamic],
cfg.is_advanced AS [Advanced],
cfg.value AS [ConfigValue],
cfg.value_in_use AS [RunValue],
cfg.description AS [Description]
FROM
sys.configurations AS cfg

exec sp_executesql N’
        –Preparing to access the Catalog object
        DECLARE @t_catalogs TABLE (
        Name sysname COLLATE SQL_Latin1_General_CP1_CI_AS,
        EncryptionAlgorithm nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
        SchemaVersion int,
        SchemaBuild nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
        OperationLogRetentionTime int,
        MaxProjectVersions int,
        OperationCleanupEnabled bit,
        VersionCleanupEnabled bit,
        ServerLoggingLevel int,
        OperationLogNumberOfRecords int,
        VersionLogNumberOfRecords int)

        IF DB_ID(”SSISDB”) IS NOT NULL
        BEGIN
        INSERT INTO @t_catalogs VALUES(
        ”SSISDB”,
        (SELECT [property_value] FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”ENCRYPTION_ALGORITHM”),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”SCHEMA_VERSION”),
        (SELECT [property_value] FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”SCHEMA_BUILD”),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”RETENTION_WINDOW”),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”MAX_PROJECT_VERSIONS”),
        (SELECT CAST([property_value] AS BIT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”OPERATION_CLEANUP_ENABLED”),
        (SELECT CAST([property_value] AS BIT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”VERSION_CLEANUP_ENABLED”),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”SERVER_LOGGING_LEVEL”),
        (SELECT COUNT(operation_id) FROM [SSISDB].[catalog].[operations]),
        (SELECT COUNT(object_id) FROM [SSISDB].[catalog].[object_versions])
        )
        END

SELECT
”IntegrationServices[@Name=” + quotename(CAST(SERVERPROPERTY(N”Servername”) AS sysname),””””) + ”]” + ”/Catalog[@Name=” + ”””” + REPLACE((SELECT Name from @t_catalogs), ””””, ””””””) + ”””” + ”]” AS [Urn],
(SELECT Name from @t_catalogs) AS [Name],
(SELECT EncryptionAlgorithm from @t_catalogs) AS [EncryptionAlgorithm],
(SELECT SchemaVersion from @t_catalogs) AS [SchemaVersion],
(SELECT SchemaBuild from @t_catalogs) AS [SchemaBuild],
(SELECT OperationLogRetentionTime from @t_catalogs) AS [OperationLogRetentionTime],
(SELECT MaxProjectVersions from @t_catalogs) AS [MaxProjectVersions],
(SELECT OperationCleanupEnabled from @t_catalogs) AS [OperationCleanupEnabled],
(SELECT VersionCleanupEnabled from @t_catalogs) AS [VersionCleanupEnabled],
(SELECT ServerLoggingLevel from @t_catalogs) AS [ServerLoggingLevel],
(SELECT OperationLogNumberOfRecords from @t_catalogs) AS [OperationLogNumberOfRecords],
(SELECT VersionLogNumberOfRecords from @t_catalogs) AS [VersionLogNumberOfRecords]
WHERE
(CAST(SERVERPROPERTY(N”Servername”) AS sysname)=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’PWIRTPTPSP3W12′

SELECT ISNULL(IS_SRVROLEMEMBER (‘sysadmin’), 0)

exec sp_executesql N’SELECT name FROM msdb.sys.sysdatabases WHERE name = @dbname’,N’@dbname nvarchar(6)’,@dbname=N’SSISDB’

declare @key_value nvarchar(1024); exec master.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’,’SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\Setup\DTSPath’, N”, @key_value output;select @key_value;

DECLARE @CatalogFileExists bit
if object_id(‘tempdb..#t’) is not null drop table #t
BEGIN
       DECLARE @CatalogFile nvarchar(1024)
       SELECT @CatalogFile = N’C:\Program Files\Microsoft SQL Server\110\DTS\Binn\SSISDBBackup.bak’
       CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
       INSERT #t EXEC xp_fileexist @CatalogFile
       SELECT TOP 1 @CatalogFileExists=file_exists from #t
END
SELECT @CatalogFileExists
— check whether the database is already exist and with correct version
SET NOCOUNT ON

IF DB_ID(‘SSISDB’) IS NOT NULL
    RAISERROR(27135, 16, 1, ‘SSISDB’)

IF SUBSTRING (CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(20)),1,2) <> ’11’
    RAISERROR (27193,16,1, ‘Denali’) WITH NOWAIT

SELECT [value_in_use] FROM sys.configurations WHERE [name] = ‘clr enabled’
DECLARE @path nvarchar(1024) = Convert(nvarchar(1024),ServerProperty(‘MasterFile’)); SELECT @path = SUBSTRING(@path, 1, CHARINDEX(N’master.mdf’, LOWER(@path)) – 1); SELECT @path;
–DECLARE @CatalogFileExists bit
if object_id(‘tempdb..#t’) is not null drop table #t
BEGIN
       –DECLARE @CatalogFile nvarchar(1024)
       SELECT @CatalogFile = N’D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf’
       cREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
       INSERT #t EXEC xp_fileexist @CatalogFile
       SELECT TOP 1 @CatalogFileExists=file_exists from #t
END
SELECT @CatalogFileExists
–DECLARE @CatalogFileExists bit
if object_id(‘tempdb..#t’) is not null drop table #t
BEGIN
       –DECLARE @CatalogFile nvarchar(1024)
       SELECT @CatalogFile = N’D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf’
       CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
       INSERT #t EXEC xp_fileexist @CatalogFile
       SELECT TOP 1 @CatalogFileExists=file_exists from #t
       DROP TABLE #t
END
SELECT @CatalogFileExists
exec sp_executesql N’RESTORE FILELISTONLY FROM DISK = @backupfile’,N’@backupfile nvarchar(67)’,@backupfile=N’C:\Program Files\Microsoft SQL Server\110\DTS\Binn\SSISDBBackup.bak’
exec sp_executesql N’RESTORE DATABASE @databaseName FROM DISK = @backupFile  WITH REPLACE, MOVE @dataName TO @dataFilePath, MOVE @logName TO @logFilePath’,N’@databaseName nvarchar(6),@dataName nvarchar(4),@dataFilePath nvarchar(65),@logName nvarchar(3),@logFilePath nvarchar(65),@backupFile nvarchar(67)’,@databaseName=N’SSISDB’,@dataName=N’data’,@dataFilePath=N’D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf’,@logName=N’log’,@logFilePath=N’D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf’,@backupFile=N’C:\Program Files\Microsoft SQL Server\110\DTS\Binn\SSISDBBackup.bak’
GO
————————————————————————-

USE master; 

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name]=’SSISDB’ AND [is_read_only] = 1)  ALTER DATABASE [SSISDB] SET READ_WRITE WITH ROLLBACK IMMEDIATE

USE [SSISDB];

IF EXISTS (SELECT [name] FROM sys.symmetric_keys WHERE [name] = ‘##MS_DatabaseMasterKey##’)
    DROP MASTER KEY

exec sp_executesql N’USE [SSISDB];DECLARE @pwd nvarchar(4000) = REPLACE(@password, N””””, N””””””);EXEC(”CREATE MASTER KEY ENCRYPTION BY PASSWORD = ””” + @pwd + ””””);’,N’@password nvarchar(10)’,@password=N’Password@1′
GO
——————————————————————————-

USE master

   IF NOT EXISTS(SELECT * FROM sys.asymmetric_keys WHERE name = ‘MS_SQLEnableSystemAssemblyLoadingKey’)
        CREATE ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey FROM EXECUTABLE FILE = ‘C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll’

   IF EXISTS(SELECT [name] FROM sys.server_principals where name = ‘##MS_SQLEnableSystemAssemblyLoadingUser##’)
        DROP LOGIN ##MS_SQLEnableSystemAssemblyLoadingUser##

  CREATE LOGIN ##MS_SQLEnableSystemAssemblyLoadingUser## FROM ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey
    GRANT UNSAFE ASSEMBLY TO ##MS_SQLEnableSystemAssemblyLoadingUser##

  IF EXISTS(SELECT name FROM sys.procedures WHERE name=N’sp_ssis_startup’)
    BEGIN
       EXEC sp_procoption N’sp_ssis_startup’,’startup’,’off’
       DROP PROCEDURE [sp_ssis_startup]
    END
GO

   CREATE PROCEDURE [dbo].[sp_ssis_startup]
    AS
    SET NOCOUNT ON
        /* Currently, the IS Store name is ‘SSISDB’ */
        IF DB_ID(‘SSISDB’) IS NULL
            RETURN
        IF NOT EXISTS(SELECT name FROM [SSISDB].sys.procedures WHERE name=N’startup’)
            RETURN
        /*Invoke the procedure in SSISDB  */
        EXEC [SSISDB].[catalog].[startup]

   IF (0=1)
    BEGIN
        /* Run sp_ssis_startup when Sql Server restarts */
        EXEC sp_procoption N’sp_ssis_startup’,’startup’,’on’
    END
GO
——————————————————————————–

–USE msdb

–IF EXISTS (SELECT name FROM sysjobs WHERE name = N’SSIS Server Maintenance Job’)
–EXEC sp_delete_job
—    @job_name = N’SSIS Server Maintenance Job’ ;

–IF EXISTS(SELECT * FROM sys.server_principals where name = ‘##MS_SSISServerCleanupJobLogin##’)
—    DROP LOGIN ##MS_SSISServerCleanupJobLogin##

–DECLARE @loginPassword nvarchar(256)
–set @loginPassword = ‘Password@1’

–SELECT @loginPassword = REPLACE (CONVERT( nvarchar(256), CRYPT_GEN_RANDOM( 64 )), N””, N”””)
–EXEC (‘CREATE LOGIN ##MS_SSISServerCleanupJobLogin## WITH PASSWORD =”’ +@loginPassword + ”’, CHECK_POLICY = ON’)

 

There, at the end of the script, is the cause of the consternation. So it can run everything successfully except for that account. I commented out those lines of code and reran the script. Success!!! AWESOME-SAUCE! One catalog ready to do some cool stuff. Note that you could also use this if you just want to create the catalog with t-sql. The last step to do is create the job manually to call those procedures and prevent SSISDB from growing too large.

SIDE NOTE: the main password for the Catalog is on line 161. Make sure you change this if you are using this to create your catalog.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’SSIS Server Maintenance Job’,
        @enabled=1,
        @notify_level_eventlog=2,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N’Runs every day. The job removes operation records from the database that are outside the retention window and maintains a maximum number of versions per project.’,
        @category_name=N'[Uncategorized (Local)]’,
        @owner_login_name=N’DOMAIN\USERNAME‘, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [SSIS Server Operation Records Maintenance]    Script Date: 2/27/2015 8:17:49 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’SSIS Server Operation Records Maintenance’,
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=3,
        @retry_interval=3,
        @os_run_priority=0, @subsystem=N’TSQL’,
        @command=N’EXEC [internal].[cleanup_server_retention_window]’,
        @database_name=N’SSISDB’,
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’SSIS Server Max Version Per Project Maintenance’,
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=3,
        @retry_interval=3,
        @os_run_priority=0, @subsystem=N’TSQL’,
        @command=N’EXEC [internal].[cleanup_server_project_version]’,
        @database_name=N’SSISDB’,
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’SSISDB Scheduler’,
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20001231,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=120000,
        @schedule_uid=N’82cb4d40-977f-480b-9c59-bf7509e16740′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

And there you have it. Creating the SSIS Catalog via t-sql.

 

~Chris

Dynamicallly writing those pesky Update Statements

Whenever you are loading a data warehouse, often one of the most time consuming tasks is writing the update statements that you need in order to properly handle changing records coming from your system. Further adding to development time, columns are then split into type 1 or type 2 attributes, and in my current shop, processes for deletes out of the source system as well. After you write a few of these, that loving feeling you got when you started starts to dissipate, and you start to feel bored with the same statements. So with that in mind, I wrote the below procedure that takes a table name and an update_type variable of U, V, or D and then generates the appropriate update, type 2 version, or delete statement you need. The procedure makes 2 assumptions, one in that you have a separate etl_working database to handle set based updates, and the second that your table primary keys contain of column named “version_key” as a part of the naming convention for your tables primary key. If this isn’t your naming convention, feel free to change it.

UPDATE: 01/21/2015: found a bug in the version code, fixed. Sorry about that!

/*
Author: Christopher Schmidt
Date: 2015-01-19
Description: taking the table name and an update type of U, V, or D as inputs, generates an update statement against the target table. This proc makes an assumption that
there is a separate database titled “etl_working”, that consists of a mirror copy of the target table to handle updates in a set based manner. This can be adjusted in the
code below on lines 55,58,65, and 67.

It also assumes that your primary_key of the target table contains the words
“version_key”. if you do not use this naming convention, you can update them in the where clauses of the column selects on lines 34,40,46, and 52.

As always, with any code you find on the internet, test and use in development.

*/

CREATE PROCEDURE [dbo].[update_generator](
@tbl varchar(200)
,@update_type char(1)
)
AS

DECLARE @sqlu varchar(5000)
DECLARE @sqld varchar(5000)
DECLARE @sqlv varchar(5000)
DECLARE @cols varchar(4000)
DECLARE @colsi varchar(4000)
DECLARE @colsv varchar(4000)
DECLARE @keycol varchar(200)
DECLARE @keycold varchar(200)

set @cols = stuff(( select ‘,’+quotename(C.name) + ‘=Src.’ + quotename(c.name)
from sys.columns as C
where (C.object_id = object_id(@tbl)
AND c.name NOT LIKE ‘%version_key’)
for xml path(”)), 1, 1, ”)

SET @colsi = stuff(( select ‘,’+quotename(C.name)
from sys.columns as C
where (C.object_id = object_id(@tbl)
AND c.name NOT LIKE ‘%version_key’)
for xml path(”)), 1, 1, ”)

SET @keycol = stuff(( select ‘,’+quotename(C.name) + ‘=Src.’ + quotename(c.name)
from sys.columns as C
where (C.object_id = object_id(@tbl)
AND c.name LIKE ‘%version_key’)
for xml path(”)), 1, 1, ”)

SET @keycold = stuff(( select ‘,src.’+quotename(C.name) + ‘= dw.’ + quotename(c.name)
from sys.columns as C
where (C.object_id = object_id(@tbl)
AND c.name LIKE ‘%version_key’)
for xml path(”)), 1, 1, ”)

SET @sqlu = N’UPDATE [‘ + @tbl + ‘] SET ‘ + @cols + ‘ FROM change.[‘ + @tbl + ‘] [src] JOIN dbo.[‘ + @tbl + ‘] [dst] ON dst.’ + @keycol + ‘ WHERE Update_Type = ”U”’

SET @sqld = N’UPDATE [‘ + @tbl + ‘] SET dmproc_batch_key=src.dmproc_batch_key, source_revision_action = ”D”, version_end_dtmx = GETDATE(), version_current_ind = 0
FROM change.[‘ + @tbl + ‘] [src] JOIN dbo.[‘ + @tbl + ‘] [dst] ON dst.’ + @keycol + ‘ WHERE Update_Type = ”D”’

SET @sqlv = N’UPDATE ‘ + @tbl + ‘ SET version_current_ind = 0
,version_latest_ind = 0
,dmproc_batch_key=src.dmproc_batch_key
,version_end_dtmx=GETDATE()
,source_revision_end_dtmx = GETDATE()
,source_revision_action = ”V” FROM change.[‘ + @tbl + ‘] [src] JOIN dbo.[‘ + @tbl + ‘] [dst] ON dst.’ + @keycol + ‘ WHERE Update_Type = ”V”
GO
INSERT INTO dbo.[‘ + @tbl + ‘] ( ‘ + @colsi + ‘) SELECT ‘ + @colsi + ‘FROM change.[‘ + @tbl + ‘] WHERE Update_Type = ”V”’

SELECT
CASE
WHEN @update_type = ‘U’ THEN @sqlu
WHEN @update_type = ‘D’ THEN @sqld
WHEN @update_type = ‘V’ THEN @sqlv
END [sql]

 

 

Hope it saves you some time!

SSIS: Dynamically setting the Default Buffer Max Rows property

I’ve blogged in the past about the Default Buffer Max Rows property in SSIS, and how powerful it can be. As a recap, there are 2 properties in SSIS that control the amount of data that your ETL package will hold in any given buffer: The DefaultBufferMaxRows property, and the DefaultBufferSize. When performance tuning SSIS packages, we can modify either one of these to adjust how much data SSIS can carry through a buffer at once.  By default, this is 10,000 rows or 10 MB. One thing I didn’t like about my “CalculateDefaultBufferSize” spreadsheet is that it made an assumption that you were going to change your DefaultBufferSize to 100MB. So this is 100 MB per component that is running in our data flow, which can become extremely costly. If you leave it at the default however, you’re  still using 10 MB per component no matter what. If we have a really skinny data set we’re importing (such as a fact table or a hub), it’s very possible we could be leaving a lot of empty space in the data flow if we stop every 10,000 rows. Conversely,  if we have a really wide dataset that we can only fit 6,000 or 7,000 rows in a buffer, we’re having to make extra hops to move that same data. It’s always a balancing act. I recently decided to see if I could automate the setting of this property in order to maximize the efficiency of my SSIS packages without needing to manually calculate it out every time.

To start, we need to find our destination table and identify the maximum row length in that table. Fortunately, this can be obtained with a simple query (using DimCurrency in the AdventureWorksDW2012):

SELECT
    SUM (max_length)  [row_length]
FROM  sys.tables t
JOIN sys.columns c
    ON t.object_id=c.object_id
JOIN sys.schemas s
    ON t.schema_id=s.schema_id
WHERE t.name = ‘DimCurrency’
    AND s.name = ‘dbo’

You could easily turn this into a procedure to take the tablename and schema as a parameter, and return it. For this post, I’m going to leave it as a query. Once we have this, it becomes a simple exercise in math. The table above returns a max row length of 118 (in bytes), so we take this value and divide it into 1our default value (which fortunately is stored in SSIS in bytes as well – as 10485760). So our calculation becomes:

10485760/118

giving us a result of 88,862 rows. We’ll round down to the closest 100, and that will be our default buffer max rows property. Now how do we make this dynamic? To start, we open up our SSIS package and add an Execute SQL task into the Control Flow. A good item to improve upon this would be take this and put in a Pre-Execute SQL task, and write the value to an information table. Then you can log it and do some reporting around what your buffers are. As an added bonus with this dynamic structure: as your data scales, your ETL package will too!

image

Next we want to create 3 variables: str_table_name, str_schema_name, and int_row_size. str_table_name and str_schema_name should be strings, and int_row_size should be an Int32. For the int_row_size variable, set the Value to 1. This is going to avoid an error in our expression later. This way we can parameterize our inputs for our destination tables and the corresponding values, and create a placeholder for our incoming result.  If your package names are the same as the destination tables then instead of having to assign this value manually you could just configure it from an expression, using the @[System::PackageName] global variable:

image

Let’s open up our Execute SQL Task and configure it. The connection will point to our destination database, and the SQL query will be as we’ve outlined above, subbing parameters for the table and schema name:

image

Do the parameter mapping on the 2nd tab to parameterize your table and schema with the variables you created above, and set your result set to single row. The single row result set should map to your int_row_size variable. Now for the fun part. Create a data flow task, and right click on it and go to properties.

image

Under expressions, select DefaultBufferMaxRows and expand the expression ellipses. For the expression, use the following logic:

@[User::int_row_size]==1? 10000: 10485760/ @[User::int_row_size]

The initial test of the value is to ensure that if the sql fails to get the proper row size, your buffers remain at the default. And that’s it! Now you can dynamically set your default buffer max rows for ssis!

Now that’s some awesome-sauce.

Chris

BIML: Columbus SQL Server User Group

I am pleased to announce that I’ll be presenting on BIML for the Columbus SQL Server User Group on December 11th at the Microsoft Polaris office in Westerville, OH. If you’re located in or around the central OH area, please consider joining us! Here is a link to the chapter website.

Rethink your ETL with BIML

It’s estimated that 70% of data warehousing development is in ETL (extract, transformation, and load) between the different source systems, your data warehouse, and/or your data mart. Recently a new tool has entered the marked that promised to revolutionize the way we think about ETL processes. Gone are the days where we need to manually code every component, every event handler, and painstakingly ensure that every package adheres to corporate standards. In this session, Chris and Harsh will introduce you to the world of BIML and how it can change your world. We’ll show you how to quickly create a template in BIML that will make sure all of your packages adhere to corporate standards, and then use metadata to quickly generate dozens of packages to populate your dimensions and facts. Stop spending weeks developing SSIS packages that can be built in hours! You won’t want to miss this session that will get your boss to say “I don’t think we’re paying you enough!”

~Chris