I can speak SQL

Fluent SQL speaker

Posts Tagged ‘SQL SERVER 2012’

How to install Git Source Control Provider on SQL Server Data Tools

Posted by kulmam92 on September 5, 2013

Git Source Control Provider

If you decide to use Git as a SCM (Source Control Management) system, you may decide to use Git Source Control Provider since it’s integrated to the Visual Studio (SQL Server Data Tools). This is not available from the Extension Manager’s online gallery if you try to search it from the SQL Server Data Tools. However, you can download Git Source Control Provider from its codeplex page : http://gitscc.codeplex.com/.

GitSourceControlProvider

If you click the “Installer for VS2010, VS2012”, “GitSccProvider.vsix” will be downloaded. Double clicking that file should install Git Source Control Provider but you will see the following error message.

ErrorMessage

The message means that SQL Server Data Tools which is a Visual Studio 2010 shell edition is not supported by Git Source Control Provider. Fortunately, there’s a workaround.

Workaround

What is VSIX

It will be easier for you to understand what I’m doing, if you know what VSIX is. Below is the definition of VSIX quoted from the Quan To’s Visual Studio Extensibility blog.

The VSIX file is the unit of deployment for a Visual Studio 2010 Extension. Visual Studio will recognize the VSIX extension and install the contents of the file to the right location.

A VSIX file is a zip file that uses the Open Packaging Convention. You can rename the .VSIX extension to .ZIP and use any zip browser (including the Windows File Explorer) to browse its contents.In short it’s a deployment format for a Visual Studio Extension.

extension.vsixmanifest

One of key files in VSIX is “extension.vsixmanifest”.

extension.vsixmenifest.edit

This file is the manifest that describes the extension. Basically this is a XML file. The details about XML schema can be found from VSIX Extension Schema 2.0 Reference. Element called “<SupportedProducts>” tells compatible versions and editions. As you can see “IntegratedShell” is not listed.

<SupportedProducts> <VisualStudio Version=”10.0″> <Edition>Ultimate</Edition> <Edition>Premium</Edition> <Edition>Pro</Edition> </VisualStudio> <VisualStudio Version=”11.0″> <Edition>Ultimate</Edition> <Edition>Premium</Edition> <Edition>Pro</Edition> </VisualStudio> </SupportedProducts>

So I added “IntegratedShell” to the <SupportedProducts> element like below and saved the changes.

extension.vsixmenifest.contents

You will see the following message, if you close the notepad.

extension.vsixmenifest.save

Now you are ready to install Git Source Control Provider. If you double click the “GitSccProvider.vsix” file, you will the following installation popup.

Install

Summary

Even though Git Source Control Provider doesn’t officially support Visual Studio Shell edition (SQL Server Data Tools), it’s working fine after the installation.  I modified the supported version list to fool the installer. I hope the developer of  Git Source Control Provider adds Visual Studio Shell edition to the official supported list.

Posted in Deployment, SQL SERVER, SSIS | Tagged: , , , , , , , , | Leave a Comment »

SSIS 2012 fail to start – execution timed out

Posted by kulmam92 on June 27, 2013

I’ve recently upgraded to SQL Server 2012 and created a centralized ETL server. Things were fine until I enabled the scheduled jobs. Jobs started to fail randomly after reporting the error below:

Started:  1:10:01 AM  Failed to execute IS server package because of error 0x80131904.
Description: The operation failed because the execution timed out.  Source: .Net SqlClient Data Provider  Started:  1:10:01 AM  Finished: 1:10:07 AM  Elapsed:  5.6 seconds.  The package execution failed.  The step failed.

I searched the web and found that I wasn’t the only one. Furthermore, MS has yet to release a patch for this issue. The relevant reported MS Connect post:

SSIS package fails to start – application lock timeout in SSISDB.catalog.create_execution

My environment

Name Value
Product Microsoft SQL Server Standard (64bit)
Operating System Microsoft Windows NT 6.1 (7601)
Platform NT x64
Version 11.0.3368.0
Memory 8191 (MB)
Processors 1

What I did

I started my investigation by checking dmv and ended up modifying one of the SPs in SSISDB. I know that this isn’t a good choice but did that to fix quickly.

Process Monitoring

I ran a query against sys.dm_exec_requests and got the following results. I noticed that there was a lot of blocking going on and the execution timed out error had occurred because of this.

Before_Change_Lock

I was able to track down that xp_userlock was being called by sp_getapplock in catalog.create_execution SP.

Index creation

Many have pointed out the problem of missing indexes on SSISDB. Phil Brammer shared a missing index creation script via SSIS 2012 – Catalog Indexing Recommendations. I was hoping this error was due to the missing indexes, so I simply applied Phil Brammer’s script instead of spending more time investigating. Unfortunately, it didn’t help and I still got an execution timed out error. At this point I thought it’d be better to use a different approach. I started to review the logic of catalog.create_execution SP thoroughly.

