ISSUE#0002:0x8004D01B " The Transaction Manager is not available.". The DTC transaction failed to start.

There're some TransactionOption can be set in SSIS Package (Package Level, Container Level, Task Level...):

• Required - if a transaction exists join it else start a new one
• Supported - if a transaction exists join it (this is the default)
• NotSupported - do not join an existing transaction

But sometime when you set Required to a container or other component and to execute the package, error information will show you:

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Just use an example to show how to use Trasaction in SSIS package and solve this issue.

1. There're two SQL Tasks in a container, the first task is to TRUNCATE all data in 'Documents' table, the second task is to insert one record to table 'Documents'. I just use a wrong insert statement in the second task, so the second task will throw an error when I execute this package. We use default TransactionOption - Supported to all components.

 

The result is the 'Insert Data' task failed, but the 'Truncate Data' works and all data in table have been deleted.



2. Change TransactionOption of Sequence Container to 'Required'.

 

 



Execute this package and get this error

 



 

The error information show - The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

3. This error information tells you need to start your MSDTC Service. Open the cmd prompt with Administrator role.

Try 'NET START MSDTC' to start your MSDTC Service.

Then execute this package again.

 

Task 'Insert Data' still throw an error but the 'Truncate Data' will be roll back. Because they're in a same transaction when Sequence Container set its TransactionOption from 'Supported' to 'Required'.

SQL T001- How to use SQL Profiler to get DeadLock information

 Test - How to use SQL Profiler to get the DeadLock Information.

First, to create two tables and insert some testing data.

-------------------------------------------------

-- Script 1 - Create testing table first.

-------------------------------------------------

USE SSISDemoDB

GO

SET NOCOUNT ON

GO

 

IF OBJECT_ID('DeadLockTable1') IS NOT NULL

DROP TABLE DeadLockTable1

IF OBJECT_ID('DeadLockTable2') IS NOT NULL

DROP TABLE DeadLockTable2

GO

CREATE TABLE DeadLockTable1

(

   ID INT IDENTITY(1,1) PRIMARY KEY,

   NAME VARCHAR(50)

)

CREATE TABLE DeadLockTable2

(

   ID INT IDENTITY(1,1) PRIMARY KEY,

   NAME VARCHAR(50)

)

GO

-- Insert some testing data

INSERT INTO DeadLockTable1

SELECT NAME

FROM sys.objects

 

INSERT INTO DeadLockTable2

SELECT NAME

FROM sys.objects

Run this script to create tables and data.

Second, to create two trasactions in two scripts, don't execute it when you finish it.

------------------------------------

-- Script 2

------------------------------------

USE SSISDemoDB

Go

 

BEGIN TRANSACTION

   -- Update table 1 first

   UPDATE DeadLockTable1

   SET NAME = 'Test1'

   WHERE ID > 0

  

   -- Pause 5 seconds

   WAITFOR DELAY '00:00:05'

 

   -- Update table 2

   UPDATE DeadLockTable2

   SET NAME = 'Test2'

   WHERE ID > 0

COMMIT TRANSACTION

GO

----------------------------------------

-- Script 3

----------------------------------------

USE SSISDemoDB

Go

BEGIN TRANSACTION

  

   -- Updata table 2

   UPDATE DeadLockTable2

   SET NAME = 'Test2'

   WHERE ID > 0

   -- Pause 5 seconds

   WAITFOR DELAY '00:00:05'

   -- Update table 1

   UPDATE DeadLockTable1

   SET NAME = 'Test1'

   WHERE ID > 0

 

COMMIT TRANSACTION

GO

Third, open SQL Profiler and new a trace

You can output the trace to a file or save data into a table.

We need to add some event for this trace, make sure the below event you have selected.

Deadlock Graph
Lock:Deadlock
Lock:Deadlock Chain
RPC:Completed
SQL:BatchCompleted
SQL:BatchStarting

 

Execute Script 2 and Script 3.

After 5 seconds, the message from script 2 show successfully updated information.
But script 3 return an error information -

Msg 1205, Level 13, State 51, Line 13
Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In this case, there's a Deadlock caused by your query.

Find the Deadlock Graph on Profiler and you can see a query flow which can reflect the reason of the Deadlock and system end the left trasaction and keep the right one.

 

 

 

It helps us know which statements caused the Deadlock and we need to analyze the reason and tune our scripts. The Deadlock wouldn't impact the process, because SQL Server will choose a connection to end it.

Before the application go live, we may use SQL Profiler to trace the performance and check if there's any Deadlock in system. You can save the trace information into a table and use SSIS Package or SQL Job to inform team there's something unexpected in our system.

