donderdag 25 november 2010

Change owner of Database Objects Schema and Role

Do you also always forget how to do the simple things? I do.

Today I needed to change the owner of database schemas and roles after migrating databases from SQL 2000 to SQL 2008 and again did forget how to do this in T-SQL. To never forget again and for those who are looking for the same:

Change Schema owner:
ALTER AUTHORIZATION ON SCHEMA::[<schemaname>] TO [<newowner>]

Change Role owner:
ALTER AUTHORIZATION ON ROLE::[<rolename>TO [<newowner>]

woensdag 24 november 2010

SQL11 (Denali) CTP1 First look at SSIS Package Configuration or is Parameters?


Today I was looking at package configurations in SSIS SQL11 (Denali) CTP1, clear is that the current CTP is not completed yet as Jamie Thomson and Hennie de Nooijer did discuss on there blogs.

I created a basic package with a connection manager and a single SQL Task that creates a table. It's simple but I want to explain Package Configurations and not sophisticated packages :-).

The first thing that intrigued me was that when I right-clicked on the control flow page the option to choose Package Configuration did not appear.

Looking a little further you will discover it's still there, click the control flow background again to display the package properties. On the properties pane browse to the MISC section, Configuration and click the [...] button that is displayed behind (Collections). The trusted enable Package Configuration pane will appear!

Again when you now right-click on the Control Flow background Package Configurations is even showed in the pop-up menu.

So why is it not there in the first place as we know how to find it in SQL 2005/2008? Is it going to be depreciated in the SQL Server editions that will follow on SQL11 or is it just because the CTP1 edition and will be there in the RTM?

It could be depreciated because now SSIS introduces Package Parameters! The way they work is how I like to use variables for setting up database connections based on Package Variables and Expressions. Only now you can also manage them in SQL Server Management Studio for SQL 11 Denali. Let's have a look.

Back to the previous package, the first thing is to display the Parameters and Variables, when you look at the SSIS menu after selecting the control flow the first change is we see not only Variables but it has changed to Parameters and Variables.


When enabled we see a new pane displaying Parameters and Variables

What I now like to do is make my Connection TargetDB parameterized. The first thing we need to do is to add a Parameter, click the Add button, a new Parameter entry will be enabled, enter your desired naming of the Parameter.

The second step is to configure the Connection Manager TargetDB to use the created Parameter. Select the Connection Manager TargetDB, Go to the Properties pane Expressions section and add a new Expression property for the Connectionstring. The first thing you see that there is no Parameters section displayed only Variables, something to mention on  to create a separated Parameters section?

Expanding the Variables section brings us further, new kind of Variable is listed, this should be the Parameter, again naming is ugly I hope it is the CTP1 edition. Lets select the Parameter and evaluate.

Seems to be fine so click OK. We now have a Parameterized Connection Manager. When you change the Parameter in the Package and Save, Close and re-open the package the Connection Manager is changed. Just like I used to with Package Configurations, Variables used for the Connection Manager connectionstring expression.

Only now in Denali we have an SSIS Catalog on the SQL Server that helps with using the Parameters for quick change of the processing environment of the packages when running on the server.

First thing to do is create a SSIS Catalog on the SQL Server instance. Start SSMS, open a Connection to the SQL Server Database Engine instance you Like to create the Catalog database on. Right-Click the Integration Services Folder. No there is no seperated SSIS Engine to connect to available anymore in Denali :-) it's standard under the Database Engine.
Fill in the requested information, I can not change the Catalog name and you can only create one Catalog on each instance. Password is required, enable OK only when password is entered.

I did take a look or I could simpel create the database by T-SQL, it should be possible only the database is created by a restore file \MSSQL11.MSSQL01\MSSQL\DATA\..\Backup\SSISDBBackup.bak and maintenance task (SSIS Server Maintenance Job) is created during the process. So let's skip that for now :-)

Next is to create a folder to deploy our package project to. Right-Click the just created Catalog database and select Create the Folder.

T-SQL statements to create the folder:

Declare @folder_id bigint
EXEC [SSISDB].[catalog].[create_folder] @folder_name=N'FirstLook', @folder_id=@folder_id OUTPUT
Select @folder_id
EXEC [SSISDB].[catalog].[set_folder_description] @folder_name=N'FirstLook', @folder_description=N'My First SSIS Catalog Folder'

When folder is created it contains 2 sub folders, Projects and Environments.

The projects folder will hold the SSIS Project after deployment, In the environments folder we create the desired environments.
Let's create the Environment we need, see my previous blog for the T-SQL statements, for now right-click the Environment Folder and select Create Environment. Fill the pane with the information needed and click OK.

A Environment section is created.

Double click the Environment envFirstLook and select the Variables tab, note to my self should this not be Parameters or Parameters and Variables. Enter the needed information, click OK.