catalog.create_execution modification

Since I needed to fix this timeout issue in a single day, I decided to take an extreme route by modifying the catalog.create_execution SP. I will explain what I changed shortly, but first, the sys.dm_exec_requests query result. As you can see, the blocking issue has been resolved.

after_Change_Lock

You can download the modified code here : [catalog].[create_execution_new].

catalog.create_execution

Looking at the big picture, this SP generates data on four tables: operations, operation_permissions, executions and execution_parameter_values.

Logic flow

The developer of this SP took a procedural and very conservative approach to ensure the following two things: first, that data don’t change until insertion to execution and execution_parameter_values and second, that there was an assurance that different error messages can be returned for each step.

Transaction Lock Step No Main step Sub Step
1 Check if the sp(catalog.create_execution) is called using windows Authentication Returns an error if it’s called using SQL Server Authentication.
2 Insert data into operation table [internal].[insert_operation]
3 Insert data into operation_permissions table [internal].[init_object_permissions]
T X 4 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
T X 5 Begin transaction
T X 6 Start application lock EXEC sp_getapplock @Resource = ‘MS_ISServer_Create_Execution’, @LockTimeOut= 5000, @LockMode = ‘Exclusive’
T X 7 Get project_id and version
T X 8 Get environment information Get environment information using reference_id
Check if there’s any discrepancy in data type or sensitivity between parameter and environment variable
T X 9 Update operations.object_id to project_id
T X 10 Insert data into executions table INSERT into [internal].[executions]
T X 11 Create key to decrypt and encrypt sensitive data CREATE CERTIFICATE
CREATE SYMMETRIC KEY
T X 12 Insert execution_parameter_values 1) insert non-sensitive parameter doesn’t reference environment variable
2) insert sensitive parameter doesn’t reference environment variable
3) Insert records for object_type=50(instance of execution)
4) insert non-sensitive parameter referencing environment variable
5) insert sensitive parameter referencing environment variable
6) update sensitive parameter value after converting data type if it’s date, double or decimal
7) insert any missing parameter from project level object parameter
8) insert any missing parameter from package level object parameter
9) update parameter value to object_parameters.design_default_value if sensitive=0 and required=0 and value_set=0
T X 13 close symmetric keys
T X 14 commit

Now, the Code

I modified the original code to assure the following principals.

  1. Keep SERIALIZABLE isolation level as short as possible
    • I assumed the developer wanted to guarantee that the same meta information for the package is retrieved until he finishes the data insert.
    • You don’t need to keep the SERIALIZABLE isolation level once you have necessary data
  2. Take out application locking
    • I assumed the SERIALIZABLE isolation level is enough to guarantee that meta data wouldn’t be changed during retrieval.
  3. Keep transactions as short as possible
    • A transaction is used only when DML is executed.
  4. Convert the procedural approach to a set-based approach
Transaction Lock Step No Main step Sub Step
1 Check if the sp(catalog.create_execution) is called using windows Authentication Returns an error if it’s called using SQL Server Authentication.
2 Insert data into operation table [internal].[insert_operation]
3 Insert data into operation_permissions table [internal].[init_object_permissions]
X 4 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
X 5 Get project_id, version and environment information convert logic to single SQL statement
X 6 Create key to decrypt and encrypt sensitive data CREATE CERTIFICATE
CREATE SYMMETRIC KEY
X 7 Generate data for execution_parameter_values and insert into temp table convert logic to single SQL statement
X 8 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
T 9 Begin transaction
10 Update operations.object_id to project_id
T 11 Insert data into executions table INSERT into [internal].[executions]
T 12 Insert execution_parameter_values INSERT INTO [internal].[execution_parameter_values]
T 13 close symmetric keys
T 14 commit

Things that you should know:

  1. I took out the permission check logic. As for the reasons:
    • I worried that permission check logic might make the modified query too complicated.
    • In my environment I don’t have any need to set different permission settings for SSISDB objects
  2. I used base tables instead of views.
    • I wanted to be able to predict the execution plan and control access path.

Testing

If you capture what’s been called when you execute a package in an Integration Services Catalogs using the profiler, we will find that three SPs, [catalog].[create_execution], [catalog].[set_execution_parameter_value] and [catalog].[start_execution], are called. Below is the trace that I captured:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N’Package.dtsx’, @execution_id=@execution_id OUTPUT, @folder_name=N’DEV’
, @project_name=N’Test2′, @use32bitruntime=False, @reference_id=1
Select @execution_id

