Thursday 13 February 2014

Connecting to Excel (XLSX) in SSIS

Case
I cannot access my Excel sheets in SSIS. The Excel version in the Connection Manager is Microsoft Excel 2007 (xlsx).
Could not retrieve the table information for the
connection manager 'Excel Connection Manager'.
Failed to connect to the source using the
connection manager 'Excel Connection Manager'




























Solution
XLSX files don't use the out-of-the-box Microsoft.Jet.OLEDB provider, but they need the Microsoft.ACE.OLEDB provider. You either did not install it or you installed the 64bit version.

Download and install the 32bit version of the Microsoft Access Database Engine 2010 Redistributable. Because Visual Studio (SSDT/BIDS) is 32bit you can't use the 64bit provider for developing SSIS packages. If you already installed the 64bit version then you first need to remove it. You can't install 32bit and 64bit parts of office on the same machine. You will get an error when you run the installer (and you will get the same error if you have a 64bit version of Microsoft Office installed on your development machine):
You cannot install the 32-bit version of Microsoft
Access Database Engine 2010 because you currently
have 64-bit Office products installed. If you want to
install 32-bit Microsoft Access Database Engine 2010,
your first need to remove the 64-bit installation of
office products.

















However this means that you can't run packages with Excel Connection Mangers in 64bit on your development machine. You need to switch to 32bit, otherwise you will get an error like:
Information: 0x4004300A at DFT - xlsx source, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0209303 at xlsxSource, Connection manager "Excel Connection Manager": The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered.
If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Error: 0xC001002B at xlsxSource, Connection manager "Excel Connection Manager": The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816
Error: 0xC020801C at DFT - xlsx source, EX_SRC - My XLSX Source [8]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at DFT - xlsx source, SSIS.Pipeline: EX_SRC - My XLSX Source failed validation and returned error code 0xC020801C.
Error: 0xC004700C at DFT - xlsx source, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at DFT - xlsx source: There were errors during task validation.

