Editing Published SSIS Package in SQL Server 2012

Editing Published SSIS Package in SQL Server 2012

Many Thanks  :- http://www.mssqlgirl.com/editing-published-ssis-package-in-sql-server-2012.html


So, you have inherited a set of SQL Server 2012 Integration Services projects that have been deployed on a server. Your company do not use source control so you can’t easily see what was last published (Oh no!!). Or, you have deployed something on the server but you are not sure if it is the right version. Bottom line is you want to inspect or to edit a package that is already published on the server. Unfortunately at the moment  there is no way of just quickly downloading that single SSIS package.
SQL Server 2012 Integration Services now is now making use of  Project Deployment Model. For more information, read it here. Back to the issue at hand, if you want to get a copy of what is in the production, in summary, there are two “easy” ways of doing so.

A. Creating a new project in SQL Server Data Tools (SSDT) and import the project from existing catalog.

B. Exporting the project into ispac file via SQL Server Management Studio (SSMS).

Caveat: Both these options require access to the project on the server.

Option A: New Project

1. In the New Project dialog box of SSDT, browse to Installed Templates > Business Intelligence > Integration Services on the left navigation pane.
2. Choose Integration Service Import Project Wizard option, enter the Name, Location and Solution Name accordingly. Then click OK to proceed to the next step.

3. Click Next on the Integration Services Import Project Wizard window

4. On the Select Source dialog, choose Integration Services Catalog. Then provide the details of the project on the server that you wish to import.

5. Follow the rest of the instruction.

6. Once the project is created, you can browse to the package(s) that you wish to inspect. In my example here, I have  4 connection manager files and 9 dtsx files.

This option is one of the safest / simplistic ways of getting SSIS project from the server. All the components in the Project, such as Project Variables and Project Connection Managers are kept in tact.

Option B: The shortcut

1. Navigate to the SSIS project on SSMS; i.e.  [Server Name] > Integration Services Catalogs > SSISDB > [Project Folder] > Projects > [Project Name] as shown on the picture below.

2. Right click on the [Project Name], then choose Export….
3. Choose the destination folder on “Save As” to save the .ispac file
4. Navigate to the folder where the file has been saved and rename the file extension to .zip file.
5. Browse the zip file and you should be able to see the .dtsx file(s).  Please note that in this example, I have 4 connection manager files and 9 dtsx files.

6. Now you can extract the desired .dtsx file(s) from the zip file.
Note: If the server is in Production, you could ask your DBA to export the .ispac file of the project (Step 1 & 2). Then you can continue with Step 3.
Special thanks to Josh Fennessy (b | t), for providing Option B.

Would it work for SSDT Visual Studio 2012?

Update – 6 March 2013: Both options work for Visual Studio 2012 too. (Read Microsoft announcement on SQL Server Data Tools – Business Intelligence for Visual Studio 2012). At the time of writing (a few minutes of exploring SSDT Visual Studio 2012), everything works the same for Option A. There is a major face-lift with monochrome look.

Wrap Up

Integration Services in SQL Server 2012 introduces Project Deployment Model. This means that accessing a single SSIS package that has been published from the server is not as trivial. This article describes two ways of accessing the desired package(s) or the project as a whole from a server. Option A, which is importing the package from the Integration Services Catalogs is the safest way. Option B, is a shortcut method that is recommended only for advanced level users.
This article does not describe how the pros and cons of the new Integration services Project Deployment Model as it is covered in a couple of blogs as listed on the Further Reading section.

WMI Provider Error - Access is denied - while changing SQL Server account password

To give this right:
Login with administrative privileges to SQL Cluster nodes.
Click Start, type mmc in the Search programs and files box, and then press ENTER.
On the File menu, click Add/Remove Snap-in.
Under Available snap-ins, double-click WMI Controls, and then:

• Select the computer that you manage. Connect to: select Local Computer, click Finish. Click OK.
• On Console Root tree, select WMI Controls, right click, select Properties, then go to Security tab, locate root\Microsoft\SqlServer\ ComputerManagement10 (or 11 for SQL Server 2012) namespace then click Security.
• Add new domain user to Group or Users Names
• Select new domain user in Group or Users Names and Allow Read Security for the Permissions of new account, click OK.

Reset SA Password For Multiple Instance

Power Shell Script  :-

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
Foreach ($list In $(Get-Content -Path p:\serverList.txt)) {
   $server = $list.split(":")
   $srvObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $server[0]
   $srvObject.Logins.Item('sa').ChangePassword($server[1])
   $srvObject.Logins.Item('sa').Alter()
}


The above uses the file "ServerList.txt", where we neeed to store the instance names and corresponding password in the following pattern,

[Instance Name]:{PASSWORD}
[Instance Name2]:{PASSWORD}
......

no connection could be made because the target machine actively refused SQL Server OLAP

ERROR :- No connection could be made because the target machine actively refused it sql server olap

Event Viewer :- Failed to initialize SQLSQM timer. One of the following can be the source of the problem: registry doesn't contain all necessary information, server instance name can not be retrieved correctly, failure during timer creation, etc.


Solution :- 
 
The only supported way to connect to a clustered SSAS instance is to use the “virtual server name” (also called network name). Usage of the instance name like in  “VirtualServerName\InstanceName” is not supported! One reason being that all clustered instances will always run on port 2383 and thus we don’t need to specify the instance name. The other reason being that the SSAS part of SQL Browser service functionality (OLAP redirector service) is not cluster-aware and we cannot guarantee that the content of “msmdredir.ini” is always updated correctly.
In this context “not supported” does not mean that connectivity via instance name does not work. In fact it usually does work. But we cannot guarantee that it will always work in a stable fashion. Therefore our strong recommendation is to always use “VirtualServerName” (=Network name) for establishing connections to a clustered SSAS instance.



More Info :- http://blogs.msdn.com/b/as_emea/archive/2014/01/08/connecting-to-clustered-ssas-instances.aspx

Introducing LocalDB, an improved SQL Express

http://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx