Sunday 30 December 2012

SSIS wishlist for 2013

It's almost 2013. A good time to look back on the past year, but also a good time for my SSIS wishlist for 2013. It's only a top 10, because I don't want to look greedy. ;-)
  1. Package parts for reusability. Vote
  2. Folder options in Visual Studio for SSIS projects. Vote
  3. BI templates for the latest Visual Studio (want to look cool among my .Net colleagues). Vote and Vote
  4. Toolbox improvements (ordering and folders). Vote and Vote
  5. Rename refactoring option (try to rename an used variable). Vote
  6. Change column order of Flat File connection manager. Vote
  7. Validate without running option. Vote
  8. Expressions on Project Parameters. Vote
  9. XML Source using a connection manager. Vote
  10. Replicate option for server environments. Vote
 
Submit your own SSIS wishes in the comments or at Microsoft Connect.

Monday 17 December 2012

SSIS 2012 Copy Environments

Case
I created a new environment in the Integration Services Catalogs, but I can't copy it. If I want a duplicate / clone the enviroment then I have to create it from scratch with all the variables. Where is the copy option?
Where is the copy environment option?
















Solution
Well there isn't one yet (please see/vote this Microsoft Connect suggestion).  So I copied the create_environment stored procedure and altered it to a copy_environment stored procedure. You have to know the environment id from the environment you want to copy. Double click the existing environment and get the number from the Identifier property.
Get identifier of existing environment



















Add the new stored procedure and execute it like:
EXEC [catalog].[copy_environment] 3, 'Test', 'Test environment';

The result: a new environment with
same variables as 'Development'














PS you still have to reference the enviroment to your project manually:
Update: I adjusted the stored procedure and now it also links the new environment to the same project as the existing environment.
Reference the enviroment to your project
















The new stored procedure (use at own risk!):
USE [SSISDB]
GO

-- USE AT OWN RISK! This stored procedure is altered from create_environment that was shipped with:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
--      Oct 19 2012 13:38:57 
--      Copyright (c) Microsoft Corporation
--      Developer Edition (64-bit) on Windows NT 6.1 <x64> (Build 7601: Service Pack 1)

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [catalog].[copy_environment]
        --@folder_name        nvarchar(128),         -- Changed from parameter to variable
        @current_environment_id bigint,              -- Added to store the ID of the environment you want to copy
        @new_environment_name   nvarchar(128),         -- Renamed (added new_)
        @new_environment_description    nvarchar(1024)= NULL    -- Renamed (added new_)