exec sp_executesql N’DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N”LOGGING_LEVEL”, @parameter_value=@var0
‘,N’@execution_id bigint’,@execution_id=10010
go
exec sp_executesql N’EXEC [SSISDB].[catalog].[start_execution] @execution_id’,N’@execution_id bigint’,@execution_id=10010
go

I created my SP [catalog].[create_execution_new] and checked if both [catalog].[create_execution] and [catalog].[create_execution_new] generates the same result. I also checked if the execution result of both were identical, and they were. I then created a backup copy of [catalog].[create_execution] and replaced [catalog].[create_execution] with my SP. It’s been several days since I made the replacement and it’s still working well.

Summary

I’m afraid that I had to choose an extreme workaround. However, it was a good chance to peek at both how the SSIS catalog works and what the developer was thinking. I hope this helps those who are experiencing the same issue and that a fix from MS is coming soon.

Posted in SQL SERVER, SSIS | Tagged: , , , , | 7 Comments »

SSISDB ERD – A Journey to DB deployment automation

Posted by kulmam92 on June 17, 2013

I decided to reverse engineer the SSISDB to have a better understanding of the SSIS catalog. Reza Rad posted a really good article called “SSIS 2012 Catalog series“. However, I thought it would be better to create my own version of an ERD to have better understanding of it. Here’s what i came up with.

SSISDB ERD

SSIS ERD

Yon can download the pdf version of this from SSISDB_ERD

Table

Main tables

Object related tables

There are four object tables: folder, environment, project and package. I think you can easily figure out the purpose of each table. Other than these key tables are the tables for relationship (environment_reference), parameters, versions and permissions. SSISDB keeps a project’s previous versions as binary data in the internal.object_versions table. You can specify how many versions that you want to keep by using the properties of an SSIS catalog.

image

[Operations]

This table contains one row for each operation in the Integration Services catalog. You can track all logical operations that were performed on the server using this table, such as project deployment or package execution. Below are the operation_type values from BOL:

operation_type Value operation_type Description object_id Description object_name Description
1 Integration Services initialization NULL NULL
2 Retention window (SQL Agent job) NULL NULL
3 MaxProjectVersion (SQL Agent job) NULL NULL
101 deploy_project (Stored procedure) Project ID Project name
106 restore_project (Stored procedure) Project ID Project name
200 create_execution and start_execution (Stored procedures) Project ID NULL
202 stop_operation (Stored procedure) Project ID NULL
300 validate_project (Stored procedure) Project ID Project name
301 validate_package (Stored procedure) Project ID Package name
1000 configure_catalog (Stored procedure) NULL NULL

[Executions]

[executions] has a one-to-one relationship with [operations], which means the [execution] table is a subset of the [operations] table. If you look at the ERD closely, the relationship from operation to execution is made only when the operation type is 200 – create_execution and start_execution (Stored procedures). This means only operation_type value 200 will generate a record in the execution table.

image

If you want to know which package in a project was executed, you can use the following query. This is the creation script of the [catalog].[executions] view.

SELECT     execs.[execution_id],
execs.[folder_name],
execs.[project_name],
execs.[package_name],
execs.[reference_id],
execs.[reference_type],
execs.[environment_folder_name],
execs.[environment_name],
execs.[project_lsn],
execs.[executed_as_sid],
execs.[executed_as_name],
execs.[use32bitruntime],
opers.[operation_type],
opers.[created_time],
opers.[object_type],
opers.[object_id],
opers.[status],
opers.[start_time],
opers.[end_time],
opers.[caller_sid],
opers.[caller_name],
opers.[process_id],
opers.[stopped_by_sid],
opers.[stopped_by_name],
opers.[operation_guid] as [dump_id],
opers.[server_name],
opers.[machine_name],
ossysinfos.[total_physical_memory_kb],
ossysinfos.[available_physical_memory_kb],
ossysinfos.[total_page_file_kb],
ossysinfos.[available_page_file_kb],
ossysinfos.[cpu_count]
FROM       [internal].[executions] execs INNER JOIN [internal].[operations] opers
ON execs.[execution_id]= opers.[operation_id]
LEFT JOIN [internal].[operation_os_sys_info] ossysinfos
ON ossysinfos.[operation_id]= execs.[execution_id]
WHERE      opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER(‘ssis_admin’) = 1)
OR (IS_SRVROLEMEMBER(‘sysadmin’) = 1)

Message related tables

If you want to check the execution log of a package, these are the tables that you need to query. [operation_messages] and [event_messages] have a one-to-one relationship similar to [operation] and [event]. [event_messages] is a subset of [operation_messages]. The below is the source code of the [catalog].[event_messages] view.

