maandag 26 juni 2017

SQL : Deploying a SSIS project with a stored procedure

Introduction

A blogpost of Hans Michiels caught my attention when I was looking on how to deploy SSIS packages to the SSIS Catalog in SQL Server in a standard manner. The problem I had was that it was bit time consuming to deploy SSIS packages and I wanted to create projects, environments and environment variables on the fly from a script.

This blogpost and this stored procedure is written for a particular situation and I don't suggest that you can use it in every situation. In my situation it works perfectly but no guarantees that it will work in every situation. Test it and try it your self (and not in a production environment ;-) )

Used model

For this script, I've used the following model to understand the working of the SSIS catalog and Visual Studio SSIS project. On the left the Visual studio SSIS project is shown with the project "Project" and the parameters "Parameter1" and "Parameter2".



On the right hand of the picture, the SSIS Catalog with a folder, the project and the environment is depicted. The project in the SSIS catalog is a deployed project of the VS SSIS project. The deployed project in the SSIS catalog is connected with the environment and the parameters and environment variables are also connected.

Projectparameters

For this blog post I created a couple of project parameters in the SSIS Visual Studio project. A couple of connectionstrings that I pass to the connection mangers in the SSIS project.




The Stored Procedure 

In the box below, I've copied the stored procedure that I've created and there are a couple of things to keep in mind. First, the parameters of the stored procedure are important. These parameters controls the behavior of the stored procedure:
  • @folder_name : the name of the folder in the SSIS catalog. Anything is possible.
  • @environment_name : The name of the environment (DEV or TEST)
  • @environment_description : a free text description.
  • @project_name : This is the name of the SSISproject as entered in the properties in the VS project. This is important because this is needed for deploying of the ispac file (built VS SSIS project).
  • @ispacpath :  The path and the name of the built ispac file.
  • @SQLServerName : This parameter is used in the connectionstrings that are passed to the connectionmanagers in the SSIS packages. Working with @@Servername is handy but not always use able when working on multiple systems.
  • @projectparam1. This could be another setting that you want to change in case you deploy the SSIS project to a server. 
The stored procedure code is copied in the box below:

CREATE PROCEDURE dbo.usp_CreateAndDeploySSISProject
    @folder_name AS sysname, 
    @environment_name AS nvarchar(128),
    @environment_description AS nvarchar(128),
    @project_name AS nvarchar(128),  
    @ispacpath AS nvarchar(500) , 
    @SQLServerName AS  nvarchar(100), 
    @projectparam1 AS  nvarchar(100)                               