WITH EXECUTE AS 'AllSchemaOwner'
AS
    SET NOCOUNT ON
    
    DECLARE @folder_id bigint
    DECLARE @folder_name        nvarchar(128)     -- Added (moved from parameter)
    DECLARE @new_environment_id bigint        -- Renamed (added new_)
    DECLARE @result bit
    
    
    DECLARE @caller_id     int
    DECLARE @caller_name   [internal].[adt_sname]
    DECLARE @caller_sid    [internal].[adt_sid]
    DECLARE @suser_name    [internal].[adt_sname]
    DECLARE @suser_sid     [internal].[adt_sid]
    
    EXECUTE AS CALLER
        EXEC [internal].[get_user_info]
            @caller_name OUTPUT,
            @caller_sid OUTPUT,
            @suser_name OUTPUT,
            @suser_sid OUTPUT,
            @caller_id OUTPUT;
          
          
        IF(
            EXISTS(SELECT [name]
                    FROM sys.server_principals
                    WHERE [sid] = @suser_sid AND [type] = 'S')  
            OR
            EXISTS(SELECT [name]
                    FROM sys.database_principals
                    WHERE ([sid] = @caller_sid AND [type] = 'S')) 
            )
        BEGIN
            RAISERROR(27123, 16, 1) WITH NOWAIT
            RETURN 1
        END
    REVERT
    
    IF(
            EXISTS(SELECT [name]
                    FROM sys.server_principals
                    WHERE [sid] = @suser_sid AND [type] = 'S')  
            OR
            EXISTS(SELECT [name]
                    FROM sys.database_principals
                    WHERE ([sid] = @caller_sid AND [type] = 'S')) 
            )
    BEGIN
            RAISERROR(27123, 16, 1) WITH NOWAIT
            RETURN 1
    END
    
    --IF (@folder_name IS NULL OR @environment_name IS NULL)  -- Changed, not checking the folder_name any more. Renamed @environment_name to @new_environment_name
 IF (@new_environment_name IS NULL)
    BEGIN
        RAISERROR(27138, 16 , 6) WITH NOWAIT 
        RETURN 1 
    END
    
    IF [internal].[is_valid_name](@new_environment_name) = 0
    BEGIN
        RAISERROR(27142, 16, 1, @new_environment_name ) WITH NOWAIT
        RETURN 1
    END
    
    
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    
    
    
    DECLARE @tran_count INT = @@TRANCOUNT;
    DECLARE @savepoint_name NCHAR(32);
    IF @tran_count > 0
    BEGIN
        SET @savepoint_name = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N'');
        SAVE TRANSACTION @savepoint_name;
    END
    ELSE
        BEGIN TRANSACTION;                                                                                          
    BEGIN TRY
    
        EXECUTE AS CALLER
            --SET @folder_id = (SELECT [folder_id] FROM [catalog].[folders] WHERE [name] = @folder_name)  -- Changed, getting the folder_id via the environment_id
   SET @folder_id = (SELECT [folder_id] FROM [internal].[environments] WHERE [environment_id] = @current_environment_id)
   SET @folder_name = (SELECT [name] FROM [catalog].[folders] WHERE [folder_id] = @folder_id)    -- Added, filling the folder_name which was a parameter
        REVERT
        
        IF @folder_id IS NULL
        BEGIN
            RAISERROR(27104 , 16 , 1, @folder_name) WITH NOWAIT
        END
        
        EXECUTE AS CALLER   
            SET @result =  [internal].[check_permission] 
                (
                    1,
                    @folder_id,
                    100
                 ) 
        REVERT
        
        IF @result = 0
        BEGIN
            RAISERROR(27209 , 16 , 1, @folder_name) WITH NOWAIT    
        END
         
        IF EXISTS(SELECT env.[environment_name] 
                      FROM [internal].[folders] fld INNER JOIN [internal].[environments] env
                      ON fld.[folder_id] = env.[folder_id] AND 
                      fld.[name] = @folder_name   AND 
                      env.[environment_name] = @new_environment_name)
        BEGIN
            RAISERROR(27157 , 16 , 1, @new_environment_name) WITH NOWAIT
        END
    
  -- CHANGED, old Insert query
        --INSERT INTO [internal].[environments] 
        --    VALUES (@environment_name, @folder_id, @environment_description, @caller_sid, @caller_name, SYSDATETIMEOFFSET())
        --    
        --SET @environment_id = SCOPE_IDENTITY()
       
        ------------------------------------------------------------------------------------------------------------
  -- NEW INSERT INTO QUERIES
  ------------------------------------------------------------------------------------------------------------
  -- Copy the environment
  INSERT INTO [internal].[environments]
  (
    [environment_name]
  ,  [folder_id]
  ,  [description]
  ,  [created_by_sid]
  ,  [created_by_name]
  ,  [created_time]
  )
  SELECT  @new_environment_name as [environment_name]  -- My new environment name from the parameter
  ,  [folder_id]
  ,  @new_environment_description as [description] -- My new environment description from the parameter
  ,  [created_by_sid]
  ,  [created_by_name]
  ,  SYSDATETIMEOFFSET() as [created_time]
  FROM [internal].[environments]
  WHERE [environment_id] = @current_environment_id   -- My existing environment from the parameter
  
  
  -- Get ID from the new environment
  SET @new_environment_id = SCOPE_IDENTITY()
  
  -- Copy the variables from the existing environment to the new environment
  INSERT INTO [internal].[environment_variables]
  (
    [environment_id]
  ,  [name]
  ,  [description]
  ,  [type]
  ,  [sensitive]
  ,  [value]
  ,  [sensitive_value]
  ,  [base_data_type]
  )
  SELECT @new_environment_id as [environment_id]   -- Id from the new environment
  ,  [name]
  ,  [description]
  ,  [type]
  ,  [sensitive]
  ,  [value]
  ,  [sensitive_value]
  ,  [base_data_type]
  FROM [internal].[environment_variables]
  WHERE [environment_id] = @current_environment_id   -- My existing environment from the parameter

  -- Reference this new environment to the same project
  INSERT INTO [internal].[environment_references]
  (
     [project_id]
  ,   [reference_type]
  ,   [environment_folder_name]
  ,   [environment_name]
  ,   [validation_status]
  ,   [last_validation_time]
  )
  SELECT  p.project_id
  ,   r.reference_type
  ,   r.environment_folder_name
  ,   @new_environment_name as environment_name -- My new environment name from the parameter
  ,   r.validation_status
  ,   r.last_validation_time
  FROM  [internal].[environment_references] as r
  INNER JOIN [internal].[projects] as p
     on r.project_id = p.project_id
  INNER JOIN [internal].[folders] as f
     on p.folder_id = f.folder_id
  INNER JOIN [internal].[environments] as e
     on e.folder_id = f.folder_id
     and e.environment_name = r.environment_name
  WHERE  e.environment_id = @current_environment_id -- My existing environment id from the parameter
  
  ------------------------------------------------------------------------------------------------------------
  -- END NEW INSERT INTO QUERIES
  ------------------------------------------------------------------------------------------------------------
        
        DECLARE @sqlString    nvarchar(1024)
        DECLARE @key_name               [internal].[adt_name]
        DECLARE @certificate_name       [internal].[adt_name]
        DECLARE @encryption_algorithm   nvarchar(255)
        
        SET @encryption_algorithm = (SELECT [internal].[get_encryption_algorithm]())
        
        IF @encryption_algorithm IS NULL
        BEGIN
            RAISERROR(27156, 16, 1, 'ENCRYPTION_ALGORITHM') WITH NOWAIT
        END
        
        
        SET @key_name = 'MS_Enckey_Env_'+CONVERT(varchar,@new_environment_id)
        SET @certificate_name = 'MS_Cert_Env_'+CONVERT(varchar,@new_environment_id)
        
        SET @sqlString = 'CREATE CERTIFICATE ' + @certificate_name + ' WITH SUBJECT = ''ISServerCertificate'''
        
        IF  NOT EXISTS (SELECT [name] FROM [sys].[certificates] WHERE [name] = @certificate_name)
            EXECUTE sp_executesql @sqlString 
        
        SET @sqlString = 'CREATE SYMMETRIC KEY ' + @key_name +' WITH ALGORITHM = ' 
                            + @encryption_algorithm + ' ENCRYPTION BY CERTIFICATE ' + @certificate_name
        
        IF  NOT EXISTS (SELECT [name] FROM [sys].[symmetric_keys] WHERE [name] = @key_name)
            EXECUTE sp_executesql @sqlString 
        
        
        DECLARE @retval int
        EXECUTE AS CALLER
            EXEC @retval = [internal].[init_object_permissions] 3, @new_environment_id, @caller_id
        REVERT
        IF @retval <> 0
        BEGIN
            
            RAISERROR(27153, 16, 1) WITH NOWAIT
        END    
         
        
        
        IF @tran_count = 0
            COMMIT TRANSACTION;                                                                                 
    END TRY

    BEGIN CATCH
        
        IF @tran_count = 0 
            ROLLBACK TRANSACTION;
        
        ELSE IF XACT_STATE() <> -1
            ROLLBACK TRANSACTION @savepoint_name;                                                                           
        
        THROW 

    END CATCH

    RETURN 0 