But since there is a 64bit driver... you could install it on your test/acceptance/production server and run the packages in 64bit (as long as you don't use Visual Studio on those servers).

Saturday 1 February 2014

Deploying SSIS 2012 Environments

Case
Parameters and environments are a handy new feature in SSIS 2012, but creating environments with variables and referencing the environment to the project and the variables to the parameters is a lot of effort. No problem if you have to do it once, but if you deploy your packages through the DTAP servers you have to redo it on every single server. Is there a way to deploy environments?

No export option for Environments















Solution
Although it is easy to deploy packages through DTAP servers. Deploying environments is not  possible. That could get a little tiresome especially if you have a lot of parameters in your project.

I have created a script/stored procedure that uses the project identifier as input, loops through the tables of the SSISDB and prints SQL-statements to execute SSISDB Stored Procedures.
How to get the project identifier
















For every environment, variable and reference to this project it will generate creation scripts. You can copy these stored procedure calls, adjust the values where needed and execute them on the next environment.
Execute script then copy and execute output

























Release notes
  1. There are no checks or validations in this version. So it doesn't check whether objects already exist before calling the SSISDB Stored Procedures.
  2. First deploy the SSIS project before executing the stored procedure calls on the next server.
  3. Make sure the folder name is equal on the new server or change it in the stored procedure calls.
  4. Make sure to check sensitive variables values (you can't get the value from the tables).


Here is how you call the stored procedure to generate the scripts. If you don't want to add the new stored procedure then you could just use the script inside the stored procedure.
exec catalog.deploy_environment 11

Add the following stored procedure to the SSISDB or use the TSQL code inside.
USE SSISDB;
GO

-- USE AT OWN RISK! This stored procedure was created on the SSISDB on SQL Server version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--      Dec 28 2012 20:23:12 
--      Copyright (c) Microsoft Corporation
--      Developer Edition (64-bit) on Windows NT 6.1 <x64> (Build 7601: Service Pack 1)


-- Drop any previous versions of this stored procedure
IF OBJECT_ID ( 'catalog.deploy_environment', 'P' ) IS NOT NULL 
    DROP PROCEDURE catalog.deploy_environment;
GO

-- project_id is the identifier in the properties of a project
CREATE PROCEDURE catalog.deploy_environment 
    @project_id bigint 
AS 

 -- Internal variables used within the cursor
 Declare @environment_name as nvarchar(128);
 Declare @project_name as nvarchar(128);
 Declare @folder_name as nvarchar(128);
 Declare @environment_folder_name as nvarchar(128);
 Declare @reference_type as char(1);
 Declare @folder_id as bigint;
 Declare @environment_description as nvarchar(1024);
 Declare @environment_id as bigint;


 DECLARE ref_environment_cursor CURSOR FOR 
  -- Loop through all in the project referenced Environments
  SELECT  r.environment_name
  ,   p.name as project_name
  ,   ISNULL(r.environment_folder_name, f.name) as folder_name
  ,   ISNULL(r.environment_folder_name, f.name) as environment_folder_name -- for @reference_type = A
  ,   r.reference_type as reference_type
  ,   f.folder_id
  ,   e.description as environment_description
  ,   e.environment_id
  FROM  [SSISDB].[internal].environment_references as r
  INNER JOIN [SSISDB].[internal].projects as p
     on r.project_id = p.project_id
  INNER JOIN [SSISDB].[internal].folders as f
     on p.folder_id = f.folder_id
  INNER JOIN [SSISDB].[internal].environments as e
     on e.folder_id = f.folder_id
     and e.environment_name = r.environment_name
  WHERE  r.project_id = @project_id

 OPEN ref_environment_cursor

 FETCH NEXT FROM ref_environment_cursor 
 INTO @environment_name, @project_name, @folder_name, @environment_folder_name, @reference_type, @folder_id, @environment_description, @environment_id;

 Print '-- Create scripts for deploying enviroments'
 Print '-- Project ID: ' + CAST(@project_id as varchar(5)) + ' - Project name: ' + @project_name
 Print ''

 WHILE @@FETCH_STATUS = 0
 BEGIN
  -- Create environment
  Print '-- Create environment: ' + @environment_name
  Print 'EXEC  [SSISDB].[catalog].[create_environment]'
  Print '   @environment_name=N''' + @environment_name + ''''
  Print ',   @environment_description=N''' + @environment_description + ''''
  Print ',   @folder_name=N''' + @folder_name + ''''
  Print 'GO'
  Print ''

  -- Create reference from environment to project. Relative or Absolute
  Print '-- Reference environment ' + @environment_name + ' to project ' + @project_name
  IF @reference_type = 'R'
  BEGIN
   -- Reference Relative
   Print 'Declare @reference_id bigint'
   Print 'EXEC  [SSISDB].[catalog].[create_environment_reference]'
   Print '   @environment_name=N''' + @environment_name + ''''
   Print ',   @reference_id=@reference_id OUTPUT'
   Print ',   @project_name=N''' + @project_name + ''''
   Print ',   @folder_name=N''' + @folder_name + ''''
   Print ',   @reference_type=R'
   Print 'GO'
   Print ''
  END
  ELSE
  BEGIN
   -- Reference Absolute
   Print 'Declare @reference_id bigint'
   Print 'EXEC  [SSISDB].[catalog].[create_environment_reference]'
   Print '    @environment_name=N''' + @environment_name + ''''
   Print ',   @environment_folder_name=N''' + @environment_folder_name + ''''
   Print ',   @reference_id=@reference_id OUTPUT'
   Print ',   @project_name=N''' + @project_name + ''''
   Print ',   @folder_name=N''' + @folder_name + ''''
   Print ',   @reference_type=A'
   Print 'GO'
   Print ''
  END

  
  -- Internal variables used within the cursor 
  Declare @environment_value as sql_variant--nvarchar(max); -- SQL_VARIANT
  Declare @variable_name as nvarchar(128);
  Declare @sensitive as bit;
  Declare @variable_description as nvarchar(1024);
  Declare @variable_type as nvarchar(128);

  DECLARE environment_var_cursor CURSOR FOR 
   -- Loop through all in the variables of the active environment
   SELECT  CAST(ev.value as varchar(255)) as environment_value
   ,   ev.name as variable_name
   ,   ev.sensitive
   ,   ev.description as variable_description
   ,   ev.type as variable_type
   FROM  [SSISDB].[catalog].environment_variables as ev
   WHERE  environment_id = @environment_id

  OPEN environment_var_cursor

  FETCH NEXT FROM environment_var_cursor 
  INTO @environment_value, @variable_name, @sensitive, @variable_description, @variable_type;

  WHILE @@FETCH_STATUS = 0
  BEGIN
   -- Environments variables
   Print '-- Create variables for environment: ' + @environment_name + ' - ' + @variable_name

   -- Variable declaration depending on the type within the environment
   IF @variable_type = 'Boolean'
   BEGIN
    Print 'DECLARE @var bit = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Byte'
   BEGIN
    Print 'DECLARE @var tinyint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'DateTime'
   BEGIN
    Print 'DECLARE @var datetime = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Decimal'
   BEGIN
    Print 'DECLARE @var decimal(38,18) = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Double'
   BEGIN
    Print 'DECLARE @var float = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Int16'
   BEGIN
    Print 'DECLARE @var smallint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Int32'
   BEGIN
    Print 'DECLARE @var int = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Int64'
   BEGIN
    Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'SByte'
   BEGIN
    Print 'DECLARE @var smallint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Single'
   BEGIN
    Print 'DECLARE @var float = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'String'
   BEGIN
    Print 'DECLARE @var sql_variant = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'UInt32'
   BEGIN
    Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'UInt64'
   BEGIN
    Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END

   Print 'EXEC  [SSISDB].[catalog].[create_environment_variable]'
   Print '   @variable_name=N''' + @variable_name + ''''
   IF @sensitive = 0
   BEGIN
    Print ',   @sensitive=False'
   END
   ELSE
   BEGIN
    Print ',   @sensitive=True'
   END
    Print ',   @description=N''' + @variable_description +  ''''
   Print ',   @environment_name=N''' + @environment_name + ''''
   Print ',   @folder_name=N''' + @folder_name + ''''
   Print ',   @value=@var'
   Print ',   @data_type=N''' + @variable_type + ''''
   Print 'GO'
   Print ''

   FETCH NEXT FROM environment_var_cursor 
   INTO @environment_value, @variable_name, @sensitive, @variable_description, @variable_type;
  END
  CLOSE environment_var_cursor;
  DEALLOCATE environment_var_cursor;
  -- End Environments variables

  -- Parameter - Variable mapping
  Declare @object_type as smallint
  Declare @parameter_name as nvarchar(128);
  Declare @object_name as nvarchar(260);
  Declare @folder_name2 as nvarchar(128);
  Declare @project_name2 as nvarchar(128);
  Declare @value_type as char(1)
  Declare @parameter_value as nvarchar(128);

  DECLARE parameter_var_cursor CURSOR FOR 
   -- Loop through variables referenced to a parameter
   SELECT  op.object_type
   ,   parameter_name
   ,   [object_name]
   ,   f.name as folder_name
   ,   p.name as project_name
   ,   value_type
   ,   referenced_variable_name as parameter_value
   FROM  [SSISDB].[internal].object_parameters as op
   INNER JOIN [SSISDB].[internal].projects as p
      on p.project_id = op.project_id
   INNER JOIN [SSISDB].[internal].folders as f
      on p.folder_id = f.folder_id
   WHERE  op.project_id = @project_id
   AND   referenced_variable_name is not null

  OPEN parameter_var_cursor

  FETCH NEXT FROM parameter_var_cursor 
  INTO @object_type, @parameter_name, @object_name, @folder_name2, @project_name2, @value_type, @parameter_value;

  WHILE @@FETCH_STATUS = 0
  BEGIN
   -- Reference variables
   Print '-- Reference variable ' + @parameter_value + ' to parameter ' + @parameter_name
   Print 'EXEC  [SSISDB].[catalog].[set_object_parameter_value]' 
   Print '   @object_type=' + CAST(@object_type as varchar(5))
   Print ',   @parameter_name=N''' + @parameter_name + ''''
   Print ',   @object_name=N''' + @object_name + ''''
   Print ',   @folder_name=N''' + @folder_name2 + '''' ----
   Print ',   @project_name=N''' + @project_name2 +  '''' ---
   Print ',   @value_type=' + @value_type
   Print ',   @parameter_value=N''' + @parameter_value + ''''
   Print 'GO'
   Print ''

   FETCH NEXT FROM parameter_var_cursor 
   INTO @object_type, @parameter_name, @object_name, @folder_name2, @project_name2, @value_type, @parameter_value;
  END
  CLOSE parameter_var_cursor;
  DEALLOCATE parameter_var_cursor;
  -- End Parameter - Variable mapping

  FETCH NEXT FROM ref_environment_cursor 
  INTO @environment_name, @project_name, @folder_name, @environment_folder_name, @reference_type, @folder_id, @environment_description, @environment_id;
 END
 CLOSE ref_environment_cursor;
 DEALLOCATE ref_environment_cursor;
GO
Download as SQL file

NOTE: Please use at own risk and let me know it things could be improved!
Related Posts Plugin for WordPress, Blogger...