Published SSIS Package in SQL Server 2012

What happened to the export SSIS package?

Going from 2008R2 to 2012, SSIS packages began using the project deployment model.

The quick way to get a copy of the SSIS package out of SQL is to run SQL Server Management Studio as Administrator and expand the tree:

"Integration Services Catalogs" -> SSISDB -> ProjectFolderName -> Projects -> ProjectName

Then right-click the ProjectName and select "Export..."

Save and remember the name and path you save the *.ispac file.

Rename the ispac file with *.zip

Then can unzip the file.

Connecting with [NT AUTHORITY\SYSTEM]

Connecting with [NT AUTHORITY\SYSTEM] in older versions of SQL Server

D:\PSTools>psexec -i -s "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"


D:\PSTools>psexec -i -s "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.exe"

@@Servername is NULL

After applying CU8 for SQL 2012 SP2 @@Servername was null 

Somehow ended up in condition of no entry in sys.servers for server_id 0

select * from sys.servers where server_id=0

This returned 0 rows.  Can add the local server with:

sp_addserver '<servername>', local

Then had to restart the server for it to take effect

If it will be a while before can reboot, then suggest using 

serverproperty('ServerName')

The partner transaction manager has disabled its support for remote/network transactions

First verify the "Distribute Transaction Coordinator" Service is running on both database server computer and client computers

1.      Go to "Administrative Tools > Services"

2.      Turn on the "Distribute Transaction Coordinator" Service if it is not running


If it is running and client application is not on the same computer as the database server, on the computer running database server

1.      Go to "Administrative Tools > Component Services"

2.      On the left navigation tree, go to 

"Component Services > Computers > My Computer"  (you may need to double click and wait as some nodes need time to expand)

3.      Right click on "My Computer", select "Properties"

4.      Select "MSDTC" tab

5.      Click "Security Configuration"

6.      Make sure you check "Network DTC Access", "Allow Remote Client", "Allow Inbound/Outbound", "Enable TIP"  (Some option may not be necessary, have a try to get your configuration)

7.      The service will restart

8.      BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK

On your client computer use the same above procedure to open the "Security Configuration" setting, make sure you check "Network DTC Access", "Allow Inbound/Outbound" option, restart service and computer if necessary.

On you SQL server service manager, click "Service" dropdown, select "Distribute Transaction Coordinator", it should be also running on your server computer.

Oracle InstantClient error

The setup routines for the Oracle ODBC client in instantclient64_12_1 could not be loaded. System error code 126: The denoted module was not found. (C:\Program Files\Oracle\instantclient64_12_1\SQLORAS32.DLL).


Download and install the Microsoft Visual C++ 2010 Redistributable Package:

http://www.microsoft.com/en-us/download/confirmation.aspx?id=14632

The problem is not with the SQLORA dlls, but the modules that those ddls refer to (missing from a default Windows installation).

http://serverfault.com/questions/555972/cannot-create-oracle-odbc-source-on-64bit-windows-7

Open cmd in folder

Had an issue where on some server clicking a shortcut to cmd did not open to a path specified in "Start in:" parameter.   Work around was to add parameter to command line.

cmd /K "cd /d d:\scripts\"

Agent Job Execution

AD User executing agent job needs to have SQLAgeUserRole in msdb and be owner of job.

https://technet.microsoft.com/en-US/library/ms188283(v=SQL.110).aspx