Now we have a SSIS Catalog, a Catalog Folder and the Environment. Next is to deploy our SSIS Project to the server.

In BIDS go to the Project Properties, build the project and then start the deployment process by right-click the project in the properties pane and select deploy.
The first step can be skipped, the second step is to select the source where to create the deployment package from. You can select the catalog and catalog folder to create a deployment from an existing deployed environment, for now we select the Project deployment file and click Next

Now we need to select the destination where we like to deploy to. This is a three-way path // you can not skip the projectname.

The next thing is to configure the Parameters, I left is as is and clicked Next.

Now review your settings and click Deploy, when deployment is finished this is displayed as result, click Close.

After deployment is finished take a look at SSMS to see of your deployment has been successful. When correct right-click the SSIS Project that is created. In the Project Properties pane select page References and select the previous created reference.

Change the Parameter property for the created parameter by selecting Parameters in the Projects Properties Pane.

We now can run the Package using the just created environment....
In SSMS right click the Package and select Run. In the run package pane Select at Environment Reference the previous created References and Click OK.

The package progress is shown in the Package Running Pane. View it and look at the TargetDB you configured in the environment or the CREATE TABLE Statement is executed.

On SQL01\MSSQL01 instance a see a TestAF table as mentioned in the CREATE TABLE statement. To make sure my Environments are working I created a New environment envFirstLookSQL03 that point to .... guess ofcourse SQL03\MSSQL01. And added it to the Project as a Second Reference.


Run the package againg but now select the envFirstLookSQL03 reference.

On the Package run information we can see it has been successful and see on the Parameters tab that the connection has changed to the one that was entered in the envFirstLookSQL03 environment.

This it it for now, I would like to show how to use this when executing packages from a SQL Agent Job but this seems not to be implemented yet in SQL11 Denali CTP1 or I can just not find it!

SQL11 (Denali) CTP1 Create Environments and Environments variables with T-SQL

Looking at the current CTP I found that I could not find the Package Configurations any more. Package configurations seems to be replaced by Package Parameters. The relation to make these parameters dynamic is the Environment configuration in the SSIS Catalog.

Because I like to make i possible to script everything to be able to deploy or duplicate environments for disaster recovery or just simple fall back I did take a look at how to use T-SQL to script SSIS Enviroments.
All steps are based on that the SSIS Catalog already exists.

There a in basic 4 stored procedures that can be used for creating and deleting the environment and environment variables.

First step is to create the environment:

, @environment_description=(nvarchar(1024))
, @folder_name=(nvarchar(128))'

Second step is to create the environment variable:
, @sensitive=(bit)
, @description=(nvarchar(1024))
, @environment_name=(nvarchar(128))
, @folder_name=(sysname)
, @value=(sql_variant)
, @data_type=(nvarchar(128))

When deleting it is possible to delete the environment and all environment variables with the delete_environment stored procedure.
When you want to delete a single environment variable just use:

, @environment_name=(nvarchar(128))
, @folder_name=(nvarchar(128))

Delete the enviromments
, @folder_name=(nvarchar(128))

Here are some samples how to add and drop Environments and Enviroment variables using the given stored procedures.


