Power Shell Commands

 $psversiontable : Provides version of windows PowerShell

Get the cube size from PowerShell script

#### SSAS Size ###

Param($ServerName=”localhost”)

$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)

$server = New-Object Microsoft.AnalysisServices.Server

$server.connect($ServerName)

if ($server.name -eq $null) {

When below powershell query is executed, we will get cube size on the local server

Write-Output (“Server ‘{0}’ not found” -f $ServerName)

break

}

$sum=0

foreach ($d in $server.Databases )

{

Write-Output ( “Database: {0}; Status: {1}; Size: {2}MB” -f $d.Name, $d.State, ($d.EstimatedSize/1024/1024).ToString(“#,##0") )

$sum=$sum+$d.EstimatedSize/1024/1024

}

$SizeGB=$Sum/1024

write-host ‘Sum of Database = ‘$sum ‘ MB’

Write-host ‘Total Size of Cube Databases =’ $SizeGB ‘ GB’ 

Import Data to excel from tabular model using DAX

https://www.sqlbi.com/articles/import-data-from-tabular-model-in-excel-using-a-dax-query/

Import JSON from SQL

SELECT
       p.Id,
       p.FirstName,
       p.LastName,
       p.BirthDate
FROM OPENROWSET (BULK 'C:\temp\PeopleLarge.json', SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn)
WITH (
       Id INT,
       FirstName VARCHAR(50),
       LastName VARCHAR(10),
       BirthDate DATETIME
) AS p

Reference : http://www.sqlservercentral.com/articles/SQL+Server+2016/175784/?utm_source=SSC&utm_medium=pubemail

Interview Questions

Interview 1

1. Explain about the project
2. Tell me abou the challenges faced in your project
3. Different configuration options in ssis
4. Difference between union and union all
5. can we use cte inside while loop
6. diiference between row_num, rank and dense_rank
7. what are confirmed dimensions and factless fact table
8. Tell me about SCD
9. In SSRS report, I have a subreport. I do not want the data set for the subreport to be executed. How do you do it.
10. I have an excel with 10 sheets. I want data of all 10 sheets to be loaded to a single table

Interesting T - SQL Queries (T - SQL Interview Questions)

Question 1
Write a single DML statement to replace 0 with 1 & 1 with 0?

Answer
create table #temp

(nm varchar(10),

id int)



insert into #temp values

('one', 1), ('zero', 0), ('one', 1), ('zero', 0), ('one', 1),

('zero', 0), ('one', 1), ('zero', 0), ('one', 1), ('zero', 0)



select * from #temp



update #temp

set id = case when id = 1 then 0

when id = 0 then 1

end



select * from #temp


Question 2
Write a Query to get maximum 3rd salaried employee
create table #emp

(empid int,

empsal money)

insert into #emp values(1, 25000), (2, 30000), (3, 15000), (4, 40000), (5, 22000) ,(6, 18000)

select * from #emp order by empsal desc



--using top keyword
 select top 1 * from



(
  select top 3 * from #emp

order by empsal desc

)a

order by empsal asc



--using ranking function
 select empid, empsal



from
 (
  select empid, empsal, RANK() over (order by empsal desc) rnk

from #emp

)a

where a.rnk = 3



--using corelated subquery
 select *

from #emp e1

where (3-1) = (select COUNT(distinct (e2.empsal)) from #emp e2 where e2.empsal > e1.empsal)

Specifying the password of SSIS Package while executing it as SQL Job

Normally when we create SSIS packages, in connection managers we may specify the password for some connections. It can be SQL authentication for an OLEDB connection, FTP connection or something else.

When we specify such passwords which are sensitive information, either we should create a config file which stores these passwords or we have to save the package as "EncryptSensitiveWithPassword" and speficy the password for the package.

Whenever we specify a password for the package, and later when we want to execute that ETL using SQL job, we have to specify the password details even in the job.

Else, we may end up getting the below error:
Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information.

To avoide this error, in SQL job, under "Command Line" properties, choose the option "edit the command line manually" and then type password just after "/DECRYPT".

Command line may look as below:
/FILE "D:\TestETL.dtsx" /DECRYPT pwd123  /CHECKPOINTING OFF /REPORTING E


Here pwd123 is the password for the ETL TestETL.dtsx

 

Text-Qualifier only to the values which has a comma(seperator) in it

We generally get requirements to load data to a flat file destination or csv files.
When we load such data, we may see that value itself has a comma or a separator in it.

During such cases we usually specify text-qualifier in the flat file connection manager properties.
Whenever we specify text-qualifier like that, all column values have these text-qualifier, irrespective of whether the value has a comma in it or not.

InCase, if the customer asks us to text-qualify only to those values which has a comma in it, then below is the work-around solution for that.
Take a derived column transformation and add the below expression:

(FINDSTRING(column_name,",",1) == 0) ? column_name : "\"" + column_name+ "\""
 

Avoiding text-qualifier in the header rows while loading data to a flat file destination

Normally we get a requirement to process data from database and load the data to flat file destination.
Sometimes, we may have to add a text-qualifier as the value itself may have a separator in it.
Example: if its a csv file, we may have a value as SurfMyAds.com, Inc.

In such cases, we may have to enclose such values inside a text-qualifier like "SurfMyAds.com, Inc."

Now, when we specify text-qualifier in flat-file connection properties, even the column header also gets text-quailifer. May be something as shown below

"Name","Salary","DOJ","Department"
"Sachin","8000","2014-01-01", "Finance"
"Rahul","5000","2015-01-01","HR"



Now

But, we do not need text-qualifier in header rows.
To satisfy this requirement, we have to disable the option of "column names in the first row" in the flat file connection manager, as shown below

Later, in the data flow task, open the flat file destination. In the connection manager properties, under the section "Header" specify the column heading that is required. Sample has been shown below:

Once these settings are done, header rows in flat file destination will not have text-qualifiers




 

Fragmentation in SQL Server

Use the below query to determine the Fragmentation percentage for the tables in a particular database.

SELECT ps.database_id, ps.OBJECT_ID,o.name,

ps.index_id, b.name,

ps.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

AND ps.index_id = b.index_id

inner join sys.objects o

on ps.OBJECT_ID = o.OBJECT_ID

WHERE ps.database_id = DB_ID()

ORDER BY ps.avg_fragmentation_in_percent desc

 

Re-organize the index if Fragmentation percent is > 5% and < 30%

Re-Build the index if Fragmentation percent is > 30%

 

Syntax for Re-Organize

 

ALTER INDEX <<index_name>> ON <<table_name>>

REORGANIZE ;

Example:

ALTER INDEX IX_FactEmail_EventDateKeyStoreKey ON fact.email

REORGANIZE ;

Syntax for Re-Build

ALTER TABLE <<table_name>> REBUILD

Example:

ALTER TABLE LatestEmailFileDates REBUILD

 

Unable to see download icon for SSRS Report in Report Manager

Recently I was working on SSRS 2008 R2 version and I had to analyse one SSRS report which was already published in Report Manager.

Normally in SSRS 2005, we get an "Edit" option from which we can download.

I knew this option to download RDL file was improved in SSRS 2008 R2. If we just hover over the SSRS report and click the icon we get many options such as download/subscription and many more.

But this time I was not able to find a single option.

It was looking some thing as below

Solution:
From one of the forum , I got to know that report link needs to be added to the "Compatibility View Setting" of the browser.

To do this, in IE, click the setting button and select "Compatibility View Settings" as shown below:

Now, add the link in the box and click Add as shown below and then close the window

Now, when I went back to report manager and refreshed it, I was able to see all the options and I could download the RDL file

Reference :
http://stackoverflow.com/questions/15144158/can-not-open-the-drop-down-arrow-besides-the-report-in-ssrs-report-manager-to-ad

 

Loop Multiple Kind of Files using Regular Expression

In SSIS, we normally process multiple files in a folder using ForEach Loop Container. This container has a wildcard filter, where we can specify the type of files to be processed.

But there can be cases, where folder may contain multiple kinds of files and we have to process 2 or 3 kinds of files from them. During such cases, single wildcard filter won’t help us out.

Example for the above scenario:

Some time back we had a requirement, to pull 6 set of FTP files daily. All these files were in zipped format.

All zipped files (except one), were in “.csv” format and only one file was in “.txt” format.

Now, directly if we unzip and process files using ForEach, it will try to load the data even from zipped files to a table. But this will throw an error. To resolve this we have 3 options:

1.       Move .zip files to some other folder using another ForEach

2.       Create 2 separate ForEach, one for .csv and another for .txt

3.       Next option is to ignore the .zip files and process only .txt and .csv files

 Here is a step-by-step process on how the third option can be achieved

Suppose, we had to process all the files which had a name “SSIS” in it and all were in “.csv” format:

Inside ForEach Loop, under Files section we would have given *SSIS*.csv, something as below:

 

But now along with .csv file we need to process”.txt” file which has a word SSIS in its filename.

i.e., we need to process both “.txt” and “.csv” files which has a word SSIS in its filename

To loop such multiple kinds of files, we can use regular expressions. Detail on how it can be used in SSIS is given below

Create 2 variables

1.       RegExFilter: Data Type String; Value = ^.*\.(txt|csv)$

2.       PassesRegexFilter: Data Type Boolean;

 

Using above 2 variables, we will check if the file which will be processed is a csv or txt file using a Script Task.

Drag a Script task inside ForEachLoop container and add the below code( C# code )

/*

   Microsoft SQL Server Integration Services Script Task

   Write scripts using Microsoft Visual C# 2008.

   The ScriptMain is the entry point class of the script.

*/

 

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.Text.RegularExpressions;   // Added

using System.IO;                        // Added

 

 

namespace ST_8102847e64a74fb5ac4b523cfa570381.csproj

{

    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

 

        #region VSTA generated code

        enum ScriptResults

        {

            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        };

        #endregion

 

        /*

            The execution engine calls this method when the task executes.

            To access the object model, use the Dts property. Connections, variables, events,

            and logging features are available as members of the Dts property as shown in the following examples.

 

            To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;

            To post a log entry, call Dts.Log("This is my log text", 999, null);

            To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

 

            To use the connections collection use something like the following:

            ConnectionManager cm = Dts.Connections.Add("OLEDB");

            cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

 

            Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

           

            To open Help, press F1.

      */

 

 

        public void Main()

        {

            // Get the filename from the complete filepath

            String FileName = Path.GetFileName(Dts.Variables["User::ImportFile"].Value.ToString());

 

            // Create a regex object with the pattern from the SSIS variable

            Regex FileFilterRegex = new Regex(Dts.Variables["User::RegExFilter"].Value.ToString());

 

            // Check if it is match and return that value (boolean) to the SSIS variable

            Dts.Variables["User::PassesRegexFilter"].Value = FileFilterRegex.IsMatch(FileName);

 

            Dts.TaskResult = (int)ScriptResults.Success;

 

        }

    }

}

This code will check if the file is txt /csv. If yes then the variable PassesRegexFilter  is set to true.

After this task, add other logic to process the file. Prior to these task logics, add a script task and connect them using an expression @[User::PassesRegexFilter] == True as below

 

The ForEach Loop looks something as below

 

Reference:

http://microsoft-ssis.blogspot.com/2012/04/regex-filter-for-foreach-loop.html

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec7a6adb-2a52-49ba-8cde-56f67f2fc4d9/specifying-multiple-file-types-in-ssis-foreach-loop-container-eg-txt-and-dat?forum=sqlintegrationservices

 

Downloading Files from SFTP

Normally, if we want to download FTP files from SSIS, we have FTP task through which it can be easily done. But, if we have to download files from SFTP(Secured FTP), then the regular FTP files don’t work

We need a third-party tool to connect to SFTP. Normally winscp is used which is a Free Ware.

Usual Development Scenario:

Normally while coding, we open SFTP using winscp to check if all files are there. For the first time, when we open SFTP, it will prompt a message whether to cache it’ key. When we submit YES button, from then on it goes on smoothly.

Caching this host key is user specific. So when the SSIS package which connects to SFTP using this winscp, will be the same credential which had opened winscp manually. So there won’t be any issues while developing the ETL and the running the package using BIDS.

Problem:

Normally there will be an error when this SSIS which connects to SFTP is run as SQL job

Reason

Usually SQL job will run the job using a specific ID like “Service Account”. For this SQL Service Account, the host key for SFTP was not cached. So there will be an error as below

The server's host key was not found in the cache.

Along with this error, usually if debug we get even the host key which was missing

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

The server's host key was not found in the cache.
You have no guarantee that the server is the computer you think it is. 
The server's dss key fingerprint is:  ssh-dss 1024 c1:70:cb:3b:27:16:10:ed:a5:c8:e5:ba:8f:1b:ec:ad 
If you trust this host, press Yes.
To connect without adding host key to the cache, press No.
To abandon the connection press Cancel. 
Continue connecting and add host key to the cache?  (Y)es, (N)o, C(a)ncel, (C)opy Key: Cancel  Host key wasn't verified!  Authentication failed.

 

Solution

There are 2 solutions for this

1.       Include the hostkey while connecting to SFTP as below

ConnectString = "open " + FtpType.ToLower() + "://" + Username + ":" + Password + "@" + Host + ":" + Port + " -hostkey=" + """ssh-dss 1024 c1:70:cb:3b:27:16:10:ed:a5:c8:e5:ba:8f:1b:ec:ad"""

Reference: http://winscp.net/eng/docs/scripting#hostkey

2.       Other option is to login to the system using SQL Service Account and connect to SFTP and cache the host key. This can be done only by the Windows IT team.