GO
Download as SQL file

NOTE: Please use at own risk and let me know it things could be improved!

Saturday 15 December 2012

DTAP package configurations - part 2

Case
I have a DTAP (Development, Test, Acceptance, Production). How do I use SSIS configurations over these multiple environments?

Solutions
The most commonly used solutions for configurating multiple environments are:
a) XML Configuration File
b) SQL Server Configuration (in combination with environment variable)
c) SSIS 2012 (in combination with the new project deployment)

In this post I will eleborate SQL Server Configuration solution. For this scenario I have a simple package with one Data Flow Task that has one CSV source and one SQL destination. Let's start by adding configurations for the two Connection Managers.
Simple Scenario



















1) Add configurations
Go to the SSIS menu and select Package Configurations... and then Enable package configurations. After that hit the Add button.
Enable package configurations




















2) Configuration Type
Select SQL Server in the Configuration type selectbox. Create a new Connection Managers that links to the database with your configuration table (1). Create a new Configuration table or select an existing one (2). Enter a name that will describe the configurations (3). After that click Next.
Configuration Type




















3) Select Properties to Export
In this step you select all the properties you want to configure in the config table. In our case we select the ConnectionString property of both Connection Managers (but not the one from the new connection manager that you created in the previous step!). After that click next.
Select the two ConnectionString properties






















4) Configuration Name
Give your configuration a suitable name that will explain its purpose if you have multiple configurations.
Configuration name



















5) Add Environment variable
We now have a third Connection Manager, but its connectionstring will be different on all servers in your dtap environment. So we need to configure that new Connection Manager as well. We will store its connectionstring in a Windows Environment Variable.
This step will vary for each Operating System. The screenshots are from my Windows 7 laptop. Go to the properties of "My Computer", then to Advanced system settings, then to the Advanced tab and then hit he Environment Variables button. Now add a new System variable with the name SsisConfigurationDatabase and the value must me the connectionstring of that new Connection Manager. Repeat this step for each ssis server in your dtap environment.
New System Variable


















6) Add second configuration
Before we add a second configuration, you probably need to restart Visual Studio because it doesn't know your new Windows Environment variable. Add a new configuration and select Environment variable as Configuration type. Then select your new variable named SsisConfigurationDatabase. And click Next.
Configuration type




















7) Select Target Property
Now select the connectionstring of the new connection manager and click Next.
Select the ConnectionString




















8) Configuration name and move up
Give your configuration a suitable name and move it upwards so that it will be the first configuration. After that close the window.
Move it upwards!






