--- Create a new Environment
@folder_name = (nvarchar(128)
IF NOT EXISTS (SELECT 1 FROM catalog.environments WHERE name = N'envFirstLook')
EXEC [catalog].[create_environment]
, @environment_description=N'This is my first Integration Services Environment'
, @folder_name=N'FirstLook'
PRINT 'Environment is created'
PRINT 'Environment already exists'

--- Create a new Environment Variable
IF NOT EXISTS (SELECT 1 FROM catalog.environment_variables WHERE name = N'MyFirstEnvironmentVariable')
DECLARE @var sql_variant = N'This is the value of the environment variable'
EXEC [catalog].[create_environment_variable]
, @sensitive=False
, @description=N'Why is this a Environment Variable and not Parameter?'
, @environment_name=N'envFirstLook'
, @folder_name=N'FirstLook'
, @value= @var
, @data_type=N'String'
PRINT 'Environment variable is created'
PRINT 'Environment variable already exists'

--- Drop environment variable
IF EXISTS (SELECT 1 FROM catalog.environment_variables WHERE name = N'MyFirstEnvironmentVariable')
EXEC [catalog].[delete_environment_variable]
, @environment_name=N'envFirstLook'
, @folder_name=N'FirstLook'
PRINT 'Environment variable is deleted'
PRINT 'Environment variable not found, nothing to drop'

--- Drop environment
IF EXISTS (SELECT 1 FROM catalog.environments WHERE name = N'envFirstLook')
EXEC [catalog].[delete_environment]
, @folder_name=N'FirstLook'
PRINT 'Environment is deleted'
PRINT 'Environment not found, nothing to drop'

woensdag 17 november 2010

Enabling Distributed Transaction Support for SSIS

I need to build SSIS packages that handle transaction over multiple SQL Servers. When a SQL transaction fails in a sequence container all other SQL transactions in that container should be rolled back.

The SSIS Package will run from SQL01.
The SSIS Package contains a single Sequence Container:
The package level Transaction configuration is set to:
The Sequence Container Transaction configuration is set to:
The SQL tasks Transaction configuration is set to: (all SQL Tasks)

Both servers contain a test database with the table tblName in it.
CREATE TABLE [dbo].[tblName](
[Name] [nchar](10) NULL

On SQL01 the below inserts are executed:



The table on SQL02 is still empty:

The SQL tasks in the package are executing the following commands:

  • SQL Task: SQL Insert Bert Local
    INSERT INTO [tblName] ([Name]) VALUES ('Bert')
  • SQL Task: SQL Insert Bert Remote
    INSERT INTO [tblName] ([Name]) VALUES ('Bert')
  • SQL Task: SQL Insert Key 1 again local
    INSERT INTO [tblName]
    (ID,[Name]) VALUES (1,'Bert')
The first and second SQL task should be executed without errors, the last SQL task will cause a duplicate Key insert error and fail the sequence container.

I added a Pre-Execute brake point on the last SQL task to make it possible to check the results of the preceding SQL tasks.

Let's first run the package without Distributed Transaction enabled; DTC configuration is the same on both servers:

Let's run the package and look at the result.
We do get an error immediately:
When we take a look at the SSIS Package execution progress see multiple errors.
  • On the SQL Insert Bert Remote task the below error is displayed:
    [Execute SQL Task] Error: Failed to acquire connection "SQL02.dbAFTest". Connection may not be configured correctly or you may not have the right permissions on this connection.
  • On the Package level I see the below errors displayed
    [Connection manager "SQL02.dbAFTest"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions.".

    [Connection manager "SQL02.dbAFTest"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D025.
The first error on the SQL task doesn’t clarify much. The other error mentions something about the distributed transaction. Because we configured the sequence container to REQUIRE the transaction it needs to be able to handle distributed transaction.

Let's configure DTC on SQL01 and SQL02.

  1. Logon to the server with a local administrators account
  2. Click start
  3. In the search box type dcomcnfg and hit ENTER
  4. Browse to the Local DTC
  5. Right click the Local DTC and select properties
  6. Select the security tab and set configuration as belowimage021
    I know that Mutual Authentication Required is more secure; this is however not supported on SQL Server Cluster.
  7. Click OK
  8. A warning will be displayed that a Reset of the DTC service is going to be executed. Click Yes
  9. A message will be displayed that the reset is completed.
  10. Do the same on the other server(s)
Let's run the package again and look at the result.
For a longer period then I would like to, the below status of the run is displayed, nothing seems to be happening. Get some coffee! And be patient.
After about 2,5 minutes the following is displayed.
Not where I hoped for so let's look at the progress errors.
  • On the SQL Insert Bert Remote task the below error is displayed:
    [Execute SQL Task] Error: Failed to acquire connection "SQL02.dbAFTest". Connection may not be configured correctly or you may not have the right permissions on this connection.
  • On the Package level I see the below errors displayed
    [Connection manager "SQL02.dbAFTest"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted".

    [Connection manager "SQL02.dbAFTest"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D00E.
Still seems to be wrong. When you compare these errors with the previous ones you will see some differences.

The first time the DTC error displayed: "The partner transaction manager has disabled its support for remote/network transactions."
This time the DTC error displays: "The transaction has already been implicitly or explicitly committed or aborted".

So what did we do wrong or even did forget? When you search the internet for the last DTC error you will find multiple references to the Windows firewall. So let's have a look. On both servers I found that the DTC TCP firewall Inbound/Outbound rules are still disabled.

Let's enable them, I hope you know how, do this on all servers that require to use SSIS and DTC.

With the firewall rules enabled, let's run the package again and look at the result.
This looks promising, before we continue processing let's look at the SQL results on the tables.

SQL Insert Bert Local Task result on server SQL01
SQL Insert Bert Remote Task result on server SQL02

Until now it is as we should aspect. Both tasks completed successful and data is inserted in the tables. The next step should fail!
And lucky us! It does!
Let's look at the progress errors.
  • On the SQL Insert Key 1 again local task the below error is displayed:
    [Execute SQL Task] Error: Executing the query "INSERT INTO [tblName] (ID,[Name])..." failed with the following error: "Cannot insert explicit value for identity column in table 'tblName' when IDENTITY_INSERT is set to OFF.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This is an expected error, but what we need to now are previous transactions rolled back?

On SQL01 the previous inserted Bert by the SQL Insert Bert Local task is gone!
On SQL02 the table is empty, again.
We are now ready to develop packages with multi server transactions!

See How to Cluster MSDTC for additional information how to configure MSDTC.