Declare @ExecutionID NVarChar(max)
select @ExecutionID=N’132′
SELECT     opmsg.[operation_message_id] as [event_message_id],
opmsg.[operation_id],
opmsg.[message_time],
opmsg.[message_type],
opmsg.[message_source_type],
opmsg.[message],
opmsg.[extended_info_id],
eventmsg.[package_name],
eventmsg.[event_name],
message_source_name =
CASE
WHEN (opmsg.message_source_type = 10) THEN ‘ISServerExec’
WHEN (opmsg.message_source_type = 20) THEN ‘Transact-SQL stored procedure’
ELSE eventmsg.message_source_name
END,
eventmsg.[message_source_id],
eventmsg.[subcomponent_name],
eventmsg.[package_path],
eventmsg.[execution_path],
eventmsg.[threadID],
eventmsg.[message_code]
FROM       [internal].[operation_messages] opmsg LEFT JOIN [internal].[event_messages] eventmsg
ON opmsg.[operation_message_id] = eventmsg.[event_message_id]
WHERE      opmsg.[operation_id] = @ExecutionID

If you execute an SSIS package deployed to a SSIS catalog and it fails, you will see a message similar to the following.

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  4:09:59 PM  Package execution on IS Server failed. Execution ID: 120092, Execution Status:4.  To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report  Started:  4:09:59 PM  Finished: 4:10:27 PM  Elapsed:  28.641 seconds.  The package execution failed.  The step failed.

You will need to use either a prebuilt standard report or query the SSISDB tables to get a detailed error message. The following screenshot shows where you can locate that report. If you decide to query the SSISDB, you may want to consider using the SSIS Reporting Pack developed by Jamie Thomson.

image

Table definition

Here I list the tables and its definitions copied from BOL.

Schema

Table name

Definition

internal catalog_encryption_keys Contains SSISDB encryption key
internal catalog_properties Contains the properties of the Integration Services catalog.
internal data_type_mapping Contains SSIS data type and SQL data type mapping
internal environment_permissions
internal environment_references Contains the environment references for all projects in the Integration Services catalog.
internal environment_variables Contains the environment variable details for all environments in the Integration Services catalog.
internal environments Contains the environment details for all environments in the Integration Services catalog. Environments contain variables that can be referenced by Integration Services projects.
internal event_message_context
internal event_messages Contains information about all messages passed during events like OnWarning, OnError
internal executable_statistics Contains Detailed execution log for each executable
internal executables Contains Information about each executable object in packages
For example : Package itself, Execute SQL Task, ETC.
internal execution_component_phases Contains Detailed execution log for each data flow component.
internal execution_data_statistics Contains Number of rows sent through data flow and few other details
internal execution_data_taps Contains information about data taps
Data Taps are new features in SSIS 2012 which provides ability to log whole data stream in one of data flow’s data paths. and the best part of story is that you can add and remove data taps to a package at the time of execution
internal execution_parameter_values Contains the actual parameter values that are used by Integration Services packages during an instance of execution.
internal execution_property_override_values Contains information about property values that set at the time of execution of package and overrides default values.
internal executions Contains the instances of package execution in the Integration Services catalog. Packages that are executed with the Execute Package task run in the same instance of execution as the parent package. With every execution of package, an Execution_id will be assigned
internal extended_operation_info Contains extended information for all operations in the Integration Services catalog.
internal folder_permissions
internal folders Contains the folders in the Integration Services catalog.
internal object_parameters Contains the parameters for all packages and projects in the Integration Services catalog.
internal object_versions Contains the versions of objects in the Integration Services catalog. In this release, only versions of projects are supported in this view.
internal operation_messages Contains messages that are logged during operations in the Integration Services catalog.
internal operation_os_sys_info Contains system information like cpu count, available physical memory and total physical memory at the time of operation
internal operation_permissions Contains the effective permissions for the current principal for all objects in the Integration Services catalog.
internal operations Contains the details of all operations in the Integration Services catalog. Each operation can be applied to an object and have a status as a result.
internal packages Contains the details for all packages that appear in the Integration Services catalog.
internal project_permissions
internal projects Contains the details for all projects that appear in the Integration Services catalog.
internal validations Contains the details of all project and package validations in the Integration Services catalog.

Scripts

Code definition tables

Reza Rad shared a script that creates code conversion tables. I created and added a table for permsssion_type. You can download the script from SSISDB_CodeTables.

Posted in Deployment, SQL SERVER, SSIS | Tagged: , , , , | 1 Comment »

A Journey to DB deployment automation-Planning

Posted by kulmam92 on June 8, 2013

The mind map below shows what I had in mind when planning and implementing automation for DB Deployment. I will cover the details in a later series.

DB_Deployment_Xmind

Covered topics

Posted in Deployment, SQL SERVER | Tagged: , , , , , , , , , , | Leave a Comment »