AS
BEGIN
 --------------------------------------------------------------------------------
 --Variables Section
 --------------------------------------------------------------------------------
 DECLARE @environment_variables_table TABLE(
  [id] int identity(1, 1),
  [variable_name] nvarchar(128),
  [data_type] nvarchar(128),
  [sensitive] bit,
  [value] nvarchar(4000),
  [description] nvarchar(1024)
  )
 
 DECLARE @object_parameter_value_table TABLE(
  [id] int identity(1, 1),
  [object_type] smallint,
  [object_name] nvarchar(260),
  [parameter_name] nvarchar(128),
  [parameter_value] sql_variant,
  [value_type] char(1)
 )
 
 DECLARE @id int
 DECLARE @max_id int
 --             DECLARE @folder_name sysname
 --             DECLARE @environment_name nvarchar(128)
 --             DECLARE @environment_description nvarchar(128)
 --             DECLARE @project_name nvarchar(128)
 DECLARE @variable_name nvarchar(128)
 DECLARE @data_type nvarchar(128)
 DECLARE @sensitive bit
 DECLARE @value sql_variant
 DECLARE @value_tinyint tinyint
 DECLARE @description nvarchar(1024)
 DECLARE @nsql nvarchar(max)
 
 DECLARE @object_type smallint
 DECLARE @object_name nvarchar(260)
 DECLARE @value_type CHAR(1)
 DECLARE @parameter_name nvarchar(128)
 DECLARE @parameter_value sql_variant
 
 --    DECLARE @ProjectBinary as varbinary(max)
 DECLARE @operation_id as bigint
 DECLARE @ispacbinary as varbinary(max)
 --    DECLARE @ispacpath as nvarchar(500)
 --    DECLARE @servername as nvarchar(100)
 
 --------------------------------------------------------------------------------
 --Setting the variables
 --------------------------------------------------------------------------------
 --SET @folder_name = N'SSISBIFolder5'
 --SET @environment_name = N'DEV'
 --SET @environment_description = N'Development environment'
 --SET @project_name=N'SSISBI'
 --SET @ispacpath = 'D:\SSISProject\.....\SSISBI.ispac'
 
 IF  @folder_name IS NULL OR 
  @environment_name IS NULL OR
  @environment_description IS NULL OR
  @project_name IS NULL OR
  @ispacpath IS NULL OR
  @projectparam1 IS NULL
 BEGIN
  Print 'One (or more) parameters is/are empty!! Aborting'
  RETURN
 END
 --SET @SQLservername = CONVERT(sysname, SERVERPROPERTY('servername'))
 
 --------------------------------------------------------------------------------
 -- Create Folder
 --------------------------------------------------------------------------------
 PRINT '--------------------------------------------------------------------------------'
 PRINT 'Creating folder ' + @folder_name
 PRINT '--------------------------------------------------------------------------------'
 
 IF NOT EXISTS (
  SELECT 1
  FROM [SSISDB].[internal].[folders]
  WHERE [name] = @folder_name
  )
 BEGIN
  SET @nsql = N'[SSISDB].[catalog].[create_folder] N''' +  @folder_name + ''';'
  PRINT @nsql;
  EXEC sp_executesql @nsql;
 END
 ELSE
 BEGIN
  PRINT 'Not able to (re) create folder ' + @folder_name  + ' because it already exists'
 END
 PRINT CHAR(13)

 --------------------------------------------------------------------------------
 --Create the environment and add the variables.
 --------------------------------------------------------------------------------
 PRINT '--------------------------------------------------------------------------------'
 PRINT 'Creating Environment ' + @environment_name  + ' in the folder ' + @folder_name
 PRINT '--------------------------------------------------------------------------------'
 
 IF NOT EXISTS (
  SELECT 1
  FROM [SSISDB].[internal].[environments] E
  INNER JOIN [SSISDB].[internal].[folders] F ON E.folder_id = F.folder_id
  WHERE [environment_name] = @environment_name
  AND F.[name] = @folder_name
  )
 BEGIN
  SET @nsql = N'[SSISDB].[catalog].[create_environment] N''' + @folder_name + ''',N''' +
               @environment_name + ''',N''' +  @environment_description + ''';'
  PRINT @nsql;
  EXEC sp_executesql @nsql;
 END
 ELSE
 BEGIN
  PRINT 'Not able to (re) create Environment ' + @environment_name  + ' because it already exists in the folder ' + @folder_name
 END
 PRINT CHAR(13)

 --------------------------------------------------------------------------------
 -- Delete and add the environment variables to the Environment
 --------------------------------------------------------------------------------
 PRINT '--------------------------------------------------------------------------------'
 PRINT 'Deleting and adding Environmentvariables in ' + @environment_name
 PRINT '--------------------------------------------------------------------------------'
 
 INSERT INTO @environment_variables_table
  ( [variable_name], [data_type], [sensitive], [value], [description] )
 VALUES
    ( N'envMartConnectionString', N'String', 0, N'Data Source=' + @SQLservername + ';Initial Catalog=Mart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connection string for Mart')
  , ( N'envMetaConnectionString', N'String', 0, N'Data Source=' + @SQLservername  + ';Initial Catalog=rMeta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connection string for Meta' )
  , ( N'envStagingConnectionString', N'String', 0, N'Data Source=' + @SQLservername  + ';Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connection string for Staging' )
  , ( N'envSourceConnectionString', N'String', 0, N'USER=<user> <Somevariables> <HOST>=' + @projectparam1 + ' PASSWD=!@#@#!@%%$%', N'Connection string for source' )
            
 SELECT @id = 1, @max_id = MAX([id]) FROM @environment_variables_table
 WHILE @id <= @max_id
 BEGIN
  SELECT
   @variable_name = v.variable_name,
   @data_type = v.data_type,
   @sensitive = v.sensitive,
   @value = v.value,
   @description = v.[description]
  FROM @environment_variables_table v
  WHERE [id] = @id;
 
  IF EXISTS (
   SELECT 1
   FROM [SSISDB].[internal].[environment_variables] V
   INNER JOIN [SSISDB].[internal].[environments] E ON E.environment_id = V.environment_id
   INNER JOIN [SSISDB].[internal].[folders] F ON E.folder_id = F.folder_id
   WHERE V.[name] = @variable_name
   AND E.environment_name = @environment_name
   AND F.[name] = @folder_name
   )
  BEGIN                      
   SET @nsql = N'EXECUTE [SSISDB].[catalog].[delete_environment_variable] '
    + N'@folder_name = N'''+ @folder_name + ''', @environment_name = N''' + @environment_name + ''', '
    + N'@variable_name = N''' + @variable_name + ''''
   PRINT @nsql;
   EXEC sp_executesql @nsql;
  END
 
  --PRINT '/*'
  --PRINT @variable_name
  --PRINT CONVERT(nvarchar(128), SQL_VARIANT_PROPERTY(@value, 'BaseType'));
  --PRINT '*/'
 
  SET @nsql = N'EXECUTE [SSISDB].[catalog].[create_environment_variable] '
   + N'@folder_name = N'''+ @folder_name + ''', @environment_name = N''' + @environment_name + ''', '
   + N'@variable_name = N'''+ @variable_name + ''', @data_type = N''' + @data_type + ''', '
   + N'@sensitive = ' + CONVERT(NVARCHAR, @sensitive) + ', @description = N''' + @description + ''', '
   + CHAR(13) + CHAR(10) + N'@value = ' +
   CASE UPPER(@data_type)
   WHEN 'String' THEN 'N''' + CONVERT(NVARCHAR(1000), @value) + ''' '
   ELSE CONVERT(NVARCHAR(1000), @value)
   END + '; '
  PRINT @nsql;
  EXEC sp_executesql @nsql;
 
  SET @id = @id + 1
 END
 PRINT CHAR(13)
 
 --------------------------------------------------------------------------------
 -- Deploy the SSIS project (ispac) in the SSIS Catalog
 --------------------------------------------------------------------------------
 PRINT '--------------------------------------------------------------------------------'
 PRINT 'Deploy the ispac of the SSIS project in the SSIS Catalog '
 PRINT '--------------------------------------------------------------------------------'
 
 SET @nsql = 'SET @ispacBinary = (SELECT * FROM OPENROWSET(BULK N''' + @ispacpath + ''', SINGLE_BLOB) as BinaryData)'
 print @nsql
 EXEC SP_EXECUTESQL
   @Query  = @nsql
  , @Params = N'@ispacBinary varbinary(max) OUTPUT'
  , @ispacBinary = @ispacBinary OUTPUT
 
 SET @nsql = N'EXECUTE [SSISDB].[catalog].[deploy_project] '
    + N'@folder_name = N'''+ @folder_name + ''', @project_name = N''' + @project_name + ''', '
    + N'@Project_Stream = @ispacBinary'
 PRINT @nsql;
 EXEC sp_executesql @nsql, N'@ispacBinary varbinary(MAX)', @ispacBinary = @ispacBinary;
 PRINT CHAR(13)
     
 --------------------------------------------------------------------------------
 --Create the environment and add the variables.
 --------------------------------------------------------------------------------
 PRINT '--------------------------------------------------------------------------------'
 PRINT 'Creating an environmentreference  in ' + @environment_name
 PRINT '--------------------------------------------------------------------------------'
 Declare @reference_id bigint
 IF NOT EXISTS(SELECT 1
   FROM [SSISDB].[internal].[environment_references] R
   INNER JOIN [SSISDB].[internal].[projects] P ON P.project_id = R.project_id
   INNER JOIN [SSISDB].[internal].[folders] F ON P.folder_id = F.folder_id
   WHERE P.name = @project_name
   AND R.environment_name = @environment_name
   AND F.[name] = @folder_name
  )
 BEGIN
  SET @nsql = N'EXECUTE [SSISDB].[catalog].[create_environment_reference] '
   + N'@environment_name = N''' + @environment_name + ''', '
   + N'@reference_id = @reference_id , @project_name = N''' + @project_name + ''', '
   + N'@folder_name = N''' + @folder_name + ''', @reference_type = N''' + 'R' +''''
  PRINT @nsql;
  EXEC sp_executesql @nsql, N'@reference_id bigint', @reference_id = @reference_id;
 END
 PRINT CHAR(13)
 
 --------------------------------------------------------------------------------
 -- Connect the environment variables with the project parameters
 --------------------------------------------------------------------------------
 PRINT '--------------------------------------------------------------------------------'
 PRINT 'Connect the environment variables with the project parameters'
 PRINT '--------------------------------------------------------------------------------'
 INSERT INTO @object_parameter_value_table (
  [object_type],
  [object_name],
  [parameter_name],
  [parameter_value],
  [value_type]
 )
 VALUES
   ( 20, N'', N'MartConnectionString', N'envMartConnectionString', 'R')
 , ( 20, N'', N'MetaConnectionString', N'envMetaConnectionString', 'R')
 , ( 20, N'', N'StagingConnectionString', N'envStagingConnectionString', 'R')
 , ( 20, N'', N'SourceConnectionString', N'envSourceConnectionString', 'R')

 SELECT @id = 1, @max_id = MAX([id]) FROM @object_parameter_value_table
 WHILE @id <= @max_id
 BEGIN
  SELECT
   @object_type = v.[object_type],
   @object_name = v.[object_name],
   @parameter_name = v.[parameter_name],
   @parameter_value = v.[parameter_value],
   @value_type = v.[value_type]
  FROM @object_parameter_value_table v
  WHERE [id] = @id;
 
  SET @value_type = 'R'
  --SET @parameter_value = @parameter_name;
  
  SET @nsql = N'EXECUTE [SSISDB].[catalog].[set_object_parameter_value]'
   + N'@object_type = ''' + CAST(@object_type AS nvarchar(10)) + ''', '
   + N'@folder_name = N''' + @folder_name + ''', '
   + N'@project_name = N''' + @project_name + ''', '
   + N'@parameter_name = N''' + @parameter_name + ''', '
   + N'@parameter_value = N''' + CAST(@parameter_value AS Nvarchar(100)) + ''', '
   + N'@object_name = N''' + @object_name + ''', '
   + N'@value_type = N''' + @value_type + ''';'
 
  PRINT @nsql;
  EXEC sp_executesql @nsql, N'@reference_id bigint', @reference_id = @reference_id;
 
  SET @id = @id + 1
 END
 PRINT CHAR(13)
 PRINT '--------------------------------------------------------------------------------'
 PRINT 'Done'
 PRINT '--------------------------------------------------------------------------------'
END
 

Deploying a SSIS project to the SSIS Catalog

Now let's try this stored procedure and see what happens. Don't forget to make a note of the Name of the SSISProject. That name is needed as a parameter for the stored procedure.


The next step is to build the project. This will create a new ispac file and the location of the ispac file is used in the stored procedure. Store the location of the ispac file in notepad.

Creating a SSIS project in a SSISfolder

First let's show how the SSIS catalog looks like.



This is the execution code of the Stored Procedure:

USE [TestSSISProjects]
GO

DECLARE @RC int
DECLARE @folder_name sysname
DECLARE @environment_name nvarchar(128)
DECLARE @environment_description nvarchar(128)
DECLARE @project_name nvarchar(128)
DECLARE @ispacpath nvarchar(500)
DECLARE @SQLServerName nvarchar(100)
DECLARE @projectparam1 nvarchar(100)

-- Create a SSIS project in SSISFolder1
SET @folder_name =  'SSISFolder1'
SET @environment_name =   'DEV'
SET @environment_description = 'Development'
SET @project_name =  'TestSSISProject'
SET @ispacpath = 'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject.ispac'
SET @SQLServerName = NULL
SET @projectparam1 = 'SourceServer'

EXECUTE @RC = [dbo].[usp_CreateAndDeploySSISProject] 
   @folder_name
  ,@environment_name
  ,@environment_description
  ,@project_name
  ,@ispacpath
  ,@SQLServerName
  ,@projectparam1
GO 
 

Executing this piece of code results in the following log from the stored procedure.

     --------------------------------------------------------------------------------
Creating folder SSISFolder1
--------------------------------------------------------------------------------
[SSISDB].[catalog].[create_folder] N'SSISFolder1';


--------------------------------------------------------------------------------
Creating Environment DEV in the folder SSISFolder1
--------------------------------------------------------------------------------
[SSISDB].[catalog].[create_environment] N'SSISFolder1',N'DEV',N'Development';


--------------------------------------------------------------------------------
Deleting and adding Environmentvariables in DEV
--------------------------------------------------------------------------------

(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMartConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Mart', 
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Mart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ; 
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMetaConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Meta', 
@value = N'Data Source=SQL2017CTP21;Initial Catalog=rMeta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ; 
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envStagingConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Staging', 
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ; 
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envSourceConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for source', 
@value = N'USER=<user> <Somevariables> <HOST>=SourceServer PASSWD=!@#@#!@%%$%' ; 


--------------------------------------------------------------------------------
Deploy the ispac of the SSIS project in the SSIS Catalog 
--------------------------------------------------------------------------------
SET @ispacBinary = (SELECT * FROM OPENROWSET(BULK N'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject.ispac', SINGLE_BLOB) as BinaryData)
EXECUTE [SSISDB].[catalog].[deploy_project] @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject', @Project_Stream = @ispacBinary


--------------------------------------------------------------------------------
Creating an environmentreference  in DEV
--------------------------------------------------------------------------------
EXECUTE [SSISDB].[catalog].[create_environment_reference] @environment_name = N'DEV', @reference_id = @reference_id , @project_name = N'TestSSISProject', @folder_name = N'SSISFolder1', @reference_type = N'R'


--------------------------------------------------------------------------------
Connect the environment variables with the project parameters
--------------------------------------------------------------------------------

(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject', @parameter_name = N'MartConnectionString', @parameter_value = N'envMartConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject', @parameter_name = N'MetaConnectionString', @parameter_value = N'envMetaConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject', @parameter_name = N'StagingConnectionString', @parameter_value = N'envStagingConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject', @parameter_name = N'SourceConnectionString', @parameter_value = N'envSourceConnectionString', @object_name = N'', @value_type = N'R';


--------------------------------------------------------------------------------
Done
--------------------------------------------------------------------------------


And now a SSIS Catalog folder with a SSIS project and an environment is created


And now I can execute the SSIS package(s) with the environment variables of the SSISCatalog :



Adding a SSIS project to an existing folder
Now I can add another SSIS project in the SSIS Catalog folder. Let's try that. Don't forget to rename the project (in this testsituation) and execute a build.


Execute the following code:


DECLARE @RC int
DECLARE @folder_name sysname
DECLARE @environment_name nvarchar(128)
DECLARE @environment_description nvarchar(128)
DECLARE @project_name nvarchar(128)
DECLARE @ispacpath nvarchar(500)
DECLARE @SQLServerName nvarchar(100)
DECLARE @projectparam1 nvarchar(100)

SET @folder_name =  'SSISFolder1'
SET @environment_name =   'DEV'
SET @environment_description = 'Development'
SET @project_name =  'TestSSISProject2'
SET @ispacpath = 'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject2.ispac'
SET @SQLServerName = NULL
SET @projectparam1 = 'SourceServer'

EXECUTE @RC = [dbo].[usp_CreateAndDeploySSISProject] 
   @folder_name
  ,@environment_name
  ,@environment_description
  ,@project_name
  ,@ispacpath
  ,@SQLServerName
  ,@projectparam1
GO    
 

Here is the logging again:

--------------------------------------------------------------------------------
Creating folder SSISFolder1
--------------------------------------------------------------------------------
Not able to (re) create folder SSISFolder1 because it already exists


--------------------------------------------------------------------------------
Creating Environment DEV in the folder SSISFolder1
--------------------------------------------------------------------------------
Not able to (re) create Environment DEV because it already exists in the folder SSISFolder1


--------------------------------------------------------------------------------
Deleting and adding Environmentvariables in DEV
--------------------------------------------------------------------------------

(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[delete_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMartConnectionString'
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMartConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Mart', 
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Mart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ; 
EXECUTE [SSISDB].[catalog].[delete_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMetaConnectionString'
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMetaConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Meta', 
@value = N'Data Source=SQL2017CTP21;Initial Catalog=rMeta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ; 
EXECUTE [SSISDB].[catalog].[delete_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envStagingConnectionString'
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envStagingConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Staging', 
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ; 
EXECUTE [SSISDB].[catalog].[delete_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envSourceConnectionString'
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envSourceConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for source', 
@value = N'USER=<user> <Somevariables> <HOST>=SourceServer PASSWD=!@#@#!@%%$%' ; 


--------------------------------------------------------------------------------
Deploy the ispac of the SSIS project in the SSIS Catalog 
--------------------------------------------------------------------------------
SET @ispacBinary = (SELECT * FROM OPENROWSET(BULK N'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject2.ispac', SINGLE_BLOB) as BinaryData)
EXECUTE [SSISDB].[catalog].[deploy_project] @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @Project_Stream = @ispacBinary


--------------------------------------------------------------------------------
Creating an environmentreference  in DEV
--------------------------------------------------------------------------------
EXECUTE [SSISDB].[catalog].[create_environment_reference] @environment_name = N'DEV', @reference_id = @reference_id , @project_name = N'TestSSISProject2', @folder_name = N'SSISFolder1', @reference_type = N'R'


--------------------------------------------------------------------------------
Connect the environment variables with the project parameters
--------------------------------------------------------------------------------

(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'MartConnectionString', @parameter_value = N'envMartConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'MetaConnectionString', @parameter_value = N'envMetaConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'StagingConnectionString', @parameter_value = N'envStagingConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'SourceConnectionString', @parameter_value = N'envSourceConnectionString', @object_name = N'', @value_type = N'R';


--------------------------------------------------------------------------------
Done
--------------------------------------------------------------------------------

 

And now a new project is placed besides the other project in the SISS folder in the SSIS catalog.



Adding an environment to an existing SSIS folder
It is also possible to add an extra enviroment in the SSIS folder in the SSIS catalog. In this case the same parameter values are used but off course it is a small step to make this flexible.


       
DECLARE @RC int
DECLARE @folder_name sysname
DECLARE @environment_name nvarchar(128)
DECLARE @environment_description nvarchar(128)
DECLARE @project_name nvarchar(128)
DECLARE @ispacpath nvarchar(500)
DECLARE @SQLServerName nvarchar(100)
DECLARE @projectparam1 nvarchar(100)

SET @folder_name =  'SSISFolder1'
SET @environment_name =   'TST'
SET @environment_description = 'Test'
SET @project_name =  'TestSSISProject2'
SET @ispacpath = 'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject2.ispac'
SET @SQLServerName = NULL
SET @projectparam1 = 'SourceServer'

EXECUTE @RC = [dbo].[usp_CreateAndDeploySSISProject] 
   @folder_name
  ,@environment_name
  ,@environment_description
  ,@project_name
  ,@ispacpath
  ,@SQLServerName
  ,@projectparam1
GO
 

Resulting in the following log information :

--------------------------------------------------------------------------------
Creating folder SSISFolder1
--------------------------------------------------------------------------------
Not able to (re) create folder SSISFolder1 because it already exists


--------------------------------------------------------------------------------
Creating Environment TST in the folder SSISFolder1
--------------------------------------------------------------------------------
[SSISDB].[catalog].[create_environment] N'SSISFolder1',N'TST',N'Test';


--------------------------------------------------------------------------------
Deleting and adding Environmentvariables in TST
--------------------------------------------------------------------------------

(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'TST', @variable_name = N'envMartConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Mart', 
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Mart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ; 
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'TST', @variable_name = N'envMetaConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Meta', 
@value = N'Data Source=SQL2017CTP21;Initial Catalog=rMeta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ; 
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'TST', @variable_name = N'envStagingConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Staging', 
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ; 
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'TST', @variable_name = N'envSourceConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for source', 
@value = N'USER=<user> <Somevariables> <HOST>=SourceServer PASSWD=!@#@#!@%%$%' ; 


--------------------------------------------------------------------------------
Deploy the ispac of the SSIS project in the SSIS Catalog 
--------------------------------------------------------------------------------
SET @ispacBinary = (SELECT * FROM OPENROWSET(BULK N'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject2.ispac', SINGLE_BLOB) as BinaryData)
EXECUTE [SSISDB].[catalog].[deploy_project] @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @Project_Stream = @ispacBinary


--------------------------------------------------------------------------------
Creating an environmentreference  in TST
--------------------------------------------------------------------------------
EXECUTE [SSISDB].[catalog].[create_environment_reference] @environment_name = N'TST', @reference_id = @reference_id , @project_name = N'TestSSISProject2', @folder_name = N'SSISFolder1', @reference_type = N'R'


--------------------------------------------------------------------------------
Connect the environment variables with the project parameters
--------------------------------------------------------------------------------

(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'MartConnectionString', @parameter_value = N'envMartConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'MetaConnectionString', @parameter_value = N'envMetaConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'StagingConnectionString', @parameter_value = N'envStagingConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'SourceConnectionString', @parameter_value = N'envSourceConnectionString', @object_name = N'', @value_type = N'R';


--------------------------------------------------------------------------------
Done
--------------------------------------------------------------------------------
 
 

And this is how it looks like in the SSIS catalog :




Deleting a SSISfolder with all of its projects and environments (Blogpost for later)
Last step of this blogpost is to clean up the SSIS catalog. For this reason I've created another stored procedure : usp_DropSSISProject. This is not described in this blog post

USE [TestSSISProjects]
GO

DECLARE @RC int
DECLARE @parfolder_name sysname
DECLARE @parenvironment_name nvarchar(128)
DECLARE @parproject_name nvarchar(128)
DECLARE @pardeleteall nchar(1)

SET @parfolder_name = 'SSISFolder1'
SET @parenvironment_name = NULL
SET @parproject_name  = NULL
SET @pardeleteall = 'Y'

EXECUTE @RC = [dbo].[usp_DropSSISProject] 
   @parfolder_name
  ,@parenvironment_name
  ,@parproject_name
  ,@pardeleteall
GO

And executing the code will result in the following log information:

 --------------------------------------------------------------------------------
Delete all Project (s) in SSISFolder1
--------------------------------------------------------------------------------

(2 row(s) affected)
[SSISDB].[catalog].[delete_project] @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject';
[SSISDB].[catalog].[delete_project] @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2';


--------------------------------------------------------------------------------
Delete Environment (s) in SSISFolder1
--------------------------------------------------------------------------------

(2 row(s) affected)
[SSISDB].[catalog].[delete_environment] @folder_name = N'SSISFolder1', @environment_name = N'DEV';
[SSISDB].[catalog].[delete_environment] @folder_name = N'SSISFolder1', @environment_name = N'TST';


--------------------------------------------------------------------------------
Deleting folder SSISFolder1
--------------------------------------------------------------------------------
[SSISDB].[catalog].[delete_folder] N'SSISFolder1';

The SSIS Catalog is empty now


Conclusion

This blogpsot describes the implementation of a stored procedure that creates folders, projects, environments and environment variables in an almost automated way.

Hennie



woensdag 31 mei 2017

Error during install SQL Server 2017 CTP 2.1

I tried to install the CTP version 2.1 of SQL Server 2017 and ran into an error when starting the management studio.

FileMgr::StartLogFiles: Operating system error 2
(The system cannot find the file specified.) 
occurred while creating or opening file 
'd:\dbs\sh\sprel\0510_114453\cmd\31\obj\x64retail\sql\mkmastr\databases\
mkmastr.proj\modellog.ldf'.
Diagnose and correct the operating system error, and retry the operation.

I  don't what I did or how but it seems a glitch in the CTP version. The only thing I can think of is that I changed the data and log folder to the D Drive. I selected all the options in the installation process.

I solved it by creating the required path and copying the Master, Model, tempdb and msdb databases and copy to this location d:\dbs\sh\sprel\0510_114453\cmd\31\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\

I had some problems with security of the folders and files :

 FCB::Open failed: Could not open file 
 d:\dbs\sh\sprel\0510_114453\cmd\31\obj\x64retail\sql\mkmastr\databases\
 mkmastr.proj\model.mdf for file number 1.  
 OS error: 5(Access is denied.).

But this is easily solved by adding the MSSQLSERVER account to the folders security and propagate this to this folders beneath it.


Next problem was that the administrator login/user was not present in SQL Server. I managed to solve that by executing the following steps: Stopping/Starting the SQL Server process (net stop mssqlserver) and run it in single user mode (net start mssqlserver /m), add the administrator account and give it sysadmin rights and then stop and start the SQL Server with normal settings(net start mssqlserver).

Hennie

dinsdag 30 mei 2017

Setting up Visual Studio Team Services

Introduction

When a team of developers grows larger and larger it is a best practice to introduce Version Control in the project. Version control has some advantages :
  • A controlled way of working together on the same code.
  • Storing versions in order to understand the differences and to understand what happened during development of the code.
  • To see how much work a subproject/sprint was during evaluation.
  • As a backup for the code (in a former blogpost I described a way to automatically backup the project)
  • Creating multiple branches in the code in order to distinguish subprojects, Development and production code (eg. for hotfixes).

In this blogpost the following steps are described:
  • Step 1 : Create a sample project in Visual Studio
  • Step 2 : Create a Team Project
  • Step 3 : Connect to the Team project environment in Visual Studio.
  • Step 4:  Setup a workspace on your local system.
  • Step 5:  Add the project to Source control / Team project
  • Step 6:  Do some changes in the local VS project.
  • Step 7:  Check the online Team foundation Server environment.

Step 1: Create a sample project

First, lets make a solution with some examples in a Visual Studio project. The solution I've created for this blogpost includes a SQLProject, a SSISProject and a Tabular Project.



Step 2: Create a Team Project

The next step is to go to the VisualStudio.com and to create a new team project with New team project. 


Give the Team project a proper project name, give the project a Description, choose the type of version control and choose the type of project type with Work Item process. Choose Create.



Now, the project is created in Team Projects and an overview is given about the project.


Step 3:  Connect to the Team Project environment

The next step is to find the Team Explorer in Visual Studio, and there are several options to choose from. In this case, select the Connect link of the Visual Studio Team Services.




The window "Connect to Team Foundation Server" appears and now the url is needed of name of the Visual Studio Team services account. Select the option "Servers".



Press the Servers button and Add a new server. Login in with your account and press sign in



The following window appears


Connecting with the Visual Studio Team Services and select the Team projects.


Step 4: Setup a workspace

In Visual Studio it is needed that there is a local workspace to work with. This is a local copy of the code of the central repository. Click on Configure Workspace.


Next step is to Map & Get the local workspace with the central repository


When the Map&Get is successfully a message is presented : "The workspace was mapped successfully"

Step 5: Add the project to Source control / Team Projects

Now the workspace and the central repository is connected and now it 's time to add the local solution to the Visual Studio Team Services by clicking on Add Solution to Source Control.


Now, it is important to set the location in Visual Studio Team Services. Press OK when  this is done


Now the projects are marked with a + sign indicating that the files are ready to check in Visual Studio Team Services.


Right click on the solution and click on Check in to upload the code the repository.


Add some comment, check if everything is ok and press Check in


A confirmation window appears and click on Yes.


A confirmation is shown to the usr that the code is successfully checked in


Step 6: Do some changes in the local VS project

Now, let's see what happens when something changes in the code. In the initial situation the code is locked and check in.


For instance let's add a SSIS package to the SSIS project.


Two packages were added to the local Workspace. You can see this below Package1 and Package2.


Because the SSIS Packages are added to the project(file), the project (file) is also marked as changed and therefore this should be uploaded to the repository too.


Select the Project and click on the Source Control option.


An overview is shown of the new and changed packages. Click on Check in.


Step 7: The check the online Visual Studio Team Services environment

The last step is checking the online Visual Studio Team Services environment where the code is now stored.



Conclusion

In this blogpost I've described the setup of Visual Studio and Visual Studio Team Services working together such that version control of your BI project is enhanced.

zondag 14 mei 2017

Getting filtered data from a tabular with Excel

Introduction

Building a PowerPivot model on filtered data of a table from a SQL Database is fairly simple. In the table properties you can change the table view to query view and change the query. I haven't found a similar way when using a tabular with Excel (realtime connection).

The only option I have found so far is using the CommandText in the connection properties, but that changes my look and feel of browsing the cube. The retrieved data from the tabular is just like one plain table. This blogpost will describe the way of adjusting the .odc file in Excel and getting the data as a plain table.

The goal of this blogpost is to show how to filter the factdata based on a dimension value (DimCustomer).

The Datamodel

I've created a simple model in SQL Server and imported it into the Tabular project. It is modeled as follows.


One Facttable with a key to the DimCustomer table and a measure Amount. I added a few records.

Some simple EVALUATES

In SSMS, I experimented with some EVALUATES to get some data from the Tabular. Below the result of this exercise.

--Getting the data from a table
EVALUATE Fact

--A simple  query (and join between tables)
EVALUATE
SUMMARIZE (Fact
,DimCustomer[Name]
,"TotalAmount", SUM(Fact[Amount])
)

--A simple query and a calculated measure is used
EVALUATE
SUMMARIZE (Fact
,DimCustomer[Name]
,"TotalAmount", Fact[TotalAmount]
)

--A calculated measure and a Filter
EVALUATE
FILTER(
 SUMMARIZE (Fact
 ,DimCustomer[Name]
 ,"TotalAmount", Fact[TotalAmount]
 ),
 DimCustomer[Name] = "Hennie"
)

Adjusting the .odc file

From the experiments in SSMS i took one of the DAX Evaluates and inserted that in the CommandText tag in the odc file of Excel.



This is the result in the Connection properties in the Excel file. The CommandType combobox is changed to Default and in the CommandText box a DAX EVALUATE expression is presented.

The result in Excel

And below you can see the fields in the FieldList and as you can see the Look and Feel has changed of the FieldList. 



Conclusion

This blogpost is about how to filter the data in Excel from a Tabular Model.


Best regards,

Hennie



woensdag 3 mei 2017

DAX : Selecting a measure with a Slicer

Introduction

Someone asked me a question about selecting a measure in Excel with PowerPivot/Tabular and I founded a blog about selecting a measure written by James Thomas. I have made my own example and wrote this blogpost, about it. In this blogpost I'll describe the following steps:
  • Create some sample data.
  • Create a helper table.
  • Create the datamodel.
  • Create the measures.
  • Create a pivottable and add the properfields to the pivottable.
  • Testing the result.

The source data

I've created three tables: DimCustomer, FactSales and a Measure table.


I added these to the PowerPivot model and the datamodel looks like the following screenshot:



The DAX Expressions

I've created the following DAX Expression (the + 2, etc is for getting some different testdata)

MeasureA:=SUM([Amount]) +2      
MeasureB:=SUM([Amount]) + 3
MeasureC:=SUM([Amount]) +8
MeasureD:=SUM([Amount]) +4
       
MeasureValue:=switch (TRUE;
Min ('Measure'[MeasureID] ) = 1;
FactSales[MeasureA];
Min ('Measure'[MeasureID] ) = 2;
FactSales[MeasureB];
Min ('Measure'[MeasureID] ) = 3;
FactSales[MeasureC];
Min ('Measure'[MeasureID] ) = 4;
FactSales[MeasureD])

Configure the Pivottable

This is how the pivottable is setup. The MeasureName is added to the rows and the MeasureValue measure is in the VALUES area.


The result

Here is the result of the solution. Here you can see that the slicerbutton MeasureA and MeasureC selects only the measure A and C and that is exactly what I want.


Yet, another example of selection with the slicerbutton:


Here you can see the different measures according to the selected slicer buttons.

Conclusion

This blogpost is about selecting the proper measures with a slicer.


Greetz,
Hennie

zondag 30 april 2017

Powershell : Backing up your project

Introduction

In a project where people work together in a team it is advisable to use Team Foundation Server for tracking and storing your Visual Studio project. When I start a project or working on my own on a project, I normally make a backup of the Visual Studio project every day or couple of days. In this blogpost I describe a way to automate the backup of a Visual Studio project by zipping it, tagging it, storing it in a backup folder, send a email when it succeeded or when it failed.

The Script

Below an example of the Powershell script that executes a couple of steps:
  1. If the same zipped backup file exists remove it.
  2. Try to compress the project. 
  3. Copy the file to the backup folder.
  4. Send an email when it succeeds or when it fails.

#########################################
# Author : Hennie de Nooijer
# Description : Script copies the folder, make a zip with a date and time tag.
# Date : 20170430
#########################################
$source = "F:\Project"
$dDateTime = 
$strDate = Get-Date -format yyyyMMdd
$strTime = Get-Date -format HHmmss
 
$destination = "F:\Backup\Project_" + $strDate + "_" + $strTime +  ".zip"
 
If(Test-path $destination) {Remove-item $destination}
 
Add-Type -assembly "system.io.compression.filesystem"
Try 
{
    [io.compression.zipfile]::CreateFromDirectory($Source, $destination) 
    Send-MailMessage -From <putyouremailhere> 
                  -To <putyouremailhere> 
      -Subject "Project Backup Succeeded" 
      -SmtpServer  <SMTPServerHere> 
}
Catch
{
    Remove-Item $destination
    $ErrorMessage = $_.Exception.Message
    Send-MailMessage -From <putyouremailhere> 
                  -To <putyouremailhere> 
      -Subject "Project Backup Failed" 
      -SmtpServer <SMTPServerHere> 
      -Body "The error message was $ErrorMessage"
    Break
}

This is the result if you execute the script a couple of times (without _).


Schedule a windows task

In order to automate the backup of the project, a task in windows is needed. To do that, the Computer Management tool is used. There you can setup windows tasks. Below, the steps are explained step by step.

Give the task a appropriate name and press Next.


Select the frequency of the windows task. In this example we choose Daily. Press Next.


Next step is the time of execution. Select the Start moment and by how many days the task should be recurring. Press Next.


Choose start a program and press Next.


The following step is an important step. Here you enter the command that executes the script. The command is Powershell and the parameters -file "D:\ps1\BackupProject.ps1". Press Next.


Here is the script again.

       
powershell -file "D:\ps1\BackupProject.ps1"
        

A warning appears and you can press on Yes.


Finally the task is ready and now press Next.


The task below is shown and is ready or usage.


With the context menu it is possible to run the task on an ad hoc manner..



 And  you can even schedule a task every five minutes ;-) every 5 minutes.



Conclusion

This blogpost describes a Powershell script that automates zips and backups of a project.

Greetz,

Hennie