How to Fix Transaction Manager Connectivity Error in SSIS?

When we set Transaction Option to 'Required' in SSIS containers, often we get an error on Connection Manger

and data connection.

To work around this problem, follow these steps on the computer that Windows Server 2003 or Windows XP SP2 is installed on:

1.       Make sure that the Log On As account for the MSDTC service is the Network Service account. To do this, follow these steps:

a.       Click Start, and then click Run.

b.       In the Run dialog box, type Services.msc, and then click OK.

c.        In the Services window, locate the Distributed Transaction Coordinator service under Name in the right pane.

d.       Under the Log On As column, see whether the Log On As account is Network Service or Local System. 

If the Log On As account is Network Service, go to step 2. If the Log On As account is Local Systemaccount, continue with these steps.

e.       Click Start, and then click Run.

f.         In the Run dialog box, type cmd, and then click OK.

g.       At the command prompt, type Net stop msdtc to stop the MSDTC service.

h.       At the command prompt, type Msdtc –uninstall to remove MSDTC.

i.         At the command prompt, type regedit to open Registry Editor.

j.         In Registry Editor, locate the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC

registry key.

Delete this key.

k.       Quit Registry Editor.

l.         At the command prompt, type Msdtc –install to install MSDTC.

m.     At the command prompt, type Net start msdtc to start the MSDTC service.

Note that the Log On As account for the MSDTC service is set to Network Service account.

    Enable MSDTC to allow the network transaction. To do this, follow these steps:

 .         Click Start, and then click Run.

a.       In the Run dialog box, type dcomcnfg.exe, and then click OK.

b.       In the Component Services window, expand Component Services, expand Computers, and then expandMy Computer.

c.        Right-click My Computer, and then click Properties.

d.       In the My Computer Properties dialog box, click Security Configuration on the MSDTC tab.

e.       In the Security Configuration dialog box, click to select the Network DTC Access check box.

f.         To allow the distributed transaction to run on this computer from a remote computer, click to select theAllow Inbound check box.

g.       To allow the distributed transaction to run on a remote computer from this computer, click to select theAllow Outbound check box.

h.       Under the Transaction Manager Communication group, click to select the No Authentication Requiredoption. Set No Authentication Required on both the client and the remote systems.

i.         In the Security Configuration dialog box, click OK.

j.         In the My Computer Properties dialog box, click OK.

    Configure Windows Firewall to include the MSDTC program and to include port 135 as an exception. To do this, follow these steps:

 .         Click Start, and then click Run.

a.       In the Run dialog box, type Firewall.cpl, and then click OK

b.       In Control Panel, double-click Windows Firewall.

c.        In the Windows Firewall dialog box, click Add Program on the Exceptions tab.

d.       In the Add a Program dialog box, click the Browse button, and then locate the Msdtc.exe file. By default, the file is stored in the <Installation drive>:\Windows\System32 folder.

e.       In the Add a Program dialog box, click OK.

f.         In the Windows Firewall dialog box, click to select the msdtc option in the Programs and Services list.

g.       Click Add Port on the Exceptions tab.

h.       In the Add a Port dialog box, type 135 in the Port number text box, and then click to select the TCP option.

i.         In the Add a Port dialog box, type a name for the exception in the Name text box, and then click OK.

j.         In the Windows Firewall dialog box, select the name that you used for the exception in step j in thePrograms and Services list, and then click OK.

    Test pinging from the host server to the remote server, and from the remote server to the host server, using the netbios name (server name, without the domain). Microsoft Distributed Transaction Coordinator uses the netbios name, not the fully qualified domain name, to locate servers. If name resolution fails, distributed transactions will fail. If pings using the netbios name fails, refer to the following knowledge base article:

Forgot or Reset SQL Authentication 'sa' password

To reset password for an SQL Authentication login user, execute the following code:

Say for example, you forgot 'sa' password, then you can execute the below code to assign new password for 'sa' user:

EXEC SP_PASSWORD @new='900%sec', @loginame='sa'.
 
Now try connecting to SQL server with your 'sa' login and new password.

Error - Unable to Copy Control Tasks from SSIS Package

Sometime we may not able to copy Control Tasks from SSIS Package to another package and an error message will be displayed, in that occassion execute the below command script:

SSRS Error Code - rsReportServerDatabase Unavailable

When we try to connect to some of the SSRS report server, the foolowing data connection error is displayed. It is because of improper Server and ReportDatabase configuretion. To overcome this perform the below steps:

1. Navigate to Programs --> MS SQL Server XXXX --> Configuration Tools --> Report Service Configuration Manager.

2. Click on Database menu.

3. Check whether SQL Server Name and proper ReportServer database is configured. If not click on Change Database button and configure your server name and ReportServer database. The connection will work now.

Pentaho JDBC and JTDS connection for SQL Server

Pentaho JTDS Connection:

Pentaho JDBC Connection

New T-SQL functions in SQL Server 2012

New T-SQL functions in SQL Server 2012:

--PARSE()

--Parse Currency Symbol

SELECT PARSE('$100' AS MONEY USING 'EN-US') AS Currency

--Result = 100.00

--Parse DATETIME

SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS US_Date

--Result = 2010-12-13 00:00:00.0000000

--CONCAT()

SELECT CONCAT('Firstname',' ','Surname') AS MyName

--Result = Firstname Surname

--CHOOSE()

DECLARE @Letter INT = 4

SELECT CHOOSE(@Letter, 'A','B','C','D', 'E','F')

--Result = D

--IIF()

DECLARE @Letter INT = 5

SELECT IIF(@Letter % 2 > 0,'ODD','EVEN')

--Result = ODD

--Get Date for the provided yy,MM,dd

SELECT DATEFROMPARTS(1999,2,3)

--GET LastDate of Month

SELECT EOMONTH(GETDATE(),0)

SQL 2012 Snippet Feature to Create Syntax for Objects

SQL 2012 new feature includes Snippet to Create Syntax for adding new objects.
When a user click (Ctrl + K, Ctrl + X). The below snippets occurs providing option to select objects like Table, Stored Procedure, etc.