ISSUE#0001:0x80041003&0x80041010 Cannot connect to WMI provider.You do not have permission or the server is unreachable.

I got an error when I want to open my SQL Server 2008 R2 Configuration Manager. Just few month ago I upgraded my SQL Server 2008 R2 to SQL Server 2012, I am not sure if this operation impacted it.

 

Error Information

"Cannot connect to WMI provider.You do not have permission or the server is unreachable.Not that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.Invalid class [0x80041010]".

Find a solution by Google..

1. Find the *.mof file under your SQL Server 100(Version 2008) or 110(Version 2012)
2. Use command "mofcomp"...

But I got another error:
An error occurred while processing item 1.... Error Number: 0x80041003, Facility: WMI Description: Access denied Compiler returned error 0x80041003

Googling it..and find the reason that I didn't run this cmd as Administrator role, so correct it..

Right click cmd.exe and Run as administrator

 

 

Again..

Done!!!

Now I can open my SQL Server Configuration Manager successfully.

When I tried to open my SQL Server 2012 Configuration Manager, I saw the same error, then I just followed the above steps and solved the problems.

SSIS Step by Step 006 - Use XSLT to transform XML documents

Requirement & Preface

XSL stands for EXtensible Stylesheet Language, and is a style sheet language for XML documents.
XSLT stands for XSL Transformations. We have an XML file and want to transfer it to another style according to user-defined xslt document.

 

What's new in this post ?

1. How to use XML task to transfer XML style

Steps
1. Create a new package -CH04-DEMO01-XMLTask.dtsx

Drag an XML task to control flow.

2.  Edit it and create three XML file connection manager and configure then as below:

Operation Type - XSLT, we will use standard xslt document to transfer target XML file.

Source - U01-CH04-002-XMLDemo.xml

Codes:

<?xml version="1.0"?>

-<extract date="2007-12-05"> -<counters> -<counter name="server1" category="dispatcher"> <runtime>6</runtime> <queue>3</queue> <maxrequest>8</maxrequest> <color>blue</color> -<host> <name>svo2555</name> <path>\\dispatcher</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> -<counter name="server1" category="gateway"> <runtime>1</runtime> <queue>10</queue> <maxrequest>10</maxrequest> <color>purple</color> -<host> <name>svo2555</name> <path>\\gateway</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> </counters></extract>

Schema of xslt - U01-CH04-002-XMLSchema.xslt

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

        <xsl:output method="xml" indent="yes"/>

        <xsl:template match="/extract">

                <xsl:variable name="extractDate" select="/extract/@date" />   

                <counters>

                        <xsl:for-each select="counters/counter">

                                <counter>           

                                        <extractDate><xsl:value-of select="$extractDate"/></extractDate>           

                                        <category><xsl:value-of select="@category"/></category>           

                                        <name><xsl:value-of select="@name"/></name>           

                                        <runtime><xsl:value-of select="runtime"/></runtime>           

                                        <queue><xsl:value-of select="queue"/></queue>           

                                        <maxrequest><xsl:value-of select="maxrequest"/></maxrequest>           

                                        <color><xsl:value-of select="color"/></color>           

                                        <hostName><xsl:value-of select="host/name"/></hostName>           

                                        <path><xsl:value-of select="host/path"/></path>           

                                        <lastaccessed><xsl:value-of select="host/lastaccessed"/></lastaccessed>       

                                </counter>   

                        </xsl:for-each>   

                </counters>

        </xsl:template>

</xsl:stylesheet>

3. Execute package and get the output XML file

Get all SSIS Step by Step, refer to SIMON'S SSIS Step by Step Please let me know if you have any questions about this blog. Contact to me by email - simonlvpin@yahoo.com Or Skype - simonlvpin

SSIS Step by Step 005 - Load data from XML file

Requirement & Preface

Load all data from an XML file.
The XML source is from the output of previous demo.
http://simonlv.blogspot.com/2012/08/ssis-step-by-step-004-output-xml-data.html

What's new in this post ?

1. How to load data from an XML file.

Steps

1. Create a new package  - CH03-DEMO02-XMLToData.dtsx and add this two component in control flow.

2.  In EXT_TruncateOrders, truncate table order first.

3.  Data flow Task - DFT_XML_DataToDB

Added a XML source in data flow task.
Notice - there's no XML destination task in data flow task but it has XML source.

4. In XML Source editor, the data access mode is 'XML file location', we select the location which is ouputed by previous demo.

The XSD location will be generated automaticlly when you select an XML location.

See the columns of XML file.

5.  ADO.NET Destination

 

 

6. Execute package and check the results

Get all SSIS Step by Step, refer to SIMON'S SSIS Step by Step Please let me know if you have any questions about this blog. Contact to me by email - simonlvpin@yahoo.com Or Skype - simonlvpin

SSIS Step by Step 004 - Output XML Data from database

SSIS Step by Step 004 - Output XML Data from database

Requirement & Preface

There's a table Order, we need to output its records to an XML file.

What's new in this post ?

1. How to output data to an XML file?

Steps

1. Create a new package - CH03-DEMO01-DataToXML.dtsx
We need to create two variables as screenshot shows:

FilePath - to define the location of output XML file.
OrderXMLString - All data from Order table will be saved as a XML string.

2. EST_DataToXML Task Editor

The ResultSet should be XML.
The SQL Statement should be:

SELECT [Order].OrderID,
       [Order].CustomerID,
       [Order].EmployeeID,
       [Order].Freight,
       [Order].OrderDate,
       [Order].RequiredDate,
       [Order].ShipAddress,
       [Order].ShipCity,
       [Order].ShipCountryRegion,
       [Order].ShipCountryRegionRegion,
       [Order].ShipName,
       [Order].ShipPostalCode,
       [Order].ShipRegion,
       [Order].ShipVia,
       [Order].ShippedDate
FROM dbo.[Order] AS [Order] FOR XML AUTO

3. Result Set Pane

We defined a string-type variable to this XML string returned by SQL query.

Please see this SQL

The reason why I process it in this way, because there's no XML destination tool in data flow. So I have to save all data into a string, then use script task to output it to an XML file.

4. Script task

Codes inside:

public void Main()

{

            string content = Dts.Variables["User::OrderXMLString"].Value.ToString().Replace("<ROOT>", "<?xml version=\"1.0\" encoding=\"utf-8\" ?><allOrders>").Replace("</ROOT>", "</allOrders>");

            string filePath = Dts.Variables["User::FilePath"].Value.ToString();

            StreamWriter writer = new StreamWriter(filePath);

            writer.WriteLine(content);

            writer.Close();

            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;

}

5. Execute this package and check the output XML file

Segements of outputed XML file -

This output XML file will be the source of next demo.


Get all SSIS Step by Step, refer to SIMON'S SSIS Step by Step Please let me know if you have any questions about this blog. Contact to me by email - simonlvpin@yahoo.com Or Skype - simonlvpin

SSIS Step by Step 003 - Load multiple flat files in a loop container

Requirement & Preface

In last post (SSIS Step by Step 002 - Output data to multiple flat files) , we output data from database to multiple flat files.

In this post, we're going to load data from multiple flat files to database table back. 

We are going to learn:

1. How to use for each loop container in SSIS package.

2.  How to use Foreach File Enumerator

 

Testing files are generated by (SSIS Step by Step 002 - Output data to multiple flat files), about the address table schema you can find them in SSIS Step by Step 001 - Load data from a flat file

Here's a folder which contains many flat files, each file contains address information and all address records in a single file has the same city.

Source folder:

Destination table: Address

SSIS Package Framework Screenshot 

Steps:

1. Create a new SSIS package with a name - CH03_LoadBatchFile.dtsx

  

2. Create a package variable - FileName, it will be used to save the single file name in a loop.

3. Create a 'Execute SQL Task' with name - 'EST_TruncateCities', edit it .

    Configure the connection and SQL Statement.

    This task is to truncate address table before loading data. 

    

4. Add a 'Foreach Loop Container' and edit it.

    In Collection Pane - 

    Enumerator - Foreach File Enumerator

    Folder - Where the flat file locate

    Files - CHO2-Citys-*.txt         * means any characters. 

   In Variable Mappings Pane - Select package variable 'FileName' to map the value in loop. 

5. Drag a data flow task, and add below two components in data flow.

    Flat File Source - FF_SRC_Address

    OLE DB Destination

6. The source is from flat files, so edit 'FF_SRC_Address'.

     

    Browse and choose a source flat file, because the files which match the 'CHO2-Citys-*.txt' have the same file format, so we can choose any matched file first to create mapping columns in this editor.

Configure the properties of each column - please refer to Step by Step 001 - Load data from a flat file

7. Edit 'OLE DB Destination'

 8. Now we need to configure the flat file connection manager, the current flat file connection is tied to a specified file. We need to configure it with a new value which is from loop container - User::FlatFile in step 4.

Click 'CM_FF_AddressByCity' connection manager, and edit its expression.

Map connection string property  to package variable 'FileName'

9. Save package and run it.

Get all SSIS Step by Step, refer to  SIMON'S SSIS Step by Step

Please let me know if you have any questions about this blog.

Contact to me by email - simonlvpin@yahoo.com

Or Skype - simonlvpin