donderdag 8 februari 2018

Azure series : Setup an alert for billing in Azure

Introduction

I am working with and trying some tooling in Azure, for instance the Azure Data Factory V1 and V2,  Azure SQL DW, Automation, the Azure Blob Storage. Sometimes it can be cumbersome to keep up with the costs of usage of the different services in Azure.

I found an useful tool to monitor the costs of Azure in the Azure billing portal: Alerts. It is in preview. You can do that at : https://account.azure.com.

Alert

Choose for subscriptions in the Billing portal.




Click on the right subscription to set the alert.


Next step is to choose for ALERTS.



And now set the alert properties


Conclusion

Alerts can be very useful for monitoring your Azure costs.

Hennie

zondag 10 september 2017

Azure : Setting up a SQL DW (MPP)

Introduction 

In this blogpost I'll describe a walkthrough of setting up of a SQL DW in Azure. This is the MPP solution of Microsoft in the cloud. As you will see in this blogpost setting up a SQL DW is very easy in Azure.

Settung up the SQL DW


1. Sign up for the SQL DW in Azure.

2. Click New, choose database and then SQL Data warehouse create.


3. The next step is configuring the database and the Server.


4. And don't forget to select Select when you configure the Server. When all is setup correctly press Create. It will take some time to create the SQL Data warehouse. Wait for the notification of successful deployment. 


5. Now next step is adding your client pc you're working on to the Firewall settings



Connecting to the SQL DW from my local VM

6. Next step is spinning up a local VM and try to connect to the SQLDW instance


7. And this succeeds..



Conclusion

This is a blogpost about how to setup a SQLDW in the Azure. Setting up a basic SQL DW is very simple. Working with the client tools with Azure is also easy to achieve.

Hennie 



zaterdag 26 augustus 2017

Azure : Building a VM with SQL Server in Microsoft Azure

Introduction

In this blogpost I'll show you how to create a VM in Microsoft Azure. The purpose of this blogpost is describing the steps installing the VM. I'm doing this for an Edx course I'm currently following. Now what confuses me a bit is the option of installing a VM with SQL

Creating a VM with SQL Server 2016

First login into Microsoft Azure and click on Virtual Machines in the left blade.


Choose the Free license SQL Server 2016 SP1 Developer on Windows Server 2016.


In the next step it's possible to choose the deployment model. I choose to use the standard : Resource manager.


Now fill in some basic information about the VM.



Next step is to choose the size of the VM. There are different pricing models.



Next step is setting up the Storage, network information. I choose the cheaper option HDD. 


Now, the next step is is setting up the SQL Server settings.




And the last step contains an overview of all of the settings of the Virtual machine.



Scrolling down will show some more information


Creating the VM will take a 15 - 20 minutes.


And this is an overview of the settings of the VM.



Conclusion

This blogpost is about creating a VM with Microsoft Azure and spinning up a VM is very easy.


Hennie

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