tag:blogger.com,1999:blog-319424012024-03-18T12:47:53.063+01:00Jan D'HondtSQL Server - Reporting services - Integration services - Analysis Services - WCF - LightswitchJan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.comBlogger221125tag:blogger.com,1999:blog-31942401.post-74603734207906286892020-10-28T15:41:00.003+01:002020-10-28T15:42:52.641+01:00Automate deletion of SSIS packages from SSIS packagestore in SMDB<p style="text-align: left;">Once SSIS projects have been converted from Package Deployment to Project Deployment in Visual Studio, you may want to delete the deployed packages from the SSIS packagestore in MSDB on the SSIS servers. You can delete packages from the MSDB store one by one with SSMS. But it can be a tedious job if you have hundreds of packages on several servers (Development, Testing, Production environment). We can generate a script to delete the packages with the DTUTIL utility. </p><p style="text-align: left;">Here is a very simple example</p><p style="text-align: left;">Before:</p><p style="text-align: left;"></p><ul style="text-align: left;"><li>SSIS project in Visual Studio in package deployment mode</li><ul><li>2 packages PackageA & PackageB</li></ul><li>SSIS Servers (development and production)</li><ul><li>MSDB Package store</li></ul></ul>After:<p></p><p style="text-align: left;"></p><ul style="text-align: left;"><li>SSIS project in Visual Studio in project deployment mode</li><ul><li>2 packages PackageA & PackageB</li></ul></ul><p></p><ul style="text-align: left;"><li>SSIS Servers (development and production)</li><ul><li>SSISDB Integration Services catalog</li></ul></ul><p style="text-align: left;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://lh3.googleusercontent.com/-eu_OazsRGHI/X5l6KxXbNlI/AAAAAAAAOSQ/2l3TGIs5gYwNCvPwQ8Z2cCQ9eaZfOtlDgCLcBGAsYHQ/image.png" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="807" data-original-width="690" height="566" src="https://lh3.googleusercontent.com/-eu_OazsRGHI/X5l6KxXbNlI/AAAAAAAAOSQ/2l3TGIs5gYwNCvPwQ8Z2cCQ9eaZfOtlDgCLcBGAsYHQ/w483-h566/image.png" width="483" /></a></div><div class="separator" style="clear: both; text-align: left;">Once the packages have been deployed in the SSISDB catalog, there is no need to keep them any longer in the MSDB Package store.</div><div class="separator" style="clear: both; text-align: left;">The following SQL script will generate a list of all packages in a given subdirectory and generate the SQL script to delete all of them. </div><div class="separator" style="clear: both; text-align: left;"><br /></div><p></p><div style="text-align: left;"><span style="font-family: courier; font-size: x-small;">declare @SsisServers varchar(255);<br /></span><span style="font-family: courier; font-size: x-small;">declare @Parentfolder varchar(255);<br /></span><span style="font-family: courier; font-size: x-small;">declare @subfolders varchar(255);<br /></span><span style="font-family: courier; font-size: x-small;">declare @DeleteSubfolder bit;</span><span style="font-family: courier; font-size: x-small;"><br /></span><span style="font-family: courier; font-size: x-small;">SET @SsisServers = 'ServerDEV;ServerTST;ServerPRD';<br /></span><span style="font-family: courier; font-size: x-small;">set @Parentfolder = 'Genledger';<br /></span><span style="font-family: courier; font-size: x-small;">set @subfolders = 'Project1;Project2';<br /></span><span style="font-family: courier; font-size: x-small;">set @DeleteSubfolder = 1;</span><span style="font-family: courier; font-size: x-small;"><br /></span><span style="font-family: courier; font-size: x-small;">DECLARE @ts TABLE ( SsisServer varchar(255) );<br /></span><span style="font-family: courier; font-size: x-small;">insert into @ts SELECT * FROM STRING_SPLIT (@SsisServers, ';');</span><span style="font-family: courier; font-size: x-small;"><br /></span><span style="font-family: courier; font-size: x-small;">DECLARE @tf TABLE ( subfolder varchar(255) );<br /></span><span style="font-family: courier; font-size: x-small;">insert into @tf SELECT * FROM STRING_SPLIT (@subfolders, ';');</span><span style="font-family: courier; font-size: x-small;"><br /></span><span style="font-family: courier; font-size: x-small;">SELECT os.script<br /></span><span style="font-family: courier; font-size: x-small;">FROM @ts AS ts<br /></span><span style="font-family: courier; font-size: x-small;">OUTER APPLY (<br /></span><span style="font-family: courier; font-size: x-small;">SELECT o.script<br /></span><span style="font-family: courier; font-size: x-small;">FROM @tf AS t<br /></span><span style="font-family: courier; font-size: x-small;"> OUTER APPLY ( <br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>SELECT ('DTUTIL "/SQL ' + @Parentfolder + '\' + t.subfolder + '\' + p.[name] + ' /DELETE /SOURCESERVER ' + ts.SsisServer +'"') AS script<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>FROM msdb.dbo.sysssispackages p<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>INNER JOIN msdb.dbo.sysssispackagefolders f<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>ON f.folderid = p.folderid<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>INNER JOIN msdb.dbo.sysssispackagefolders pf<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>ON f.parentfolderid = pf.folderid<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>WHERE pf.foldername =@Parentfolder<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>and f.foldername =t.subfolder<br /></span><span style="font-family: courier; font-size: x-small;"> ) o<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>) os;</span><span style="font-family: courier; font-size: x-small;"><br /></span><span style="font-family: courier; font-size: x-small;">If @DeleteSubfolder <> 0<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>SELECT ('DTUTIL "/SOURCESERVER "' + ts.SsisServer + '" /FDELETE SQL;' + @Parentfolder + ';' + t.subfolder) AS script<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>FROM @tf t<br /></span><span style="font-family: courier; font-size: x-small;"><span style="white-space: pre;"> </span>cross join @ts ts</span><span style="font-family: courier; font-size: x-small;"><br /></span><span style="font-family: courier; font-size: x-small;">go</span></div><div style="text-align: left;">The output of the script can then be copied into a Powerquery script:</div><div style="text-align: left;"><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project1\PackageA /DELETE /SOURCESERVER ServerDEV"</span></div><div style="text-align: left;"><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project1\PackageB /DELETE /SOURCESERVER ServerDEV"</span></div><div style="text-align: left;"><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project2\PackageC /DELETE /SOURCESERVER ServerDEV"</span></div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project2\PackageD /DELETE /SOURCESERVER ServerDEV"</span></div><div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project1\PackageA /DELETE /SOURCESERVER ServerTST"</span></div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project1\PackageB /DELETE /SOURCESERVER ServerTST"</span></div><div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project2\PackageC /DELETE /SOURCESERVER ServerTST"</span></div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project2\PackageD /DELETE /SOURCESERVER ServerTST"</span></div></div></div><div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project1\PackageA /DELETE /SOURCESERVER ServerPRD"</span></div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project1\PackageB /DELETE /SOURCESERVER ServerPRD"</span></div><div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project2\PackageC /DELETE /SOURCESERVER ServerPRD"</span></div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SQL GenLedger\Project2\PackageD /DELETE /SOURCESERVER ServerPRD"</span></div></div></div><div><div><span style="font-family: courier; font-size: x-small;">#delete subfolder</span></div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SOURCESERVER ServerDEV /FDELETE SQL;GenLedger;Project1"</span></div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SOURCESERVER ServerDEV /FDELETE SQL;GenLedger;Project2"</span></div><div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SOURCESERVER ServerTST /FDELETE SQL;GenLedger;Project1"</span></div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SOURCESERVER ServerTST /FDELETE SQL;GenLedger;Project2"</span></div></div></div><div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SOURCESERVER ServerPRD /FDELETE SQL;GenLedger;Project1"</span></div><div><span style="font-family: courier; font-size: x-small;">DTUTIL "/SOURCESERVER ServerPRD /FDELETE SQL;GenLedger;Project2"</span></div></div><div><br /></div><div>Run the script from the Powershell ISE</div><div><h1 class="recipe-title font-bold h2-text primary-dark" style="-webkit-font-smoothing: antialiased; background-color: white; border: 0px; color: #232323; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: 1.2; margin: 0px 0px 8px; overflow: hidden; padding: 0px; text-overflow: ellipsis; text-shadow: 0px 0px 0px; vertical-align: baseline;"><div class="separator" style="clear: both; font-family: europa-boldregular, sans-serif; font-size: 40px; text-align: center;"><a href="https://lh3.googleusercontent.com/-n41H016wA4c/X5mCx9B7IVI/AAAAAAAAOSc/hsWAwcbeSeQbRb-Y0WPaAXwRKFm0xpfMwCLcBGAsYHQ/image.png" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="701" data-original-width="859" height="394" src="https://lh3.googleusercontent.com/-n41H016wA4c/X5mCx9B7IVI/AAAAAAAAOSc/hsWAwcbeSeQbRb-Y0WPaAXwRKFm0xpfMwCLcBGAsYHQ/w482-h394/image.png" width="482" /></a></div><div style="font-family: europa-boldregular, sans-serif; font-size: 40px; text-align: left;"><br /></div><div style="text-align: left;"><span style="font-family: inherit; font-size: small;">That's all folks!</span></div></h1></div></div>Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com1tag:blogger.com,1999:blog-31942401.post-66213449609223646542015-12-23T11:47:00.003+01:002015-12-28T21:08:29.059+01:00SQL Agent job to run a task on a remote system that has SQL Server instance<h2>
Problem</h2>
<div>
The last step of a SQL Server Agent job has to run a windows task on a remote system that has no SQL Server instance installed.</div>
<div>
<h3>
Description</h3>
<div>
A SQL Server has to execute a number of database tasks by means of a SQL Server Agent job. This job is scheduled to run daily at a given time. The last step should execute a task on a remote Windows server. This remote server does not have SQL Server instance installed. A simple solution would be to schedule a Windows task on the remote server, but we will never be certain that the SQL Agent job finishes before the remote server starts the task, unless we schedule the windows task a lot later than the estimated end of the SQL job. We prefer the whole process to finish in as little time as necessary.<br />
<div>
Therefore, the last step of the job should trigger the execution of the task on the remote system. The maintenance on the remote system is kept simpler because there is no windows schedule to maintain that has to be aligned with the SQL Agent schedule. Also the timespan to finalize the complete job is kept as short as possible, compared to keeping a large enough timespan between the SQL schedule and the Windows task schedule.</div>
</div>
<h2>
Solution</h2>
<div>
The executable on the remote system can be run with a Windows user account that is already used as a Windows service account on the remote system. This account has a password that does not expire and it has sufficient rights to run the executable.</div>
<div>
Powershell is available on both servers and can be used to invoke remote commands. Powershell can be scripted as a job step in SQL Server Agent.</div>
<div>
There is a powershell script on the remote computer to run the executable.</div>
<div>
What I need to do is to add a job step that invokes the powershell script on the remote computer with the credentials of the Windows service account.<br />
<h4>
Remote script setup</h4>
</div>
The script that contains the command to run the executable on the remote system is saved as<span style="font-family: Times, Times New Roman, serif;"> C:\Scripts\Powershell\RunSomeExecutable.ps.</span><br />
<span style="font-family: Times, Times New Roman, serif;">As proof of concept the script contains some lines to create a file in another directory of the remote server.</span><br />
<span style="font-family: Courier New, Courier, monospace;">$text = 'Hello World'</span><br />
<span style="font-family: Courier New, Courier, monospace;">$text | Out-File 'D:\Data\Test\file.txt'</span><br />
<h4>
Password encryption</h4>
<div>
The powershell command executed by the job step could contain the password hardcoded like this:</div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$pw = convertto-securestring -AsPlainText -Force -String tH1s1sAPa$$w0rd</span></div>
<span style="font-family: Courier New, Courier, monospace;">$user = "domainname\username"</span><br />
<div>
<span style="font-family: Courier New, Courier, monospace;">$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $</span><span style="font-family: 'Courier New', Courier, monospace;">user</span><span style="font-family: Courier New, Courier, monospace;">,$pw</span><br />
If you want to encrypt the password, you have to encrypt it on the remote server and then enter the encryption string in the Powershell command in the Job step.</div>
</div>
<div>
<span style="font-family: Courier New, Courier, monospace;"># Enter the Credentials details </span><br />
<span style="font-family: Courier New, Courier, monospace;"># DO THIS ON EVERY SQL SYSTEM locally logged in!!! Encryption will be different for every server</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">$password = read-host -prompt "Enter your Password" </span><br />
<span style="font-family: Courier New, Courier, monospace;">write-host "$password is password" </span><br />
<span style="font-family: Courier New, Courier, monospace;">$secure = ConvertTo-SecureString $password -force -asPlainText </span><br />
<span style="font-family: Courier New, Courier, monospace;">ConvertFrom-SecureString $secure |Out-File C:\Users\username\Documents\Powershell\Encryptpw.txt </span></div>
<div>
You can use the Windows Powershell ISE to create the code and run it.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNC0X9T2QAPK_LRDYNFKB0VkRfNTcF5cgCMwBazW3l7PsXgbGk3t2jsrSdd8nGBVFDj0EvOLXWS95hMSXVbKt5Rkh0EE1YW2uNzAzoBIe3crfkntdEs228xgWy1mQAdNG5aPM/s1600/EncryptPw.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="156" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNC0X9T2QAPK_LRDYNFKB0VkRfNTcF5cgCMwBazW3l7PsXgbGk3t2jsrSdd8nGBVFDj0EvOLXWS95hMSXVbKt5Rkh0EE1YW2uNzAzoBIe3crfkntdEs228xgWy1mQAdNG5aPM/s320/EncryptPw.JPG" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<span style="font-family: Times, Times New Roman, serif;"><br /></span>
<span style="font-family: Times, Times New Roman, serif;">Open Encryptpw.txt, it will contain a single line like this</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">01000000d08c8ddf0115d1118c7a00c04fc297eb01000000f4dc057c2362784b850a195b175f2e520000000002000000000003660000c000000010000000c5b288e86f6e31e4c8245f1ebbf12f070000000004800000a00000001000000084793ddf17bc3c116f3d991e469f188718000000405af26d131debb66922c4381a81edef6b996f030ac5165b14000000fa6a327ea9da87e868b491c18b8393e89002d713</span><br />
<span style="font-family: Times, Times New Roman, serif;">Copy the encrypted contents to the clipboard, then paste into the first statement of the Job step command line, to assign it to the $encryptpw variable (see next setup step)</span></div>
<h4>
</h4>
<h4>
Job step setup with encrypted password</h4>
<div>
Step Name:<br />
Type: Powershell<br />
Run As: SQL Server Agent Service Account<my-powershell-proxy></my-powershell-proxy><br />
Command:<br />
<span style="font-family: 'Courier New', Courier, monospace;">$encryptpw = "01000000d08c8ddf0115d1118c7a00c04fc297eb01000000f4dc057c2362784b850a195b175f2e520000000002000000000003660000c000000010000000c5b288e86f6e31e4c8245f1ebbf12f070000000004800000a00000001000000084793ddf17bc3c116f3d991e469f188718000000405af26d131debb66922c4381a81edef6b996f030ac5165b14000000fa6a327ea9da87e868b491c18b8393e89002d713" </span><br />
<span style="font-family: Courier New, Courier, monospace;">$pw = ConvertTo-SecureString -string $e</span><span style="font-family: 'Courier New', Courier, monospace;">ncryptpw</span><br />
<span style="font-family: Courier New, Courier, monospace;">$user = "domainname\username"</span><br />
<span style="font-family: Courier New, Courier, monospace;">$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $user,$pw</span><br />
<span style="font-family: Courier New, Courier, monospace;">$computer = "remotecomputername"</span><br />
<span style="font-family: Courier New, Courier, monospace;">$session = new-pssession -computername $computer -credential $cred</span><br />
<span style="font-family: Courier New, Courier, monospace;">Invoke-Command -Session $session -ScriptBlock {Invoke-Expression "C:\Scripts\Powershell\RunSomeExecutable.ps1" }</span></div>
<div>
Save the job-step and the job and then run it<br />
Check on the remote server if the task was executed. In my example: if the file has been created.<br />
<h4>
SQLAgent proxy</h4>
You should test the job in a scheduled run as well, because the job will run with the operator 'SQL Server Agent Service Account'. If this account does not have sufficient rights to run the powershell script remotely, you will need to create a SQL Agent proxy with rights to run the powershell script.</div>
</div>
Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com114tag:blogger.com,1999:blog-31942401.post-68040816687299713632015-12-18T14:09:00.001+01:002015-12-18T14:09:40.106+01:00Do not forget to enable Database Mail in SQL Server Agent too After setting up Database Mail on a SQL Server, do not forget to enable the mail feature of SQL Server Agent.<br />
<br />Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-60009318090497153312015-11-30T12:01:00.000+01:002015-11-30T12:53:50.885+01:00SQL Server reduce the size of an overdimensioned transaction log fileuse master<br />
go<br />
<br />
SELECT name, log_reuse_wait_desc FROM sys.databases<br />
<br />
USE Mydatabase<br />
go<br />
<br />
CHECKPOINT<br />
<br />
DBCC SHRINKFILE (N'MESTBIDW_DEV_log' , 48000)<br />
GO<br />
<br />
DBCC SQLPERF(LOGSPACE) --Optional -- just to see how much space<br />
DBCC LOGINFO --Optional<br />
<div>
<br /></div>
Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-41197403360536762752015-05-28T13:43:00.001+02:002015-05-28T13:43:51.734+02:00How to install the Notepad++ language file for the PowerQuery formula languageThe PowerQuery product is explaind here: <a href="https://support.office.com/en-us/article/Introduction-to-Microsoft-Power-Query-for-Excel-6E92E2F4-2079-4E1F-BAD5-89F6269CD605" target="">Introduction to Microsoft Power Query for Excel</a><br />
As of the moment of this writing, the editor for the “M” language does not
offer color syntax or intellisense. One way to aid in writing formulas is
provided by Matt Mason who made a Notepad++ language file for M.<br />
You can download the language file from his webpage <a href="http://www.mattmasson.com/2014/11/notepad-language-file-for-the-power-query-formula-language-m/" target="_blank">Notepad++ language file for the Power Query formula language
(“M”)</a><br />
The downloaded file is called “mlang_npp.xml”.<br />
Next we need to install the language file in Notepad++<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<ul>
<li>Open Notepad++</li>
<li>From the menu bar select Language > Define your language…</li>
<li>In the ‘user defined language’ dialog window, click on the ‘Import…’
button<br />In the ‘Open file’ dialog window, browse to your download folder, select
“mlang_npp.xml” and click ‘Open’.</li>
<li>The file should load and you see a success dialog box, click OK.</li>
<li>Close the ‘user defined language’ dialog window.</li>
<li>Close Notepad++ and reopen.</li>
<li>Open the Language menu, at the bottom you will see the “M” language choice,
below “Define your language…” (not in the M submenu).</li>
<li>With the “M” language selected, the code in your editor has syntax
highlighting and syntax folding.<br /><a href="http://2.bp.blogspot.com/-G0HNRNfLtnw/VWb-384U1wI/AAAAAAAAHuo/JrYuvthMRkQ/s1600/MinNotepadplusplus.PNG" imageanchor="1" style="clear: left; display: inline !important; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" height="45" src="http://2.bp.blogspot.com/-G0HNRNfLtnw/VWb-384U1wI/AAAAAAAAHuo/JrYuvthMRkQ/s320/MinNotepadplusplus.PNG" width="320" /></a></li>
</ul>
Most likely Microsoft will add syntax highlighting some time in the future.
For now the Notepad ++ solution is helpful to build complex “M” statements.Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-60406534974327710412015-05-04T10:31:00.001+02:002015-05-04T10:31:51.565+02:00Migrating my Postman collections from one MS-Windows system to another<p>Right now I’m in the process of migrating developer stuff from my old MS-Windows pc to a new one. On the old machine I’ve used the <a href="https://www.getpostman.com/docs" target="_blank">Postman tool</a> often to test and deploy new web-services. After having installed the tool on the new pc I wanted to copy my collections from the old pc. This article describes how to accomplish this.</p> <ul> <li>Start Postman on the old system</li> <li>My collections<br><a href="http://lh3.googleusercontent.com/-W3FaDxLbhR0/VUcuXrqochI/AAAAAAAAHsw/d-XZZeBIb9E/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgETqz0Cd_1TYmif0beTIQFw0ZRIJptK0_FlHtZsvQhOlowUOLAtys34-v7R5VBMAHTe4QdP4ML4nr1HQdRDx-otiEY50blC275Wg_U49EvpDeoghpr7dtz1IwsXnSFKGF8lV8/?imgmax=800" width="228" height="244"></a></li> <li>In the Menu Bar click on the Settings icon<br><a href="http://lh3.googleusercontent.com/-KsQjXGmwNks/VUcuZcJb2VI/AAAAAAAAHs8/ZJDSC4MSBDM/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-1of78MxtEeQ/VUcuZ7dwzuI/AAAAAAAAHtI/l6JjB78etuM/image_thumb.png?imgmax=800" width="244" height="60"></a></li> <li>In the Settings dialog window, click on the Data tab<br><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyHPzY866qfQTXGTYEj3zLgtpK1EuzWLiJLsUAgyeog9CUFtfK8XBkScdqCHQwG0r7pKMOxwJJG_Y42aDft3dyHehNnsesY81r-pfLYc-qP2ieERSnkPkTIgRSWVSg1vVoqZI/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-u-sc8is3yXM/VUcubYBXo0I/AAAAAAAAHtU/4H-MyEr52HU/image_thumb%25255B1%25255D.png?imgmax=800" width="244" height="151"></a></li> <li>Click on the Download button to download your collections in a dump file. The ‘Save As’ dialog window opens. Select a name and location on a file share that can be reached by the new PC.<br><a href="http://lh3.googleusercontent.com/-OtytxS7-EWc/VUcucXJt3ZI/AAAAAAAAHtg/BDapmVWJqEA/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-j-Q5vanJ84o/VUcudORBhTI/AAAAAAAAHto/wBrDJpQYER4/image_thumb%25255B2%25255D.png?imgmax=800" width="244" height="195"></a></li> <li>Save the file.</li> <li>Close the Postman tool on the old PC.</li> <li>Start the Postman tool on the new PC.</li> <li>In the Menu Bar click on the Settings icon.</li> <li>In the Settings dialog window, click on the Data tab.</li> <li>This time click on the ‘Select file’ button in the ‘Import Data’ section.</li> <li>In the ‘Open file’ dialog select the postman dump file you created in the file share.</li> <li>Click Open and that’s it. My collections are now available on the new PC.</li></ul> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-49733897842999804952015-04-22T11:58:00.001+02:002015-04-22T12:16:47.044+02:00Using Translations with Analysis Services and MS-Excel 2013<h3>Analysis Services Translations</h3> <p>Analysis Services (SSAS) provides an easy translation mechanism for a richer end-user experience. SSAS Translations allows for the translation of the captions of dimensions and facts as well as the translation of the content of dimension name columns. To easily show the cube in his preferred language the end-user has to use a tool that makes use of SSAS Translations. MS-Excel 2013 is such a tool. Depending on the installed display language of MS-Excel and provided the cube has a translation for the display language the cube and dimensions will automatically be shown in the default display language. Provided the necessary language packs for Office are installed, end-users can change the display language and create MS-Excel reports in the language they need, as long as the SSAS cube provides translations in the display language in MS-Excel. <h3>Setup dimension tables with different languages in SQL-Server</h3> <p>Every dimension table that has name columns to display the dimension members, should have additional name columns for every language end users request. There is always a default name column. <p>For instance: a dimension table called DimBlocking</p> <div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4"> <div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> [dbo].[DimBlocking](</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4">[BlockingId] [<span style="color: #0000ff">int</span>] <span style="color: #0000ff">NOT</span> <span style="color: #0000ff">NULL</span>,</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white">[BlockingName] [nvarchar](50) <span style="color: #0000ff">NOT</span> <span style="color: #0000ff">NULL</span>,</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">CONSTRAINT</span> [PK_DimBlocking] <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span> <span style="color: #0000ff">CLUSTERED</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white">(</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4">[BlockingId] <span style="color: #0000ff">ASC</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white">) <span style="color: #0000ff">ON</span> [<span style="color: #0000ff">PRIMARY</span>]</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4">) <span style="color: #0000ff">ON</span> [<span style="color: #0000ff">PRIMARY</span>]</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"> </pre><!--CRLF--></div></div><br /><p>This dimension has a key column ‘BlockingId’ and a name column ‘BlockingName’. This name column will serve as the default name column in the SSAS dimension specification.<br /><p>This table example has 2 rows of data<br /><br /><table cellspacing="0" cellpadding="0" width="262" border="1"><br /><tbody><br /><tr><br /><td valign="top" width="118"><br /><p>BlockingId</p></td><br /><td valign="top" width="142"><br /><p>BlockingName</p></td></tr><br /><tr><br /><td valign="top" width="118"><br /><p>0</p></td><br /><td valign="top" width="142"><br /><p>Non-blocking</p></td></tr><br /><tr><br /><td valign="top" width="118"><br /><p>1</p></td><br /><td valign="top" width="142"><br /><p>Blocking</p></td></tr></tbody></table><br /><p>To provide for other languages like Dutch and French, just add name columns of the same type and size as the default name column, e.g. BlockingNameNL for Dutch and BlockingNameFR for French.</p><br /><div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4"><br /><div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> [dbo].[DimBlocking](</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"> [BlockingId] [<span style="color: #0000ff">int</span>] <span style="color: #0000ff">NOT</span> <span style="color: #0000ff">NULL</span>,</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"> [BlockingName] [nvarchar](50) <span style="color: #0000ff">NOT</span> <span style="color: #0000ff">NULL</span>,</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"> [BlockingNameNL] [nvarchar](50) <span style="color: #0000ff">NOT</span> <span style="color: #0000ff">NULL</span>,</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"> [BlockingNameFR] [nvarchar](50) <span style="color: #0000ff">NOT</span> <span style="color: #0000ff">NULL</span>,</pre><!--CRLF--></div></div><br /><p>After adding the translation columns:<br /><table cellspacing="0" cellpadding="0" border="1"><br /><tbody><br /><tr><br /><td valign="top" width="151"><br /><p>BlockingId</p></td><br /><td valign="top" width="151"><br /><p>BlockingName</p></td><br /><td valign="top" width="151"><br /><p>BlockingNameNL</p></td><br /><td valign="top" width="151"><br /><p>BlockingNameFR</p></td></tr><br /><tr><br /><td valign="top" width="151"><br /><p>0</p></td><br /><td valign="top" width="151"><br /><p>Non-blocking</p></td><br /><td valign="top" width="151"><br /><p>Niet-blokkerend</p></td><br /><td valign="top" width="151"><br /><p>Non-bloquant</p></td></tr><br /><tr><br /><td valign="top" width="151"><br /><p>1</p></td><br /><td valign="top" width="151"><br /><p>Blocking</p></td><br /><td valign="top" width="151"><br /><p>Blokkerend</p></td><br /><td valign="top" width="151"><br /><p>Bloquant</p></td></tr></tbody></table><br /><h3>Setup MS-Excel to use the different languages provided by a cube in SSAS</h3><br /><h4>Office Language preferences</h4><br /><p>By default MS-Excel is installed with one Display language. This can be verified in the Options dialog window of MS-Excel, under the Language tab. In the screenshot below, Office 2013 was installed with English Display language.<br /><p><a href="http://lh3.googleusercontent.com/-OfV9sECeUZM/VTdwm2bD_SI/AAAAAAAAHow/4SYVJ6Ao7Nk/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-H-Hd9iXYmEY/VTdwnlHfnkI/AAAAAAAAHo0/oBBwmjr895M/image_thumb.png?imgmax=800" width="244" height="200"></a><br /><p>Install additional language packs from an Office language pack as needed. There are a few considerations when making translations for a country like Belgium where ‘Dutch (Belgium)’ is installed as Windows language, but the language pack for Office 2013 only offers Dutch from The Netherlands: ‘Nederlands (Nederland)’. Take care to setup SSAS translations to match the name of the Display language and not of the Editing language.<br /><h3>Setup dimension translations for different languages in SSAS</h3><br /><h4>Data Source View</h4><br /><p>Make sure that the Data Source View is refreshed to read the metadata for the new name columns. If your dimension table is based on a query, then adapt the query to read the new name columns. The table DimBlocking has been given the Friendly name ‘Blocking’.<br /><p><a href="http://lh3.googleusercontent.com/-hjXtyw-I5JU/VTdwoGJo4UI/AAAAAAAAHo8/Islb3HNy2nc/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9K5MGNLRuOn6fSpI4WtD1MHFzYLEP3DzUbocc2dtAyeLmDkhsu4CM6JjsuYflGP8BOdWsJAcTGta9N5-ygp5ulHFFemrqTjFP0IC16fvCJnFHciikrEd3TTcBXgah3HyyGjs/?imgmax=800" width="147" height="143"></a><br /><h4>Dimension designer - Structure</h4><br /><p>The dimension has a key and a name column. Most often a simple dimension with a key and a name has one Attribute, in this example called ‘Blocking’ with KeyColumn = Blocking.BlockingId and NameColumn = Blocking.BlockingName.<br /><p><a href="http://lh3.googleusercontent.com/-aK8BkhS4WZc/VTdwpnnnxoI/AAAAAAAAHpQ/jAqSrvCr4t4/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgF9T_XPpcgMkTtu7CcxHURcSr-vt2F1mMq3YiQnju6IFsU5iCNrlXX9peIya-X8LxWC_ojfjK2kN8vN9mvdZRN1Dn7ylk-a02IvTRkuqZXmPLhZe47zshuSsSaGTYrxgNfNpY/?imgmax=800" width="244" height="158"></a><br /><h4>Dimension Designer – Translations</h4><br /><p>In the Translations design, we can add a new translations for the captions, for the Attributes and also for the [All] and [Unknown] members.<br /><h5>Preparation to translate [All] and [Unknown] members</h5><br /><p>The captions of these 2 members appear by default when browsing a dimension:<br /><p><a href="http://lh3.googleusercontent.com/-p0NDMtsGdis/VTdwq5XJoiI/AAAAAAAAHpc/K4yo8f397Xc/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-TjpyHlB9wNM/VTdwrfiuEqI/AAAAAAAAHpo/6pSjA3ICZyE/image_thumb%25255B3%25255D.png?imgmax=800" width="138" height="109"></a><br /><p>To make [All] and [Unknown] translatable we can explicitly specify them in the Dimension properties.<br /><p>In the Dimension Structure design, select the Dimension in the Attributes Pane, and look for the following 2 Dimension properties:<br /><ul><br /><li>AttributeAllMemberName: set this to ‘All’ to enable translation. (Or use another word, e.g. Everything)<br><a href="http://lh3.googleusercontent.com/-8mvR59c9hLk/VTdwr2JYOiI/AAAAAAAAHpw/4Jk8J26FmPA/s1600-h/image%25255B14%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-YGYNtO7IfdY/VTdwsvwwhwI/AAAAAAAAHp0/8-TV1N4XAP4/image_thumb%25255B4%25255D.png?imgmax=800" width="244" height="110"></a></li><br /><li>UnknownMemberName: set this to ‘Unknown’ to enable translation (Or use any other word, like ‘Unidentified’)</li></ul><br /><h5>Add Translations</h5><br /><p>In the Dimension Designer go to the Translations tab. <br /><p>The Dimension properties that have translatable captions:<br /><ul><br /><li>Caption</li><br /><li>AttributeAllMemberName</li><br /><li>UnknownMemberName.</li></ul><br /><p>And for the Attributes:<br /><ul><br /><li>Caption</li></ul><br /><p><a href="http://lh3.googleusercontent.com/-_YxcbTRTWIU/VTdwtG77phI/AAAAAAAAHqA/RlkB7iGd1MA/s1600-h/image%25255B17%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-2zsQ5iNkTAs/VTdwt8BLGsI/AAAAAAAAHqE/Q3IYkFxPdUA/image_thumb%25255B5%25255D.png?imgmax=800" width="244" height="115"></a></p><br /><p>Click on the ‘Add new translation’ button<br /><p><a href="http://lh3.googleusercontent.com/-1mNyel35DAQ/VTdwuZ16-uI/AAAAAAAAHqM/vSGQhMWAZFY/s1600-h/image%25255B20%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-eHLrj95wDYQ/VTdwuzLlpcI/AAAAAAAAHqU/B-dzXCSKEGg/image_thumb%25255B6%25255D.png?imgmax=800" width="31" height="31"></a><br /><p>The Select language dialog box opens:<br /><p><a href="http://lh3.googleusercontent.com/-UUmOtgXqgyA/VTdwvqe5VzI/AAAAAAAAHqc/9YnwI7tZx9Y/s1600-h/image%25255B23%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-151s8aXmY40/VTdwwERO9aI/AAAAAAAAHqo/gW4iRAfg4fc/image_thumb%25255B7%25255D.png?imgmax=800" width="244" height="91"></a><br /><p>Select the language needed and make sure you have an exact match with the display language installed in MS-Office. E.g. choose ‘Dutch (Netherlands)’ when the Office 2013 language pack for ‘Nederlands (Nederland)’ is installed. Because I have a Dutch Office 2013 installation, the display language is shown translated by MS-Office.<br /><p>For the Dimension Caption and Member names, type text in the Translation column.<br /><p><a href="http://lh3.googleusercontent.com/-h1q5-9SXebs/VTdww_xCnyI/AAAAAAAAHqw/xfY5NfRhUwE/s1600-h/image%25255B42%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-GzZKsFh1RXM/VTdwxsxBDYI/AAAAAAAAHq0/rTBvL9JMZpY/image_thumb%25255B14%25255D.png?imgmax=800" width="418" height="111"></a><br /><p>For the Attribute translation, there is a translation for the caption, but here you can select a column name to use as translation for the attribute data. Click inside the translation column and you see a button. Click on the button to open the Attribute Data Translation dialog window. Enter the Translated caption and select the Translation column from the dimension table.<br /><p><a href="http://lh3.googleusercontent.com/-Kb02Vgqw0F0/VTdwyJ6mvcI/AAAAAAAAHrA/j7SnX3fLjOE/s1600-h/image%25255B29%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-VeSIYEQ6jjU/VTdwywS4jUI/AAAAAAAAHrI/TeEHOnxDqGc/image_thumb%25255B9%25255D.png?imgmax=800" width="244" height="223"></a><br /><p>Save and deploy this dimension, make sure it is processed.<br /><h3>Connect MS-Excel to the SSAS cube</h3><br /><h4>Use the default display language</h4><br /><p>The Dutch version of MS-Excel is setup to use ‘Nederlands’ as display language:<br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSsqNln6VuH9tGB0oOVtvUznIgiugVB3Z75kkRGK_q-3XpGXIwYQVf2guBOGsM9uFAkrFFjpE-80zKNoEFEMX9XXcNl4pqOVSk9uO7K7mvGRtiAStMLYjHT8cU3Zoow9tXB5s/s1600-h/image%25255B32%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-gvX9q23cS0U/VTdw0YGBA4I/AAAAAAAAHrU/e-sq_ZSmNqU/image_thumb%25255B10%25255D.png?imgmax=800" width="244" height="132"></a><br /><p>In the spreadsheet, connect to the Analysis Services Cube and create a new pivot table. The Pivot table fields shows the Blocking dimension with translation.<br /><p><a href="http://lh3.googleusercontent.com/-ve1L7tlZjWg/VTdw07jl29I/AAAAAAAAHrc/R2UiyIRBAIo/s1600-h/image%25255B35%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-S5eCIIHrXcE/VTdw1uq78-I/AAAAAAAAHro/IZG082u4q1U/image_thumb%25255B11%25255D.png?imgmax=800" width="244" height="121"></a><br /><p>After selection of this dimension in the Rows section, the pivot table shows the Attribute data translated in Dutch.<br /><p><a href="http://lh3.googleusercontent.com/-SkbXr1F95Gg/VTdw2Q22XWI/AAAAAAAAHrs/oZvZWMe1RvQ/s1600-h/image%25255B38%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-kweBxmjmUcc/VTdw2zLbwyI/AAAAAAAAHr4/bOvOF-eWkSU/image_thumb%25255B12%25255D.png?imgmax=800" width="204" height="123"></a><br /><p>Excel row 2, 3 and 4 show the translated members of the ‘Blocking’ attribute.<br /><p>MS-Excel automatically translates ‘Row-labels’ and ‘Grand total’ as ‘Rijlabels’ and ‘Eindtotaal’. This is not a function of the SSAS translation.<br /><h5>Change to another display language</h5><br /><ul><br /><li>Change the Display language to French.</li><br /><li>Close and restart MS-Excel.</li><br /><li>Open the same spreadsheet.</li><br /><li>In the Data section, click on the Refresh button. </li><br /><li>The pivot table is now shown with French captions and values.</li></ul><br /><p><a href="http://lh3.googleusercontent.com/-ljJgL-IANXo/VTdw3tSLTII/AAAAAAAAHsU/4RXAJ6F8jtU/s1600-h/image%25255B44%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-4x48vginDCM/VTdw4N1KzSI/AAAAAAAAHsc/4zX0-m1xRzQ/image_thumb%25255B16%25255D.png?imgmax=800" width="202" height="113"></a></p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-55805761972418634832015-04-20T16:10:00.001+02:002015-04-20T16:30:45.059+02:00SSRS Configure the Unattended Execution Account<p>One way to run scheduled reports is to use SQL server logins and store the credentials in the data source of the report. For every SQL server that needs to be accessed to read the report data the SQL account credentials must be maintained. The same credentials must be saved in every data source on the report server.</p> <p>An alternative is to run SQL reports with an AD windows account. The AD account has to be granted the same rights to read report data, but without saving credentials. The Reporting Service Server stores the credentials once in the Unattended Execution Account of SSRS.</p> <h3>Configure the Unattended Execution Account</h3> <p>Start the Reporting Services Configuration manager</p> <p><a href="http://lh3.googleusercontent.com/-BuuPLqNSvCU/VTUIvPRWOSI/AAAAAAAAHnk/V0VDBX7XvSE/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-C5delAcuvzc/VTUIvuQ72EI/AAAAAAAAHno/sjnUrRgFF6U/image_thumb.png?imgmax=800" width="244" height="40"></a></p> <p>Connect to the Reporting services Server</p> <p><a href="http://lh3.googleusercontent.com/-vGziIkT5YD8/VTUIwXgc48I/AAAAAAAAHn0/fk9WFaexcQw/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.googleusercontent.com/-MEnCbhW4Uz0/VTUIw-BvmYI/AAAAAAAAHn8/z5Jbd4q1exI/image_thumb%25255B1%25255D.png?imgmax=800" width="244" height="142"></a></p> <p>In the left pane of the configuration manager, select ‘Execution Account’. Check ‘Specify an account’ and enter the AD account credentials.</p> <p><a href="http://lh3.googleusercontent.com/-Qmf59aciByM/VTUIxtcSM0I/AAAAAAAAHoE/ApPY8feLluo/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1xuboEb4pk_y64tskHQmc2KA22Gsd_naDlkZfHIo_ZuNGnwZONIAqCs9ijX2zyZykIXg1U8hO8r0lp1fLAqOBWif1HOk3FxwGMN7He79ZRjNxNwfb8VBE284rdODZ7K6mgt8/?imgmax=800" width="244" height="161"></a></p> <p>Click on ‘Apply’. A dialog box will open to give the name for a key file with it’s own password.</p> <h3>Give the unattended account read rights in the SQL Server database</h3> <p>Add the AD account as a windows login to the SQL Server. Add the login as user to the database. Grant the DB user sufficient select and/or execute rights in the database to read the report data.</p> <h3>Use the unattended account in the Report Data Sources</h3> <p>The Connection string must use ‘Integrated Security=SSPI’</p> <p>For Shared data sources on a report server. Browse to the Data Sources folder of the report server.</p> <p>Select a Data Source and click on it , or open the dropdown and select ‘Manage’.</p> <p>In the properties tab, in the connection string textbox add the ‘;Integrated Security=SSPI’ Select ‘Credentials are not required’.</p> <p><a href="http://lh3.googleusercontent.com/-Ll8DLtlJ_U0/VTUIzN60ilI/AAAAAAAAHoU/14xL_uknDBY/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaWzg3MX9g5PyxSssg3NCZ90avLPeyJQ7i-aPtvl1rEiBZxqVo5XrxqemUdUR2WroPpE5RZnlYJQEhsF6hzBjXQQJ9pQAe-wRRYqI397Wz9f1PZOZgKdxU35UgdWT9BqL7qc0/?imgmax=800" width="238" height="244"></a></p> <p>click Apply.</p> <h3>Conclusion</h3> <p>Account administration is managed by AD admins. The SQL Admin only needs to define the AD as a user login on the SQL server level and grant access rights on the database level. No need to maintain passwords for every SQL server.</p> <p>The Report server stores the unattended account once, hence it is no longer necessary to maintain SQL Login credentials and passwords for every Data Source deployed on the report server.</p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-10417867413312448452015-04-16T10:43:00.001+02:002015-04-16T11:00:25.844+02:00Query to find specific job steps in SQL Server Agent jobs<p>To compare similar job steps on a server that ran several of <a href="https://ola.hallengren.com/sql-server-backup.html" target="_blank">Ola Hallengrens Maintenance scripts</a> I came across this example to <a href="https://sufianrashid.wordpress.com/2011/04/01/get-all-job-steps-in-sql-server/" target="_blank">Get all job steps in SQL Server</a> by Sufian Rashid.</p> <p>I changed the WHERE condition to look for jobs names ending in ‘FULL’ and which executed a ‘sqlcmd’ statement:</p> <div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4"> <div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span style="color: #0000ff">SELECT</span> JOB.NAME, STEP.STEP_ID, STEP.STEP_NAME, STEP.COMMAND</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">FROM</span> Msdb.dbo.SysJobs JOB</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"> <span style="color: #0000ff">INNER</span> <span style="color: #0000ff">JOIN</span> Msdb.dbo.SysJobSteps STEP <span style="color: #0000ff">ON</span> STEP.Job_Id = JOB.Job_Id</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">WHERE</span> JOB.Enabled = 1</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"> <span style="color: #0000ff">AND</span> (JOB.Name <span style="color: #0000ff">LIKE</span> <span style="color: #006080">'%FULL'</span> <span style="color: #0000ff">and</span> STEP.COMMAND <span style="color: #0000ff">LIKE</span> <span style="color: #006080">'sqlcmd%'</span>)</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> JOB.NAME, STEP.STEP_ID</pre><!--CRLF--></div></div><br /><p>The query result:</p><br /><p><a href="http://lh4.ggpht.com/-VwU8u6gsC_E/VS96JF3kt5I/AAAAAAAAHnI/VUxdPiL1QQ0/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-pe7Xer-KMKY/VS96KF_uI4I/AAAAAAAAHnM/XuwDxWCTdug/image_thumb%25255B1%25255D.png?imgmax=800" width="540" height="154"></a><br /><p>This list allows me to easily compare the commands executed. <br /><p>E.g. I saw that on line 3 there was as parameter with value 47, whereas on line 4 I had the same parameter with value 48.<br /><p>The LogToTable parameter for the instance SQL1\STORE has value ‘N’, whereas this parameter has value ‘Y’ for the SQL1\PRD instance.<br /> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-56637205957199473662015-04-07T15:20:00.001+02:002015-04-16T10:11:41.073+02:00Deploying SSAS databases<h3>Deploy AS database to test environment with VS 2012</h3> <p>With Visual Studio 2012 and SQL Server Data Tools 11.1.50318.0</p> <p>Project configuration Active Solution is set to Development.</p> <p>From within Visual Studio, right-click on the SSAS project, Select ‘Deploy’ in the dropdown menu.</p> <h3>Deploy AS database to production with Deployment Wizard</h3> <p>Start Analysis Services deployment wizard</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhl8ZMM3Tp9Btl9F5BhNtBHVlCw7try9l8rS1U_HYiGzj5VoxNcsZtOzxEaaCwVyWPB9naZLLHwgGs24whs9JwUzfMH6Gprd2NyN7_dMlMTm__qy9sek027CM1akaoVfTJT40/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgROaauJ23nG2JkJj1B8jO2A2sOBBUtoGN5W_GcV53-eHySGeM6AuAFw35BYjcc3M6iWJYIBOOT3Pks7BmFDZhl9l2OuKCoThuuyC0mLdOiQyChOvpk3ys3uvkBBtNHpvqYw1M/?imgmax=800" width="213" height="59"></a></p> <p>Welcome screen – Next</p> <p><a href="http://lh3.ggpht.com/-If7W4G_SlME/VSPZa_ug4UI/AAAAAAAAHkw/dmYJGR9z3Ec/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-1tvo0xp0790/VSPZbs-lt0I/AAAAAAAAHk4/3aB37YuA9lQ/image_thumb%25255B1%25255D.png?imgmax=800" width="244" height="118"></a></p> <p>Database file</p> <p>Browse to the bin folder of your VS2012 Analysis services project and look for the file with the asdatabase extension. Next</p> <p><a href="http://lh6.ggpht.com/-lXN3cuqAGSw/VSPZcGd2AaI/AAAAAAAAHlA/5zcYfx-fjEA/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-xEdpzvGEXhQ/VSPZcxnZEdI/AAAAAAAAHlI/jYsGys-HOZ0/image_thumb%25255B2%25255D.png?imgmax=800" width="244" height="101"></a></p> <p>Installation target. Enter Server name and Database name as it should appear on that server. Next</p> <p><a href="http://lh6.ggpht.com/-mwnISCF49A4/VSPZdX_9M_I/AAAAAAAAHlQ/jGi7sqUZEK0/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-p4wB4l1HGck/VSPZd02PwTI/AAAAAAAAHlY/bIhPcPUx3lM/image_thumb%25255B3%25255D.png?imgmax=800" width="244" height="150"></a></p> <p>Specify Options for partitions and roles. Next</p> <p><a href="http://lh6.ggpht.com/-0dFXSWKY32I/VSPZegxkVRI/AAAAAAAAHlg/FtffKtsJyu4/s1600-h/image%25255B14%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-VlKizsbZHjk/VSPZfFVWyjI/AAAAAAAAHlo/TlrVYEa0u6Y/image_thumb%25255B4%25255D.png?imgmax=800" width="244" height="152"></a></p> <p>Specify configuration Properties. Check ‘Retain configuration settings for existing objects’.</p> <p><a href="http://lh3.ggpht.com/-Pgbj4NmP-aI/VSPZfzYYUsI/AAAAAAAAHlw/uZ-gyeONYaM/s1600-h/image%25255B17%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-CFZOnX9Wzs8/VSPZgtF04FI/AAAAAAAAHl4/7g5IuwArPLg/image_thumb%25255B5%25255D.png?imgmax=800" width="244" height="149"></a></p> <p>Select Processing Options. Default processing. Next.</p> <p><a href="http://lh4.ggpht.com/-urBm24b_q4E/VSPZhcyrlII/AAAAAAAAHmA/254j-3AdS0k/s1600-h/image%25255B20%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-5zoe6RwOurE/VSPZhxcDbeI/AAAAAAAAHmE/gnYU83ekqVk/image_thumb%25255B6%25255D.png?imgmax=800" width="244" height="173"></a></p> <p>Confirm deployment. Next.</p> <p>Deployment is done.</p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com1tag:blogger.com,1999:blog-31942401.post-11217054284369767552015-04-05T11:46:00.001+02:002015-04-05T11:55:33.155+02:00Microsoft Hands-On Lab Implementing and Managing AlwaysOn Availability groups<p>When you have not yet had the opportunity to experience the SQL 2012 AlwaysOn availability feature, on Technet there is a Hands-On Lab that gives some insight:</p> <p><a href="https://vlabs.holsystems.com/vlabs/technet?eng=VLabs&auth=none&src=vlabs&altadd=true&labid=11639" target="_blank">Implementing and Managing AlwaysOn Availability groups</a></p> <p>The exercises in the lab not only sheds some light on how a basic AlwaysOn configuration can be setup by the DBA, it also shows 2 examples of how to setup the connection string for a CRUD and a reporting application.</p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-22562951834258344282015-03-26T17:40:00.001+01:002015-04-02T09:46:51.010+02:00System backup software triggers unwanted SQL backups<h3>Problem</h3> <p>Sometimes backup software that is used to backup the Windows servers on which SQL Server is running will trigger a SQL backup automatically. Although you may have setup a neat SQL backup schedule in SQL Server Agent with Full, Differential and transaction log backups, consequences are that whenever this Server backup software kicks in and triggers another full backup of your databases, your backup files may be the wrong ones in case you ever need to do a restore with your Full, differential and transaction log backups, because the order in which they are created by you is broken by the unwanted backup in the background.</p> <p>You do not see the unwanted backups in the SQL server Agent log, but you will find them back in the standard SQL server logs. The SQL server log typically show lines like this:</p> <ul> <li>Database backed up. Database: master, creation date(time): 2015/02/19(16:52:39), pages dumped: 1356, first LSN: 388:415:117, last LSN: 388:465:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{9603DF6C-C7E9-4A19-BAAF-C827EFE22023}7'}).</li> <li>I/O was resumed on database master. No user action is required.</li> <li>I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.</li></ul> <p>The same messages are repeated for all the other system and user databases on that particular server.</p> <h3>Cause</h3> <p>The SQL server service called ‘SQL Server VSS Writer’ is used by the Windows backup software to trigger the SQL server backup. From SQL BOL:</p> <p>When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup. <ul> <li> <p>‘Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running.’</p></li></ul> <h3></h3> <h3>Solution</h3> <p>In the Services management console: Stop and disable the ‘SQL Server VSS Writer’ service.</p> <p><a href="http://lh5.ggpht.com/-7_oHvzpuGPo/VRQ2fLG8XvI/AAAAAAAAHhw/eOI_5TSNsU0/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmveNUU6s17kBCXJrRAvftY-oqd8STDQ1AcG2NQxT86FdF-UJFl8ieDJcBMNl8rvdQfNhKMeT8aeCyLkMN4pj0ALJEC60LCzilSyuyWnhIgD1vq0Lth17OVHU7AIN1CPLzeM8/?imgmax=800" width="244" height="42"></a></p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-63688501821743017742015-03-26T17:34:00.001+01:002015-04-01T15:04:44.512+02:00Working with environments on a deployed SSIS 2012 Project<p>When an SSIS project is deployed from SQL Server Data Tools to the Integration Services Catalog, the Project and Package connections and parameters are stored together with the project and the packages. Each parameter or connection property in de Catalog is reconfigurable by means of environments. With environments properly setup, developers do not need to worry about changing connections or parameters when they release an upgrade to the Production server. Because the production server environment will automatically replace the test values in the connections and in the parameters by values setup in the production environment. </p> <h3>Catalog folder after 1st time deployment</h3> <p>An example of a project in the catalog with 1 package. No Environments have been created yet.</p> <p><a href="http://lh3.ggpht.com/-mcIvOhZ8dxU/VRQ08RsR65I/AAAAAAAAHhA/OOpO-6-S2H0/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-082_XQlxMDw/VRQ09LaXirI/AAAAAAAAHhI/LXqF8LLdaCs/image_thumb.png?imgmax=800" width="244" height="99"></a></p> <h3>Create Environments</h3> <p>In this example we will create 2 new environments with a sql script: Test and Production.</p> <div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4"> <div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white">--<span style="color: #008000">-- create environment</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">EXEC</span> [SSISDB].[<span style="color: #0000ff">catalog</span>].[create_environment] @environment_name=N<span style="color: #006080">'Production'</span>, @environment_description=N<span style="color: #006080">''</span>, @folder_name=N<span style="color: #006080">'SSIS_2012_Test'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span style="color: #0000ff">GO</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">EXEC</span> [SSISDB].[<span style="color: #0000ff">catalog</span>].[create_environment] @environment_name=N<span style="color: #006080">'Test'</span>, @environment_description=N<span style="color: #006080">''</span>, @folder_name=N<span style="color: #006080">'SSIS_2012_Test'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span style="color: #0000ff">GO</span></pre><!--CRLF--></div></div><br /><p>After the script is executed: the 2 new environments are visible in the Environments folder.</p><br /><p><a href="http://lh4.ggpht.com/-SYglC5OISuI/VRQ09_foZzI/AAAAAAAAHhQ/z0_QRRY9r14/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-PU3cxA1Whk4/VRQ0-RxWwkI/AAAAAAAAHhU/qrm-K4Yrp4U/image_thumb%25255B1%25255D.png?imgmax=800" width="244" height="141"></a></p><br /><h3>Create environment variables</h3><br /><h4>For project parameters</h4><br /><h4></h4><br /><p>There is a project parameter in the SSIS project ‘DaysToLoad’ with value 15. This is shown in the project configuration, right-click on the project and select ‘Configure…’</p><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnMeYudSBSHjDLkqyKuR9JfCCgHX_uBR4UTGyl_yd71OexvsWilmT3L0vTmrs73N83nke1YCFhbc2YU8s9b7ZMGq7zT-rinfd3St8VXKiyip1Y2yjJC3hAnUPvPXfQ-uMLRQ0/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuQcluiRuspXLI0CPlz5IK-O2y6LGquqlLuynCoS8ua51mFbvDgo1p2vHKBSGYuF7xgyV8UW_4qegkko6Xw_-Ce7dKQlLZHsFTQq9uu_2OHXIYJpEaX_E44eQTrqOWT5jOxg4/?imgmax=800" width="244" height="137"></a></p><br /><p>Or with a T-SQL script, create an environment variable ‘prjDaysToLoad’ in both environments:</p><br /><div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4"><br /><div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span style="color: #0000ff">DECLARE</span> @var <span style="color: #0000ff">int</span> = N<span style="color: #006080">'30'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">EXEC</span> [SSISDB].[<span style="color: #0000ff">catalog</span>].[create_environment_variable] @variable_name=N<span style="color: #006080">'prjDaysToLoad'</span>, @sensitive=<span style="color: #0000ff">False</span>, @description=N<span style="color: #006080">''</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"> , @environment_name=N<span style="color: #006080">'Production'</span>, @folder_name=N<span style="color: #006080">'SSIS_2012_Test'</span>, @<span style="color: #0000ff">value</span>=@var, @data_type=N<span style="color: #006080">'Int32'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">EXEC</span> [SSISDB].[<span style="color: #0000ff">catalog</span>].[create_environment_variable] @variable_name=N<span style="color: #006080">'prjDaysToLoad'</span>, @sensitive=<span style="color: #0000ff">False</span>, @description=N<span style="color: #006080">''</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"> , @environment_name=N<span style="color: #006080">'Test'</span>, @folder_name=N<span style="color: #006080">'SSIS_2012_Test'</span>, @<span style="color: #0000ff">value</span>=@var, @data_type=N<span style="color: #006080">'Int32'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4">GO</pre><!--CRLF--></div></div><br /><h4>Add a reference to the environment in the reference</h4><br /><p>The project needs to be made aware of the available environments first.</p><br /><p>You can do this by right-clicking on the project name and select ‘Configure…’ and add the reference in the References panel. Or you can do it with a T-SQL script</p><br /><div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4"><br /><div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span style="color: #0000ff">Declare</span> @reference_id bigint</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">EXEC</span> [SSISDB].[<span style="color: #0000ff">catalog</span>].[create_environment_reference] @environment_name=N<span style="color: #006080">'Production'</span>, @reference_id=@reference_id <span style="color: #0000ff">OUTPUT</span>, @project_name=N<span style="color: #006080">'SSIS_2012_TestOdata3'</span>, @folder_name=N<span style="color: #006080">'SSIS_2012_Test'</span>, @reference_type=R</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span style="color: #0000ff">EXEC</span> [SSISDB].[<span style="color: #0000ff">catalog</span>].[create_environment_reference] @environment_name=N<span style="color: #006080">'Test'</span>, @reference_id=@reference_id <span style="color: #0000ff">OUTPUT</span>, @project_name=N<span style="color: #006080">'SSIS_2012_TestOdata3'</span>, @folder_name=N<span style="color: #006080">'SSIS_2012_Test'</span>, @reference_type=R</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4">GO</pre><!--CRLF--></div></div><br /><p>The result in the configure dialog window looks like this</p><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEicReArR9YfFM0MEXeSuVs8G4CaTXCFR4J6ctukwf12cRU2l1ClEWxrartiROwxitJ99u6PIKBZuaX2WAAm_8u2_RJgav0StNqf1zHRb-jt5FQieeQHfzKcZRBirnVJx5ywFAI/s1600-h/image%25255B6%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-uviemfdBMnI/VRUcDwyvooI/AAAAAAAAHis/dyftehzSyJY/image_thumb%25255B2%25255D.png?imgmax=800" width="244" height="165"></a></p><br /><h4>Create an environment variable in production and test environment</h4><br /><div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4"><br /><div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white">--<span style="color: #008000">-- create env variable in the 2 environments</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">DECLARE</span> @var <span style="color: #0000ff">int</span> = N<span style="color: #006080">'30'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span style="color: #0000ff">EXEC</span> [SSISDB].[<span style="color: #0000ff">catalog</span>].[create_environment_variable] @variable_name=N<span style="color: #006080">'prjDaysToLoad'</span>, @sensitive=<span style="color: #0000ff">False</span>, @description=N<span style="color: #006080">''</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"> , @environment_name=N<span style="color: #006080">'Production'</span>, @folder_name=N<span style="color: #006080">'SSIS_2012_Test'</span>, @<span style="color: #0000ff">value</span>=@var, @data_type=N<span style="color: #006080">'Int32'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span style="color: #0000ff">EXEC</span> [SSISDB].[<span style="color: #0000ff">catalog</span>].[create_environment_variable] @variable_name=N<span style="color: #006080">'prjDaysToLoad'</span>, @sensitive=<span style="color: #0000ff">False</span>, @description=N<span style="color: #006080">''</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"> , @environment_name=N<span style="color: #006080">'Test'</span>, @folder_name=N<span style="color: #006080">'SSIS_2012_Test'</span>, @<span style="color: #0000ff">value</span>=@var, @data_type=N<span style="color: #006080">'Int32'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white">GO</pre><!--CRLF--></div></div><br /><p>See the result in the ‘Production’ environment</p><br /><p><a href="http://lh6.ggpht.com/-4f6nzbQFiz0/VRUcE9XVbiI/AAAAAAAAHi0/B_urLlJ5oFg/s1600-h/image%25255B12%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-rZNADkW2kG0/VRUcFkYjg8I/AAAAAAAAHi4/sJOSF1LINRQ/image_thumb%25255B4%25255D.png?imgmax=800" width="244" height="63"></a></p><br /><h4><br /><p>Reference the environment variable from a parameter in the Project </p><br /><p><a href="http://lh6.ggpht.com/-I2GQTSYjpiw/VRUcGML8F-I/AAAAAAAAHjE/NE-QJKeZnaU/s1600-h/image%25255B15%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQ3MjWgimVWF8aAJtQr-sZDRHFzFE56o5t0ZPJNRnnrcYl19c_3KO13Lp-HAjx2bOcjDXedKjvByfb2xOE8IJpLd8Fw2Ui6o1pAbASFl7IBTpkWTAS-C1j84CIiaQaT22H9Vs/?imgmax=800" width="244" height="228"></a></p><br /><p><font size="2"><font style="font-weight: normal">or do it with a T-SQL script</font></font></p><br /><div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4"><br /><div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white">--<span style="color: #008000">-- reference the environment from the project parameter</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span style="color: #0000ff">EXEC</span> [SSISDB].[<span style="color: #0000ff">catalog</span>].[set_object_parameter_value] @object_type=20, @parameter_name=N<span style="color: #006080">'DaysToLoad'</span>, @object_name=N<span style="color: #006080">'SSIS_2012_TestOdata3'</span>, @folder_name=N<span style="color: #006080">'SSIS_2012_Test'</span>, @project_name=N<span style="color: #006080">'SSIS_2012_TestOdata3'</span>, @value_type=R, @parameter_value=N<span style="color: #006080">'prjDaysToLoad'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white">GO</pre><!--CRLF--></div></div></h4><br /><p>This is the result</p><br /><p><a href="http://lh5.ggpht.com/-cL2HLmRCnC8/VRUcHrHN_7I/AAAAAAAAHjQ/biDz-ItVn6c/s1600-h/image%25255B18%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-nYyv4VhvT7U/VRUcId5NxUI/AAAAAAAAHjY/lWBhAsR_rmA/image_thumb%25255B6%25255D.png?imgmax=800" width="244" height="69"></a></p><br /><p>Be careful: The name of the environment variable needs to be identical in both environments</p><br /><h3>Run the package manually from the Catalog with a reference to the environment</h3><br /><p>When a package in this project is run we add the reference to one of the environments ‘Production’ or ‘Test’.</p><br /><p>Right-click on a package to Execute. The execute package will warn that no environment is selected for the parameter that has a reference:</p><br /><p><a href="http://lh3.ggpht.com/-QYOrAaaBrzs/VRUcI8aWYII/AAAAAAAAHjk/CndOha3HkJA/s1600-h/image%25255B21%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-OLgWYG9f0zQ/VRUcJ-XR6-I/AAAAAAAAHjo/OhQ7r__DekY/image_thumb%25255B7%25255D.png?imgmax=800" width="244" height="113"></a></p><br /><p>Check the Environment checkbox and select one of the environments</p><br /><p><a href="http://lh3.ggpht.com/-8hz0gE812n4/VRUcKZngCcI/AAAAAAAAHj0/AP2GfoLp3Mo/s1600-h/image%25255B24%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-4rSM0IRApfg/VRUcLBAsilI/AAAAAAAAHj4/8o8jkZow9vc/image_thumb%25255B8%25255D.png?imgmax=800" width="244" height="133"></a></p><br /><p>When OK is clicked the package will use the value for the parameter that comes from the selected environment.</p><br /><h3>Run the package in a SQL Server Agent job with a reference to the environment</h3><br /><p>In a job step the procedure is similar, select the package and check the environment checkbox and select one of the environments.</p><br /><p><a href="http://lh4.ggpht.com/-hbwWqYoyWh0/VRUcL-UX3zI/AAAAAAAAHkA/gxqvVWT0APo/s1600-h/image%25255B27%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-1H1tpoqTNu8/VRUcMqSo62I/AAAAAAAAHkI/CdLWcx3-Dbs/image_thumb%25255B9%25255D.png?imgmax=800" width="244" height="211"></a></p><br /><h3>Conclusion</h3><br /><p>Once SSIS projects are deployed from a developer environment to a production environment you do not need to change parameter or connection settings in your Visual Studio project before the deployment. In the Integration Services you can set up one or more environments with variables for parameters and connections that differ from the developer environment. The developer has no worries about deploying in the production environment (either directly or through a deployment script), because the DBA administrator can setup up an environment in the Catalog so that the packages connect to databases and use parameters adapted for this environment.</p><br /><p>In a production environment I tend to create only the ‘production’ environment and on a test or QA server I create only the ‘test’ environment. But you can still use several different environments in a production environment the package may need to address ETL operations on several production servers.</p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-40231169586676263702015-03-26T15:00:00.001+01:002015-03-26T15:03:18.818+01:00Schedule to recycle the SQL Server Logs<p>A new SQL Server log file is created each time the SQL service is restarted. On a production server that is very sparingly restarted this can result in enormous log files.</p> <p>Below is a script to schedule a weekly recycle of the SQL Server Logs. The number of log files is set to 12 before they are recycled. Thereby giving a 3 month long history of the logs.</p> <p>Other combinations are possible, e.g. make a daily schedule and set the number of files to 30, this way the log files are shorter and the history is one month long.</p> <div class="csharpcode"><pre class="alt">--<span class="rem">-- set SQL server log recycle property to Recycle after 12 files</span></pre><pre><span class="kwrd">USE</span> [master]</pre><pre class="alt"><span class="kwrd">GO</span></pre><pre><span class="kwrd">EXEC</span> xp_instance_regwrite N<span class="str">'HKEY_LOCAL_MACHINE'</span>, N<span class="str">'Software\Microsoft\MSSQLServer\MSSQLServer'</span>, N<span class="str">'NumErrorLogs'</span>, REG_DWORD, 12</pre><pre class="alt"><span class="kwrd">GO</span></pre><pre>--<span class="rem">-- Create SQL Server Agent Job to do a weekly recycle (Saturday 12:00) of the SQL Server log</span></pre><pre class="alt"><span class="kwrd">USE</span> [msdb]</pre><pre><span class="kwrd">GO</span></pre><pre class="alt"><span class="kwrd">DECLARE</span> @jobId <span class="kwrd">BINARY</span>(16)</pre><pre><span class="kwrd">EXEC</span> msdb.dbo.sp_add_job @job_name=N<span class="str">'MSOL Recycle SQL Server Logs'</span>, </pre><pre class="alt"> @enabled=1, </pre><pre> @notify_level_eventlog=0, </pre><pre class="alt"> @notify_level_email=2, </pre><pre> @notify_level_netsend=2, </pre><pre class="alt"> @notify_level_page=2, </pre><pre> @delete_level=0, </pre><pre class="alt"> @category_name=N<span class="str">'Database Maintenance'</span>, </pre><pre> @owner_login_name=N<span class="str">'sa'</span>, </pre><pre class="alt"> @notify_email_operator_name=N<span class="str">'ICT Helpdesk'</span>, @job_id = @jobId <span class="kwrd">OUTPUT</span></pre><pre><span class="kwrd">select</span> @jobId</pre><pre class="alt"><span class="kwrd">GO</span></pre><pre><span class="kwrd">EXEC</span> msdb.dbo.sp_add_jobserver @job_name=N<span class="str">'MSOL Recycle SQL Server Logs'</span>, @server_name = N<span class="str">'VMXYZ'</span></pre><pre class="alt"><span class="kwrd">GO</span></pre><pre><span class="kwrd">USE</span> [msdb]</pre><pre class="alt"><span class="kwrd">GO</span></pre><pre><span class="kwrd">EXEC</span> msdb.dbo.sp_add_jobstep @job_name=N<span class="str">'MSOL Recycle SQL Server Logs'</span>, @step_name=N<span class="str">'RecycleErrorLog'</span>, </pre><pre class="alt"> @step_id=1, </pre><pre> @cmdexec_success_code=0, </pre><pre class="alt"> @on_success_action=1, </pre><pre> @on_fail_action=2, </pre><pre class="alt"> @retry_attempts=0, </pre><pre> @retry_interval=0, </pre><pre class="alt"> @os_run_priority=0, @subsystem=N<span class="str">'TSQL'</span>, </pre><pre> @command=N<span class="str">'EXEC sp_cycle_errorlog'</span>, </pre><pre class="alt"> @database_name=N<span class="str">'master'</span>, </pre><pre> @flags=0</pre><pre class="alt"><span class="kwrd">GO</span></pre><pre><span class="kwrd">USE</span> [msdb]</pre><pre class="alt"><span class="kwrd">GO</span></pre><pre><span class="kwrd">EXEC</span> msdb.dbo.sp_update_job @job_name=N<span class="str">'MSOL Recycle SQL Server Logs'</span>, </pre><pre class="alt"> @enabled=1, </pre><pre> @start_step_id=1, </pre><pre class="alt"> @notify_level_eventlog=0, </pre><pre> @notify_level_email=2, </pre><pre class="alt"> @notify_level_netsend=2, </pre><pre> @notify_level_page=2, </pre><pre class="alt"> @delete_level=0, </pre><pre> @description=N<span class="str">''</span>, </pre><pre class="alt"> @category_name=N<span class="str">'Database Maintenance'</span>, </pre><pre> @owner_login_name=N<span class="str">'sa'</span>, </pre><pre class="alt"> @notify_email_operator_name=N<span class="str">'ICT Helpdesk'</span>, </pre><pre> @notify_netsend_operator_name=N<span class="str">''</span>, </pre><pre class="alt"> @notify_page_operator_name=N<span class="str">''</span></pre><pre><span class="kwrd">GO</span></pre><pre class="alt"><span class="kwrd">USE</span> [msdb]</pre><pre><span class="kwrd">GO</span></pre><pre class="alt"><span class="kwrd">DECLARE</span> @schedule_id <span class="kwrd">int</span></pre><pre><span class="kwrd">EXEC</span> msdb.dbo.sp_add_jobschedule @job_name=N<span class="str">'MSOL Recycle SQL Server Logs'</span>, @name=N<span class="str">'Weekly Saturday 12:00'</span>, </pre><pre class="alt"> @enabled=1, </pre><pre> @freq_type=8, </pre><pre class="alt"> @freq_interval=64, </pre><pre> @freq_subday_type=1, </pre><pre class="alt"> @freq_subday_interval=0, </pre><pre> @freq_relative_interval=0, </pre><pre class="alt"> @freq_recurrence_factor=1, </pre><pre> @active_start_date=20150326, </pre><pre class="alt"> @active_end_date=99991231, </pre><pre> @active_start_time=120000, </pre><pre class="alt"> @active_end_time=235959, @schedule_id = @schedule_id <span class="kwrd">OUTPUT</span></pre><pre><span class="kwrd">select</span> @schedule_id</pre><pre class="alt">GO</pre></div><br /><style type="text/css">.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }<br /></style><br /><br /><style type="text/css">.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }<br /></style> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-14549359089487452532015-03-25T09:03:00.001+01:002015-03-25T09:03:47.684+01:00SSIS 2012 package with Odata source–execution failed (part 3)<p>In post <a title="SSIS 2012 package with Odata source-failure (part" href="http://jandho.blogspot.be/2015/03/ssis-2012-package-with-odata-source.html">SSIS 2012 package with Odata source-failure (part</a> 2) I was able to deploy my package with Odata source. But the package failed to run.</p> <h3>Change from Project deployment to Package Deployment</h3> <p>I have converted my project with package connections from project deployment to Package deployment. Then I deployed the package on the server in MSDB catalog from SSIS. I.e. I connect with SSMS to Integration Services as an administrator and under the MSDB folder I added a new folder for my package. I then imported the dtsx into the mdsb package store. The Import worked. Still from within SSMS and connected to the Integration Services, i right-clicked on the package in the MSDB store and selected 'Run package'. In the Execute Package dialog window, I clicked on 'Execute' and the package actually ran. The Odata connector was opened, it read the data and imported into my database. <p>This experience made me realize what the real culprit was: The OData connector is a 32-bit connector in SQL Server 2012. <h3>Odata source is 32-bit </h3> <h3></h3> <p>To prove this: with SSMS I connected to the SQL database server and in the Integration services catalog, i right-clicked on the package that was deployed in Project deployment mode, then selected 'Execute...'. In the execute dialog windo in the 'Advanced' tab I checked 32-bit runtime. And the package ran. <h3></h3> <h3>Final conclusion</h3> <ol> <li>The Project deployment will work, but not with the Odata source as project connection. The Odata connection must be defined in each package as a package connection.</li> <li>Run the deployed packages in 32-bit mode on the server</li></ol> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com1tag:blogger.com,1999:blog-31942401.post-66665239246746126222015-03-24T15:51:00.001+01:002015-03-24T15:51:27.080+01:00SSIS 2012 package with Odata source-failure (part 2)<h3>Deployment failure</h3> <p>In my previous post <a title="SSIS 2012 package with Odata source–failed to deploy project" href="http://jandho.blogspot.be/2015/03/ssis-2012-package-with-odata.html">SSIS 2012 package with Odata source–failed to deploy project</a> I have encountered a deployment problem with an SSIS project created in project deployment mode with VS2012 and deployed to SQL 2012. </p> <p>In this package I have an OData connection to a SharePoint Odata service. I put the connection under the connection managers so that I could share the same connection amongst several packages in the project. The deployment failed on the system on which I was developing, as well as on another SQL 2012 server.</p> <p>This a project connection</p> <p><a href="http://lh4.ggpht.com/-H9wHuRGBoUc/VRF52Gs2WqI/AAAAAAAAHf0/06O-9c2a5eQ/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-GXZsKIYtw9s/VRF53D-80kI/AAAAAAAAHf8/ILZSJ1r98DY/image_thumb%25255B1%25255D.png?imgmax=800" width="234" height="244"></a></p> <p>Maybe the OData should not be in the project connection managers, but defined each time as a package connection. I decided to create a new SSIS project with one package with the Odata connection defined in the package. </p> <p>This is a Package connection</p> <p><a href="http://lh4.ggpht.com/-uQvauhc-Ueg/VRF530YH3ZI/AAAAAAAAHgA/nhWqNkOpAhc/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-Fs6RKWe45uE/VRF54boS9cI/AAAAAAAAHgI/Fc6vasxgBKQ/image_thumb.png?imgmax=800" width="242" height="244"></a></p> <h3>Change OData from Project to Package connection</h3> <p>The project with the Package connections deploys without errors in project deployment mode.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpnDy0bqSOSGB2f9GuW8gA1tAu2ppU1Fj8omzCvcMQWKBWxsEcmce177run3tWX2xOCfL8OuxiJ5mX2_Xs9aNzP-_pUlmfvoSt6HZ6tXH38LUCDZvipk3dcTpzgJzJGR5SlZ4/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfZ1AyF-E8sBzyFODs0aJmIz4nUr1okh_CQ917kUEpKJTZPe7XX7tf04cHO4YGflLCudBykr18iRB2d73EYnc-bIbBaD2mV-mZiv5hZN4uOu7NiwInNdRbrN0-jfP7Sk0RIgk/?imgmax=800" width="244" height="104"></a></p> <p>The project and package can be seen in the Integration Services catalog:</p> <p><a href="http://lh4.ggpht.com/-2AtOazSlv84/VRF56c0FBPI/AAAAAAAAHgg/ErUXa4Ld-ik/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-EAIn-pv8n0c/VRF57BrDBbI/AAAAAAAAHgs/2kLrJ_x1ibA/image_thumb%25255B3%25255D.png?imgmax=800" width="244" height="190"></a></p> <h3>Package deploys but Executes with runtime errors</h3> <p>When I Execute the package, it fails with error</p> <p>‘Error: The connection type "ODATA" specified for connection manager "OData Incidents" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.’</p> <p>I shall have to investigate further. </p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com1tag:blogger.com,1999:blog-31942401.post-44631769556916225942015-03-24T12:04:00.001+01:002015-03-24T15:52:18.852+01:00SSIS 2012 package with Odata source–failed to deploy project<p>On a test server with VS 2012 and SQL 2012 installed, I have created an SSIS project in project deployment mode. In the packages of the project I use the Odata source for SSIS. The packages run fine in VS 2012. But when I deploy the package to the Integration Services catalog on the same server as where I am running VS 2012 I get a deployment error:</p> <p>‘Failed to deploy project. For more information, query the operation_messages view for the operation identifier '25084'. (Microsoft SQL Server, Error: 27203)</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQVqCVTt_HQfZx2M8Bd8sYzSDn29ieXP7zSCoYuwTFwKl_Pc2ebv9AksOUOwv9rgdMarQm2XMub9zpRPDBdAQuKkWj4NrPSuvO7zw-TmG-49w_4tNXckN6Qa_rN4ec15wN1_M/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-0MKisAXYb6E/VRFExOCWleI/AAAAAAAAHfg/G-YRKdW8Nj8/image_thumb%25255B1%25255D.png?imgmax=800" width="376" height="233"></a></p> <p>To find out the cause of the problem, you can connect with SSMS to the SSISDB database where the SSIS projects are stored. Then select the record with operation_id from the operation_messages as suggested in the above-mentioned errormessage.</p><pre class="csharpcode"><span class="kwrd">use</span> SSISDB<br /><span class="kwrd">go</span><br /><br /><span class="kwrd">select</span> * <span class="kwrd">from</span> <span class="kwrd">catalog</span>.operation_messages <span class="kwrd">where</span> operation_id = 25084<br /></pre><br /><style type="text/css">.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }<br /></style><br /><br /><p>The message column in the result contains the cause of the error:</p><br /><p>‘Failed to deploy the project. Fix the problems and try again later.:Unable to create the type with the name 'ODATA'.’</p><br /><p>In my case the deployment failed of SSIS packages that use the Odata source on the same server as where I tested successfully with VS2012.</p><br /><p>Now I have to find out what is wrong, see <a title="SSIS 2012 package with Odata source-failure (part 2)" href="http://jandho.blogspot.be/2015/03/ssis-2012-package-with-odata-source.html">SSIS 2012 package with Odata source-failure (part 2)</a></p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-50117118440423982472015-03-24T11:40:00.001+01:002015-03-24T11:40:28.794+01:00Process SSAS cube in good order with SSIS package<p>Based on an article by Benny Austin about <a href="https://bennyaustin.wordpress.com/2013/08/19/processcube/" target="_blank">SSIS Package to Process SSAS Cube</a></p> <p>I have decided to process the cubes I have to maintain in the same order as suggested in his article.</p> <ol> <li>Process Dimensions</li> <ol> <li>Process default</li> <li>Process Update</li></ol> <li>Process Facts</li> <ol> <li>Process default</li> <ol> <li>Process Partitions – default</li> <li>Process Measure groups – default</li> <li>Process Cubes – default</li></ol> <li>Process Data</li> <ol> <li>Process Partitions – data</li> <li>Process Measure groups – data</li> <li>Process Cubes – data</li></ol></ol> <li>Process Index</li> <ol> <li>Process Dimensions - index</li> <li>Process Cube measures - index</li> <li>Process Partitions – index</li></ol></ol> <p>A screenshot of what the SSIS package looks like in VS 2012:</p> <p><a href="http://lh5.ggpht.com/-i7oQOl2yjpI/VRE-brjF-gI/AAAAAAAAHfE/E8wUMvrrtr0/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-_mXuW3N333k/VRE-cvDq0zI/AAAAAAAAHfI/HLKLUoYDCDI/image_thumb%25255B1%25255D.png?imgmax=800" width="411" height="240"></a></p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-71077567451135221332015-02-10T16:10:00.001+01:002015-02-10T16:10:10.025+01:00SSIS 2012 increment variable in expression task<p>With SSIS 2012 the Expression Task is a very useful addition.</p> <p><a href="http://lh5.ggpht.com/-7tMFFt6QGg0/VNofSGvixjI/AAAAAAAAHdo/dUsmyPigOvA/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-Wtw7UT1C2WY/VNofSpR2ZXI/AAAAAAAAHdw/-dnvc8Ebg_U/image_thumb.png?imgmax=800" width="234" height="198"></a></p> <p>It can replace some of the tedious little script tasks when all you want to do is to change the value of a variable.</p> <p>However! Although the programming language of C# in scripts can look very similar to the expression language used in SSIS it can be misleading. </p> <p>Today I needed a counter in a foreach loop container that needed to be incremented by one if an error was found in a preceding task. I created a variable of type int that was initiallly set to zero. Inside the container I added an Expression Task to increment my counter by 1. </p> <p>There I made my mistake: being used to incrementing with C# I wrote the following (incorrect) statement:</p> <p>@[User::Errorcounter] += 1</p> <p>This statement will evaluate to a value of 1 when you click the ‘Eveluate Expression’ button. since my variable was initially set to zero, this looked OK to me.</p> <p><a href="http://lh3.ggpht.com/-hY73N1OiQuk/VNofTBJYV8I/AAAAAAAAHd4/e9A-63RfpsA/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-UvdPzuq0cOo/VNofTxRpGtI/AAAAAAAAHeA/kmWLyr83v5Q/image_thumb%25255B1%25255D.png?imgmax=800" width="192" height="228"></a></p> <p>However, the variable will always stay at value 1, it will never increment! </p> <p>The correct increment statement is (in a VB-like or SQL-like form)</p> <p>@[User::Errorcounter] = @[User::Errorcounter] + 1</p> <p>This also evalutates to 1, but it will increment to 2, 3 and so on when the package is run.</p> <p>By the way: this can easily be verified when you set the initial value of the variable Errorcounter to another value, e.g. 5. With the incorrect statement the expression will still evaluate to 1. With the correct statement the expression will evaluate to 6.</p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-44738135251697530372015-01-26T10:20:00.001+01:002015-01-26T10:20:11.627+01:00Installing Master Data Services Add-in for MS-Excel version 2013<p>On a PC with an Office 2013 installation, the SQL Server 2012 MDS Add-in for MS-Excel will not install, the installer displays an error message that says Excel 2007 or 2010 is needed. The solution is simple, just download the SP1 version of the Add-in, you can find it here <a href="http://www.microsoft.com/en-us/download/details.aspx?id=35581" target="_blank">Microsoft® SQL Server® 2012 Service Pack 1 (SP1) Master Data Services Add-in For Microsoft® Excel®</a></p> <p>The installation file to download for SP1 has exactly the same name as the version for Excel 2007/2010.</p> <p><a href="http://lh4.ggpht.com/-jtFbYgtFN_Q/VMYGwa-Q9CI/AAAAAAAAHbw/R-czHYbYE-E/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-zYb1BV9Fg7c/VMYGwwLFAUI/AAAAAAAAHb0/UUD5hv6PjHc/image_thumb.png?imgmax=800" width="244" height="73"></a></p> <p>After download, run the MSI installation and when the installer has finished, you can see the new ‘Master Data’ item in the Excel menu</p> <p><a href="http://lh4.ggpht.com/-mbj9ojV2tmc/VMYGxTk4m4I/AAAAAAAAHcA/Xohux42LazQ/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-n7vvlhh-0GQ/VMYGyA9jwGI/AAAAAAAAHcI/qD3quSx1xog/image_thumb%25255B1%25255D.png?imgmax=800" width="244" height="64"></a></p> <p>Hope this helps.</p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-64385403716616263632015-01-14T10:29:00.001+01:002015-01-14T10:29:44.740+01:00Sort date names order in a matrix report based on SSAS Data Source<p> </p> <p>I created a report based on a simple SSAS cube with a fact table containing a value per day per division. The report has a year parameter and needs to show the sum per month in a pivot table, with 12 months in the columns and a row for each division.</p> <p>The report is easy to generate with the report wizard, but it results in columns where months are sorted by name: April, August, December, February, …</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEif8JlPhZd02NiI0si-V81_GCFh2JLNHtmDkef25vVv5hLG6FaWynDcnPGcANd2fMTPlSXTy1XZa3NBABHp9iPFzcRd1Iw6RdpZhjOdgdO11Bh3ijtrcUR33CxKZIHGag3VCQA/s1600-h/image%25255B6%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-2lEdgbklxR8/VLY29al--iI/AAAAAAAAHaY/Sn6HOyOoALM/image_thumb%25255B4%25255D.png?imgmax=800" width="446" height="55"></a></p> <h2>Problem</h2> <p>The Report designer sorts the columns alphabetically by the name of the month.</p> <h2>Solution</h2> <p>The months are supplied by the SSAS MDX in correct order, it is the report definition that sorts the resulting columns alphabetically.</p> <p>The solution is quite easy: remove the sort order from the Month Column Group.</p> <p><a href="http://lh5.ggpht.com/-mEu-qc472ms/VLY2-ENaRLI/AAAAAAAAHac/doG8D85KvJ4/s1600-h/image%25255B15%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-1_rfDCfqXbc/VLY2-umjEmI/AAAAAAAAHao/26k8NVXZ0Hg/image_thumb%25255B7%25255D.png?imgmax=800" width="244" height="40"></a></p> <p>in the Group properties of the Month column group, go to the Sorting options and remove the sort on the Month:</p> <p><a href="http://lh5.ggpht.com/-1IYjbwZrXic/VLY2_HZulGI/AAAAAAAAHas/2PAe4Gh0bpc/s1600-h/image%25255B18%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-JNmFuMfSz28/VLY2_ildHuI/AAAAAAAAHa0/CQjs2dW_qwY/image_thumb%25255B8%25255D.png?imgmax=800" width="244" height="77"></a></p> <p>So that no sorting is set:</p> <p><a href="http://lh4.ggpht.com/-s2APR53qGsQ/VLY3AEklskI/AAAAAAAAHbA/PYvrmAocwuw/s1600-h/image%25255B21%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-7BFIoOEosdY/VLY3Aq-GofI/AAAAAAAAHbE/k0hGAjVAUPc/image_thumb%25255B9%25255D.png?imgmax=800" width="244" height="83"></a></p> <p>The report shows the months in chronological order:</p> <p><a href="http://lh6.ggpht.com/-lUHMeyEzhps/VLY3BDnLilI/AAAAAAAAHbM/KtkYWovokr0/s1600-h/image%25255B25%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-i99Dk0V1oqM/VLY3BvtcgDI/AAAAAAAAHbY/PUPrxElTp0I/image_thumb%25255B11%25255D.png?imgmax=800" width="376" height="49"></a></p> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-61648048896842072092014-12-30T11:28:00.001+01:002014-12-30T11:40:49.550+01:00Embedding a simple Bing map in a blog post<p>Simple test to embed a bing map in a Blogger blogpost with Windows Live Writer.</p> <ul> <li>open maps.bing.com <li>search a location <li>click on the ‘Share’ link at the upper right hand side of the map <li>in the share dialog, click ‘Customize and preview’<br><a href="http://lh4.ggpht.com/-SMeR9t1L5SY/VKKBIOYRBPI/AAAAAAAAHZI/CaHSH6hjXqE/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-rL3_nBYef8U/VKKBIuTFraI/AAAAAAAAHZM/PDdjq8tSrQY/image_thumb.png?imgmax=800" width="244" height="140"></a> <li>In the customiez embedded maps dialog, click on the map to place a pushpin (blue dot)<br><a href="http://lh3.ggpht.com/-DhzgTvGB-xM/VKKBJMW4qwI/AAAAAAAAHZY/WQ7rSxY5GQs/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-3LS6FERAtFo/VKKBJ0UbXFI/AAAAAAAAHZc/A24p5ZyFLgE/image_thumb%25255B1%25255D.png?imgmax=800" width="244" height="148"></a> <li>Then click the Generate Code button, copy the code in this dialog window<br><a href="http://lh3.ggpht.com/-xJKADjG1Pbo/VKKBKWQDEpI/AAAAAAAAHZo/RfXVXawqwRU/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-fePpSR5Irdg/VKKBLPv-7gI/AAAAAAAAHZs/eHEqGVyw-QM/image_thumb%25255B2%25255D.png?imgmax=800" width="244" height="92"></a> <li>In windows Live Writer 2012, click on the ‘Source’ tab at the bottom of the Window. <li>In the source view, paste your code<br><a href="http://lh4.ggpht.com/-j3bJ-D9htfQ/VKKBLZAAVYI/AAAAAAAAHZ0/-_6pqg0suws/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-3lJgRyNtHl8/VKKBL5YkNEI/AAAAAAAAHZ8/CsjRoiDUgHk/image_thumb%25255B3%25255D.png?imgmax=800" width="244" height="187"></a> <li>Publish to your blog.</li></ul> <div><iframe height="400" src="http://www.bing.com/maps/embed/viewer.aspx?v=3&cp=50.812298~3.291944&lvl=13&w=500&h=400&sty=r&typ=d&pp=~~50.810534~3.297469&ps=&dir=0&mkt=en-us&src=SHELL&form=BMEMJS" frameborder="0" width="500"></iframe></div> <div style="margin: 12px 0px 0px"><a href="http://www.bing.com/maps/?cp=50.812298~3.291944&sty=r&lvl=13&where1=50.810534,3.297469&mm_embed=map" target="_blank">View Larger Map</a> | <a href="http://www.bing.com/maps/?cp=50.812298~3.291944&sty=r&lvl=13&rtp=~pos.50.810534_3.297469__&mm_embed=dir" target="_blank">Get Directions</a> | <a href="http://www.bing.com/maps/?cp=sg1kd2h87994&sty=b&lvl=18&where1=50.810534,3.297469&mm_embed=be" target="_blank">View Bird's Eye</a></div> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-80346891895661998742014-12-05T11:05:00.001+01:002014-12-05T11:05:47.722+01:00Test RESTful API with Postman REST Client<h3>Purpose</h3> <p>The Postman REST client aids in developing client software that needs to send and capture requests from RESTful web service APIs. <p>It is also a useful tool for the developer of the API because he can setup test examples, store them in text files and give them to the client developer as an example to develop his client and as an aid in debugging his client software. <p>Here you can read the <a href="https://www.getpostman.com/">Postman documentation</a> <h3>Install Postman REST client in MS-Windows with Google Chrome</h3> <ul> <li>Start Google Chrome</li> <li>Go to the extensions page <a href="chrome://extensions/">chrome://extensions/</a></li> <li>Click on <a href="https://chrome.google.com/webstore/category/extensions?hl=en-US">Get more extensions</a></li> <li>In the find text box type: postman rest client</li> <li>The result is shown her<br><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDlEID4a3wda6LyXqD11ThKv2PZUVOJsJ-8pwkonaBppPBCJTUMbk5wqqCLuXnlBY3YlqixnrvC3oX0_T8iQzcIIOtWd2bg3zaRrKfhq5UOqfwl5mWtVB9_QyrNsnWrWG-cWw/s1600-h/image%25255B7%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-P76uJ2XSD2o/VIGDZTI5IaI/AAAAAAAAHXs/3Ana-rGAHvY/image_thumb%25255B3%25255D.png?imgmax=800" width="364" height="187"></a></li> <li>In the ‘Postman – REST client (packaged app)’ frame, click on the FREE button.</li> <li>In the ‘Confirm New App’ dialog box, Click on ‘Add’ to install.<br><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuIiqynZ3SLWIPdBfMDgJHTV1rRkOIf4OLT2Ksb05lq_2EOq5LnvqOsTjIO0XWYE8hfGUR53c7Z7XQUgk_00kMVjugSAGDg-cHQry4JbgDH2MLBfpeVdhfam8JXlM8L4kwu_Q/s1600-h/image%25255B10%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-evdyfGrLk1A/VIGDaojoO-I/AAAAAAAAHYA/fUvfohz4ndI/image_thumb%25255B4%25255D.png?imgmax=800" width="244" height="157"></a></li> <li>The browser displays The Postman – REST client has been add to the new Chrome App Launcher<br><a href="http://lh6.ggpht.com/-hRKUq6x0i4U/VIGDbaBDDVI/AAAAAAAAHYI/V-MGD3WI3ks/s1600-h/image%25255B13%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-tanC3s8Vh1c/VIGDcF66mdI/AAAAAAAAHYM/STcJNV6hteY/image_thumb%25255B5%25255D.png?imgmax=800" width="244" height="233"></a></li> <li>You can effectively see the App Launcher in your Windows Taskbar. When you click on it the Launcher opens and the ‘'Postman” App is available<br><a href="http://lh6.ggpht.com/-Fo8Z_-We0vM/VIGDcml-1VI/AAAAAAAAHYY/MIrHmFSSgBQ/s1600-h/image%25255B16%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-VgtOiSBWKd4/VIGDdKDjbHI/AAAAAAAAHYg/jqhG28C0BVQ/image_thumb%25255B6%25255D.png?imgmax=800" width="180" height="244"></a></li> <li>Now you can launch Postman from the Launcher by clicking on its icon<br><a href="http://lh4.ggpht.com/-nNnp8rKbi10/VIGDd41v1TI/AAAAAAAAHYo/FWzeR8MjsaE/s1600-h/image%25255B20%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-gZ5zXvg0TKg/VIGDeqjfuAI/AAAAAAAAHYs/o70DZYtyJY8/image_thumb%25255B8%25255D.png?imgmax=800" width="287" height="187"></a></li></ul> <h3>Test a RESTful API with Postman</h3> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-72629661005775304172014-11-28T11:14:00.001+01:002014-11-28T11:14:14.685+01:00Connecting to a pre-SQL 2012 SSIS Instance with SQL 2012 SSMS<p>when I tried to connect to a SSIS SQL 2008 R2 instance with SQL 2012 SSMS I got the following error:</p> <p>Connecting to the Integration Services service on the computer “MyServerName” failed with the following error: “Class not registered”. <p>This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance. <p>I found a workaround proposed in <a href="http://www.ssistalk.com/2012/03/21/connecting-to-a-pre-sql-2012-ssis-instance-with-sql-2012-ssms/" target="_blank">this article by Phil Brammer</a> <p>In short, you have to edit the C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml file on your SSIS 2012 instance server. Then add folder entries with new folder names that point to the Pre-2012 version of SSIS instance. After restarting the SSIS service on your SSIS 2012 instance, start SSMS 2012 and connect to the the SSIS 2012 instance (NOT to the SSIS 2008). In this instance you will see the new folders, and when you fold open the folders you will see the packages installed in the SSIS 2008 instance. <p><a href="http://lh6.ggpht.com/-fM93i80XjdE/VHhK8TvcGlI/AAAAAAAAHXM/vXgD1E4cCMg/s1600-h/SSMS2012-SSIS2008%25255B2%25255D.png"><img title="SSMS2012-SSIS2008" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="SSMS2012-SSIS2008" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUrgnwCRBxpVIFACiip0TV8ShYEIjioN_R_eZg-yT_ZXAk3CVaAABhrU_ysAmns2Ud5GnGhwIp0OyxZv4ozUxtvZlMTwLZhi4LDlLXCaPvl3S-ha3fp7DpcyTyQaZjTZcduPM/?imgmax=800" width="244" height="193"></a> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0tag:blogger.com,1999:blog-31942401.post-12721585529788213112014-11-18T16:03:00.001+01:002014-11-18T16:03:33.184+01:00Add administrators to reports site after installation of Reporting Services<h3>Add administrators to reports site after installation of Reporting Services</h3> <h4>Problem</h4> <p>After installation and setup of the reporting services, when you browse to the reports site, you receive this error: <p>“User 'Domainname\Username' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.” <p><a href="http://lh3.ggpht.com/-YHl69bYQX-M/VGtfj4bouRI/AAAAAAAAHUQ/0lgDt0lkXQY/s1600-h/clip_image002%25255B5%25255D%25255B2%25255D.jpg"><img title="clip_image002[5]" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image002[5]" src="http://lh4.ggpht.com/-kxXLxQbkfaA/VGtfkj06BaI/AAAAAAAAHUU/NrMV832hYpA/clip_image002%25255B5%25255D_thumb.jpg?imgmax=800" width="244" height="52"></a> <p>You cannot even assign access because the Site settings link on the top right hand side is not available. <h4>Solution</h4> <p>2 steps need to be performed: <ul> <li>Add a windows user or group to the System Role Assignments and grant System Administrator rights.</li> <li>Add a role for this user or group at the root folder of the Report Server and grant</li></ul> <h5>Walkthrough</h5> <ul> <li>Log on locally to Windows Server running the Reporting Services with an account that is member of the local admin group.</li> <li>From the start button, run IE as administrator</li> <li><a href="http://lh3.ggpht.com/-NKhK_GM1Zig/VGtflCkfNLI/AAAAAAAAHUg/HGR-uT0XdsE/s1600-h/clip_image003%25255B4%25255D%25255B2%25255D.png"><img title="clip_image003[4]" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image003[4]" src="http://lh5.ggpht.com/-VJFdCbclZMU/VGtfl4AqhEI/AAAAAAAAHUk/LaPY8wgTDAU/clip_image003%25255B4%25255D_thumb.png?imgmax=800" width="244" height="108"></a></li> <li>In the UAC dialog, click Yes</li> <li><a href="http://lh5.ggpht.com/-4BbtWxXLcuY/VGtfm7pWQ5I/AAAAAAAAHUs/Bdy0z5G9QBM/s1600-h/clip_image005%25255B3%25255D.jpg"><img title="clip_image005" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image005" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFpgXlSoXu30kJeNP5L6ry3KWMogI3CBxJvxyJmCB-R2e5Z0mapZm0H-6exlZ99dMT1wHTi7oH7iWmw3am9RPVbv9IsP2dkFLJxs98DyV8ocRihKutiwy3vaImzBm2DoPxmok/?imgmax=800" width="244" height="126"></a></li> <li>In the IE address bar, browse to <a href="http://localhost/reports">http://localhost\reports</a></li> <li>This there is no error message and the Site Settings link is available</li> <li><a href="http://lh6.ggpht.com/-E2keMI0jzDQ/VGtfoFruIvI/AAAAAAAAHU8/wRoXyhl0VDY/s1600-h/clip_image007%25255B3%25255D.jpg"><img title="clip_image007" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image007" src="http://lh3.ggpht.com/-aQMhuQs1Y-A/VGtfou8tqWI/AAAAAAAAHVE/R-jcKTOGWE8/clip_image007_thumb.jpg?imgmax=800" width="244" height="51"></a><br>Click on the Site Settings link, Click on the Security tab in the left hand pane</li> <li><a href="http://lh5.ggpht.com/-GQMG_s-TEuw/VGtfpNNthVI/AAAAAAAAHVQ/W3ZmIjsD5Wg/s1600-h/clip_image009%25255B3%25255D.jpg"><img title="clip_image009" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image009" src="http://lh5.ggpht.com/-UDmyGe9FJ2o/VGtfp3auodI/AAAAAAAAHVY/x5hwcg5aCkg/clip_image009_thumb.jpg?imgmax=800" width="244" height="57"></a></li> <li>Click New Role Assignment in the toolbar above the list of groups and Users.</li> <li>In the New System Role Assignment page, add a Windows Group or username and assign a role, check System Administrator and System User. Click OK.</li> <li><a href="http://lh3.ggpht.com/-F1FrOqb5mcM/VGtfqnDFTBI/AAAAAAAAHVc/U9UB2gnG2MA/s1600-h/clip_image011%25255B3%25255D.jpg"><img title="clip_image011" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image011" src="http://lh4.ggpht.com/-Bnp6GEkgQW8/VGtfrCVUSoI/AAAAAAAAHVk/625IcJyy70g/clip_image011_thumb.jpg?imgmax=800" width="244" height="71"></a></li> <li>The new role is listed under Group or User.</li> <li><a href="http://lh3.ggpht.com/-hN4sOF36mgA/VGtfrk-dz5I/AAAAAAAAHVw/BdVjP6tlO9Y/s1600-h/clip_image013%25255B3%25255D.jpg"><img title="clip_image013" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image013" src="http://lh3.ggpht.com/-TiNNP_ZP4TE/VGtfsafOVnI/AAAAAAAAHV0/Dfwfmg0eJno/clip_image013_thumb.jpg?imgmax=800" width="244" height="47"></a></li> <li>Browse to the Reporting Services Home page, by clicking the Home link at the top of the page.</li> <li>Now you are in the Root folder of the Reports. In the toolbar, click Folder Settings.</li> <li><a href="http://lh4.ggpht.com/-tfypemjYxYI/VGtfsz_l-sI/AAAAAAAAHV8/jnazRBkabLM/s1600-h/clip_image015%25255B3%25255D.jpg"><img title="clip_image015" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image015" src="http://lh3.ggpht.com/-b4y6urUHtO4/VGtftfvWd9I/AAAAAAAAHWI/FH0DI7CwqHI/clip_image015_thumb.jpg?imgmax=800" width="244" height="77"></a></li> <li>In the Folder settings, there is only one tab, namely ‘Security’. Click on New Role Assignment.</li> <li><a href="http://lh3.ggpht.com/-neS8zbqRBKY/VGtfuPT_5KI/AAAAAAAAHWM/3Ktc9o77a9I/s1600-h/clip_image017%25255B3%25255D.jpg"><img title="clip_image017" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image017" src="http://lh5.ggpht.com/-Yoe9oH41Q6U/VGtfukjvSiI/AAAAAAAAHWY/q4OYMLSPd-M/clip_image017_thumb.jpg?imgmax=800" width="244" height="61"></a></li> <li>In the New Role Assignment page, type a group or username, check Content Manager. Click OK to create.</li> <li><a href="http://lh3.ggpht.com/-XGNBVuJayQ0/VGtfvfjK4CI/AAAAAAAAHWc/EnYjdTSE7AE/s1600-h/clip_image019%25255B3%25255D.jpg"><img title="clip_image019" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image019" src="http://lh6.ggpht.com/-H4K8_AtIEqo/VGtfv2mLakI/AAAAAAAAHWk/EiTqR5kB_VE/clip_image019_thumb.jpg?imgmax=800" width="244" height="94"></a></li> <li>The new role is assigned and listed in the list of Group or User.</li> <li><a href="http://lh3.ggpht.com/-TMoS-z5Lz7k/VGtfwdH-FsI/AAAAAAAAHWo/rwD6OTPar5w/s1600-h/clip_image021%25255B3%25255D.jpg"><img title="clip_image021" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="clip_image021" src="http://lh5.ggpht.com/-OF-20pO92sU/VGtfw_PW7uI/AAAAAAAAHWw/g9erTnP3AWM/clip_image021_thumb.jpg?imgmax=800" width="244" height="53"></a></li></ul> <p>With these 2 actions we granted a windows group or user administrative rights on the report server. From now on you can browse to the reports site on any client PC, without the need to run as administrator. <h3>To add users that can only browse reports</h3> <p>The following steps are needed: <ul> <li>Grant access to the root folder of the reports site.</li> <li>Grant access to the Folder with the reports that the user needs to consult.</li></ul> Jan D'Hondthttp://www.blogger.com/profile/13334079483657304103noreply@blogger.com0