9) Copy configuration database
Now copy the new configuration database table to all database servers in your dtap enviroment and make sure its values are adjusted to the server. Also make sure the environment variable is available on each server. Now you can deploy your package to each server and it will use its own configuration.

Friday 14 December 2012

DTAP package configurations - part 1

Case
I have a DTAP (Development, Test, Acceptance, Production). How do I use SSIS configurations over these multiple environments?

Solutions
The most commonly used solutions for configurating multiple environments are:
a) XML Configuration File
b) SQL Server Configuration (in combination with environment variable)
c) SSIS 2012 (in combination with the new project deployment)

In this post I will eleborate the XML Config file solution. For this scenario I have a simple package with one Data Flow Task that has one CSV source and one SQL destination. Let's start by adding configurations for the two Connection Managers.
Simple Scenario




















1) Add configurations
Go to the SSIS menu and select Package Configurations... and then Enable package configurations. After that hit the Add button.
Enable package configurations




















2) Configuration Type
Select XML configuration file (default value) in the Configuration type select box and enter a filepath for your config file. After that click Next.
Configuration Type





















3) Select Properties to Export
In this step you select all the properties you want to configure in the config file. In our case we select the ConnectionString property of both Connection Managers. After that click next.
Select both ConnectionString properties




















4) Configuration Name
Give your configuration a suitable name that will explain its purpose if you have multiple configurations. After that hit the Finish button and close the configuration window.
Configuration name






















5) View the file
If you browse to your config file and open it in for example Internet Explorer then you can see what's in it. Now you can just copy the xml config file to all environments. The path to the config file is embedded in the packages. So if you keep the file path the same, then you can just copy the packages and config files between environments. You can edit the config file in notepad to change the values for each environment.









6) Deployment Manifest
If you use different paths to store the config files then you should use the Deployment Manifest to change the embedded file path of the config file. Go to the properties of your project and go to the Deployment Utility page and select True at the CreateDeploymentUtility.
CreateDeploymentUtility





















7) Build to create the Deployment Utility
Build your project (right click project in Solution Explorer and select Build). This will create the 3 files in the bin folder of your project:

Folder: \ProjectName\bin\Deployment\




8) Deployment
Now you can give these three files to the server administrator and let him/her double click on PackageConfig.SSISDeploymentManifest to deploy the package(s) and determine the config file folder. It's even possible to change the config value while deploying.

Sunday 9 December 2012

BUG: Could not decrypt file

Case
I have an Excel (xls) file as a source and it has cell protection turned on to prevent editing the wrong cells. It's an official form from a national care authority, so I can't change that. I don't even have the password.

When I try to read that file in SSIS it gives me an error:
Error at Package.dtsx [connection manager "xxxxx"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured. Error code: 0x80004005
An OLE DB record is available. Source "Microsoft JET Database Engine"Hresult: 0x80004005
Description: Could Not Decrypt File

Error at xxxxx [Excel Source]"SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "xxxxx" failed with error code 0xC0202009.

SSIS 2005 - 2012: Could not decrypt file














The error doesn't occur when the file is still opened in Excel, but that's not really an option on a server.

Solution
You either have to remove the cell protection or convert the XLS file to XLSX (The ACE OLE DB provider doesn't have the same problem as the JET OLE DB provider). Also see/vote for this Microsoft Connect Bug report.

But can you do this automatically if you can't (or don't want to) ask that to the people who provided the excel files or if you have a whole bunch of those files? Here are a couple of options I have examined:

1) Interop Excel in Script Task
There is a Microsoft library (Microsoft.Office.Interop.Excel) available that can do that for you, but the BIG downside is that it requires an Office installation! Even if you download the Redistributable Primary Interop Assemblies (PIA), you still need Office installed. PIA is only a .Net wrapper that lets you communicate in .Net to the COM dll's from Office.
Serverside Office installation is a bad practice and Microsoft doesn't support/recommend an Office installation on a server.

2) Third party / open source dll's  in Script Task
There are a lot of third party and open source dll's for excel. A .Net colleague of mine tried a whole bunch them, but they either don't work for the old (xls) excel files or they don't have an option to convert to xlsx or to remove the cell protection.

3) Cozyroc Excel Task
The COZYROC SSIS+ Library has an Excel Task that can do the conversion to XLSX for you. It's not for free, but I think it's worth it. Hiring an external BI/ETL consultant for two days is probably more expensive. Moreover you get a whole bunch of other cool tasks and transformations with it and you can test them freely within BIDS or SSDT before you buy them. This is my solution with the Cozyroc tasks.
(with a little help from Cozyroc Consultant Ivan Peev)


















Note: Although the Cozyroc Excel Task works like a charm. I still think this is a bug that Microsoft should fix. Cell protection is an edit prevention and I 'm not editing...

Tip: You need to know the password to remove the cell protection in Excel. If you don't have it, use this vba macro to get it.
Related Posts Plugin for WordPress, Blogger...