Power-BI -Sample Report

SQL SERVER – Data Scrambling

declare @AlLChars varchar(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' --0123456789
--@AlLChars='123456789'

update h

set Fname= RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),3) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) +
' '+RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),5)

from TestTable h

SQL SERVER – JOB Status


SELECT  job.NAME NAME
                     ,job.job_ID job_ID
                     --,job.Originating_Server Originating_Server
                     ,run_requested_date run_requested_date
                     ,stop_execution_date stop_execution_date
                FROM msdb.dbo.sysjobs_view job
                INNER JOIN msdb.dbo.sysjobactivity activity ON (job.job_id = activity.job_id)
                WHERE run_Requested_date IS NOT NULL
                     AND job.NAME IN (N'xyz')
                ORDER BY run_requested_date DESC


SQL SERVER – EXECUTE SSIS PACKAGE FROM T-SQL


  
/*

GET THE ENVIRONMENT_REFERENCESID WHICH IS CONFIGURED FOR PROJECT

*/

DECLARE @REFERENCE_ID INT
     SELECT @REFERENCE_ID = REFERENCE_ID
           FROM SSISDB.[INTERNAL].[ENVIRONMENT_REFERENCES] ER WITH (NOLOCK)
           JOIN SSISDB.[CATALOG].[PROJECTS] P WITH (NOLOCK) ON ER.PROJECT_ID = P.PROJECT_ID
           JOIN SSISDB.[CATALOG].[FOLDERS] F WITH (NOLOCK) ON P.FOLDER_ID = F.FOLDER_ID
           WHERE F.NAME = 'FOLDERNAME' AND P.NAME = 'PROJECTNAME'    

 /*

GENERATE EXECUTIONID BY PASSING REQUIRED INFORMATION LIKE PACKAGENAME,FOLDERNAME,PROJECTNAME AND ENVIRONMENT_REFERENCESID

*/ 
     DECLARE @EXECUTION_ID BIGINT
     EXEC [SSISDB].[CATALOG].[CREATE_EXECUTION] @PACKAGE_NAME=N'PACKAGE1.DTSX', @EXECUTION_ID=@EXECUTION_ID OUTPUT, @FOLDER_NAME=N'FOLDERNAME', @PROJECT_NAME=N'PROJECTNAME', @USE32BITRUNTIME=FALSE, @REFERENCE_ID=@REFERENCE_ID
     SELECT @EXECUTION_ID
/*
PASS THE PARAMETER VALUES DYNAMICALLY IN T-SQL . HERE I AM PASSING @VARIABLE1 VALUE TO PARAMETERNAME1 LIKE BELOW
*/
     DECLARE @VAR0 INT = @VARIABLE1
     EXEC [SSISDB].[CATALOG].[SET_EXECUTION_PARAMETER_VALUE] @EXECUTION_ID,  @OBJECT_TYPE=20, @PARAMETER_NAME=N'PARAMETERNAME1', @PARAMETER_VALUE=@VAR0
/*
EXECUTE SSIS PACKAGE FROM T-SQL STATEMENT
*/  
     EXEC [SSISDB].[CATALOG].[START_EXECUTION] @EXECUTION_ID   


SQL SERVER – SCRIPT TO DROP NON CLUSTERED INDEX IF EXISTS


SELECT o.[name] AS [Table Name]
       ,i.[name] AS [Index Name]
       ,i.[fill_factor] AS [Existing Fill Factor]

       ,'IF EXISTS (SELECT * FROM SYS.INDEXES WHERE OBJECT_id=OBJECT_ID('''+QUOTENAME(s.name) +'.' + QUOTENAME(o.name)+'''' +') AND name = ''' + (Replace(Replace(i.name,'[',''),']','')) +''' )
        DROP INDEX ' + QUOTENAME(i.[name]) + ' ON '
           +  QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(s.[name]) +'.' + QUOTENAME(o.[name]) + ' '
           + 'WITH ( ONLINE = OFF )' AS [DROP INDEX Statement]
FROM [sys].[indexes] i
INNER JOIN [sys].[objects] o
     ON i.[object_id] = o.[object_id]
AND o.[type] = 'U' AND i.[name] <> 'NULL'
INNER JOIN sys.[schemas] s
     ON o.[schema_id] = s.[schema_id]
INNER JOIN [sys].[stats] st
     ON i.[name] COLLATE Latin1_General_CI_AS = st.[name] COLLATE Latin1_General_CI_AS
     AND o.[object_id] = st.[object_id]
     AND o.[type] COLLATE Latin1_General_CI_AS = 'U'
WHERE i.[name] NOT IN (SELECT i.[name]
                              FROM [sys].[objects] o
                              INNER JOIN [sys].[indexes] i
                            ON o.[object_id] = i.[object_id]
                              INNER JOIN [sys].[columns] c
                              ON o.[object_id] = c.[object_id]
                              WHERE c.[is_identity] = 1
                              AND o.[type] = 'U'
                              AND i.[type_desc] = 'CLUSTERED')
AND o.name NOT LIKE 'sys%'
ORDER BY o.[name]


SQL SERVER – DENSE_RANK() – ORDER BY SOME COLUMN BUT WE SHOULD ADD WHERE FILTER WHILE ASSIGNING RANK

Just another day I received following question from my friend and I find it very interesting. I decided to try it out on SQL Server. The question was – “Can we apply Dense_Rank function order by some Column but we should add where filter while assigning rank? ”





Interesting, isn’t it? So i took this as challenge and try to resolve this requrement as puzzle . So i took below small piece of paper and done some pseudo code. Then developed coding to get the final output

             


 --drop table #TEMPCASE  
 DECLARE  @StudentDetail TABLE  
  (    
    -- ID int identity(1,1),  
     StudentCode VARCHAR(10),  
     SchoolCode  VARCHAR(10),  
     StartDate DATE  
   )  
 INSERT INTO @StudentDetail VALUES ('C00001', 'P00001', '1-Sep-2014')  
 INSERT INTO @StudentDetail VALUES ('C00001', 'P00002', '1-Oct-2014')  
 INSERT INTO @StudentDetail VALUES ('C00001', 'P00003', '10-Oct-2014')  
 INSERT INTO @StudentDetail VALUES ('C00001', 'P00004', '25-Oct-2015')  
 INSERT INTO @StudentDetail VALUES ('C00002', 'P00001', '1-Oct-2014')  
 INSERT INTO @StudentDetail VALUES ('C00002', 'P00002', '1-Nov-2014')  
 INSERT INTO @StudentDetail VALUES ('C00002', 'P00001', '1-Oct-2015')  
 INSERT INTO @StudentDetail VALUES ('C00002', 'P00002', '1-Nov-2015')  
 INSERT INTO @StudentDetail VALUES ('C00003', 'P00002', '1-Nov-2015')  
 select * from @StudentDetail  
 select ROW_NUMBER() over(order by (StudentCode)) as ID,  
 ROW_NUMBER() over(partition by StudentCode order by StartDate) as RowID,cast(Null as int) as RankID,*   
 INTO #TEMPCASE from @StudentDetail  
 order by 1   
 --SELECT * FROM #TEMPCASE  
 declare @i int=1,@count int=(select MAX(RowID) from #TEMPCASE)  
 declare @RankID int  
 while @i<=@count  
  Begin  
   set @RankID=(select MAX(isnull(RankID,0)) from #TEMPCASE)  
           --IF exists(SELECT 1 FROM #TEMPCASE where RankID is null)   
           --      CONTINUE   
           --  ELSE  
           --      BREAK  
   update tt  
   set RankID=@RankID+t.RankID  
   from #TEMPCASE tt  
   join (  
   select *,DENSE_RANK() over(order by StudentCode) as RankID  
   from   
   (  
   select distinct t1.ID,t1.StudentCode from #TEMPCASE t1  
   join #TEMPCASE t2  
   on t1.StudentCode=t2.StudentCode  
   where t1.RankID is null and t2.RankID is null and t1.RowID=1 and t2.RowID>1  
   and DATEDIFF(dd,t1.StartDate,t2.StartDate)<=90  
   union all  
   select distinct t2.ID,t2.StudentCode from #TEMPCASE t1  
   join #TEMPCASE t2  
   on t1.StudentCode=t2.StudentCode  
   where t1.RankID is null and t2.RankID is null and t1.RowID=1 and t2.RowID>1  
   and DATEDIFF(dd,t1.StartDate,t2.StartDate)<=90  
   union all  
   select distinct t1.ID,t1.StudentCode from #TEMPCASE t1  
   where t1.RankID is null and not exists (select 1 from #TEMPCASE t2 where t1.StudentCode=t2.StudentCode   
   and t2.RankID is null  
   group by t2.StudentCode having COUNT(*)>1)  
  ) t  
  ) t on tt.ID=t.ID  
  update t1  
  set RowID=t.RowID  
  from #TEMPCASE t1  
  join(  
  select ID, StudentCode,ROW_NUMBER() over(PARTITION by StudentCode order by StartDate ) as RowID from #TEMPCASE  
  where RankID is null  
  ) t  
  on t.ID=t1.ID  
  set @i=@i+1  
  End  
 SELECT * FROM #TEMPCASE  



SQL SERVER – SEQUENTIAL NUMBERS FAST LOAD TO TABLE

Problem
I need to load very fastly some sample data to database table with Sequential Numbers. We can load Sequential Numbers with Loops but it is a performance issue because it will load one record in one iteration . What is an alternative for loops to load Sequential Numbers data into table ?

Solution
By using iteratative Common table exepresion(CTE) , we can able to get the results very fastly . In the below query every CTE will sequres the amount of result come from previous CTE results.

CREATE TABLE #T1(Id int NOT NULL PRIMARY KEY, X INT NOT NULL)

CREATE INDEX Ix ON #T1 (X, Id)

;WITH 
    Pass0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows 
    Pass1 AS (SELECT 1 AS C FROM Pass0 AS A, Pass0 AS B),--4 rows 
    Pass2 AS (SELECT 1 AS C FROM Pass1 AS A ,Pass1 AS B),--16 rows
    Pass3 AS (SELECT 1 AS C FROM Pass2 AS A ,Pass2 AS B),--256 rows
    Pass4 AS (SELECT 1 AS C FROM Pass3 AS A ,Pass3 AS B),--65536 rows
    Pass5 AS (SELECT 1 AS C FROM Pass4 AS A ,Pass2 AS B),--1048576 rows
    Cte AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Pass5)
INSERT INTO #T1
SELECT Number, Number
FROM  Cte
WHERE Number <= 100000

SSIS – Combination of Parent Package Configuration and SQL Server Package Configuration along with Environment variable

Problem
As explained in this post SQL Server Package Configuration,we can able to make use of SQL Server configuration table values inside SSIS packages by using Environment variables and SQL server configuration . In SSIS Configuration table , it is storing one record for one package variable. If i have 20 + packages , which are  Source and destination connections. then we have to create 40 + configurations in SISConfiguration table , even though all source and destinations are same because SSIS Configuration table is saving configurations based on Package variables.

Solution
By using Parent Package variable we can able to use only two records in SQL Server Configuration table instead of each package connection string i.e. one record for Source connection and another for Destination Connection.

What is Parent Package Variable 
If a package calls another package using execute package task, then calling package is referred as Parent package and called package is referred as child package. When parent package variable is to be passed to a child package then it is called Parent Package Variable configuration

Sample Package Development :
To explain this , I have created two Packages . One is called as MasterPackage and Another one is called ChildPackage.

Master Package Setup :
In Master Package , i have taken simple execute package task and configure like below . Choose Location of package in File System and Browse child Package and Click on OK to complete .


Set Up Environment Variable in Master Package :
Screenshot 1 :  Right Click on Control flow task window and choose Package Configuration tab


 
Screenshot 3 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable

Screenshot 4 : In next wizard screen choose ConnectionString of ConfigConnection under Connection Manager section and click Next and in next screen give name as ConfigConnection and click Ok to save .

Set Up SQL Server Configuration in Master Package :
Screenshot 1 :  Right Click on Control flow task window and choose Variables and created two variables SourceCon and DestCon. In this two variables , values inside SQL Server Configuration tables will be copied by using SQL Server Configuration

Screenshot 2 : By using ConfigConnection manager ,Value of Souce Column inside SSISConfiguration Table will be Copied to package variable SourceCon and Repeated same steps for DestCon also




Child Package Set Up :
Following steps enable passing a parent package variable to child package:
1. Add a configuration to the child package
2. Select its configuration type as Parent Package Variable
3. Give the name of the parent package variable as 'SourceCon'


4. Click Next and select the Child package  SourceConnection ConnectionString as shown in below (by doing this, the Child packageSourceConnection ConnectionString's value
gets overwritten with the value of parent package variable).Continue for DestCon also.




SSIS – SQL Server Package Configuration

Problem

After we develop SSIS Packages, if we want to move developed packages to another server/ environment ,we need to change so many configuration settings in each SSIS Packages like DB Connection strings, File path and etc. But there is so much of work around this task. How we can take the advantage of the SQL Server Package Configuration which are providing by SSIS ?

Solution

SSIS offers serveral types of package configurations like environment variables,Parent Package and XML Configurations and SQL Server Configuration . In all types of configurations , parameter values are storing outside of SSIS packages and that values are utilizing while executing packages. In this post i am going to explain an approach for implementing SQL Server based package configuration that uses an environment variable to facilitate the deployment of the packages onto different servers without having to modify the packages

What is SQL Server package configuration ?

SQL Server package configuration is nothing but storing configuration values like Connection strings ,File path etc in SQL server table. To utlize this values inside SSIS Packages, we need to create one configuration connection manager inside SSIS package which tells, in which server and which database this SSIS Package Configuration table is exists. lets call this connection name as  'ConfigConnection'.

What is Environment Variable ?

Environment Variable is an variable created at machine level(Operating System level) and we can give the value to the Environment Variable and can use this value inside any of the program which are runing in the machine . In our example we will use SSIS_CONFIG_DB Environment Variable inside our SSIS package. So i have created one SSIS_CONFIG_DB Environment Variable at machine level and given below value

Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;


We will see later in post how to create Environment Variable and edit value in this Variable

What is need of environment Variable to make use of SQL Server package configuration ?

As i explained above, to get the configuration values of SQL Server table , we have to create one connection (ConfigConnection ). Since during the development we are pointing to a development DB instance, we need to change the connection information point to the QA or Production source databases without editing the package each time.

To notify what is connection string of ConfigConnection to SSIS package , we have to use environment variable which is already having the connection string value This allows for the package to point to configuration table based on Environment (dev, QA, test, prod, etc).Each configuration table shall have the configuration values that are appropriate to each environment.The environment variable shall exists in all machine where you intend to deploy the package, and its content should be updated to accordingly.

Advantages of SQL Server package configuration over the other package configurations :

  • DBAs are usually more comfortable working with SQL Server tables than XML files.
  • Storing package configuration values in a SQL Server database rather than on the file system 
  • Simple T-SQL commands such as INSERT, UPDATE and DELETE. 
Set Up Environment Variable :


Choose Advance System Settings

 Choose Environment Variables


Sample Package Development : 
Let’s assume we have a package that has a connection manager called ‘SourceConnection’, which has the connection information of the source DataBase where the package is trying to retreive data from. Also we have one more connection manager called ‘DestinationConnection’ where the data is going to be load.

Screenshot 1: Sample Package which has one Data flow task , two OLEDB connections i.e. one Source Connection and one destination connection



 
Screenshot 3 :  Right Click on Control flow task window and choose Package Configuration tab


 
Screenshot 5 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable

Screenshot 6 : In next wizard screen choose ConnectionString of ConfigConnection under Connection Manager section and click Next and in next screen give name as ConfigConnection and click Ok to save .

Screenshot 7 : Again go to Package Configuration wizard and click on Add to add new configuration for Source 


Screenshot 8 : Choose SQL Server type in Configuration Type And in Specify Configuration tab, Choose ConfigConnection String and Choose Configuration Table (for first time we need to create new table ,by clicking New else we can choose from drop down)
 
Screenshot 9: Choose Configuration Filter from drop down list (for new record entry in table just type 'Source' then it will insert one entry in Configuration table else choose from drop down list) to utilize value from SQL Server table dynamically , when package is executing 

Screenshot 10 : Next screen , choose SourceConnection ConnectionString to copy dynamically from the SQL Server table value to SourceConnection ConnectionString


Screenshot 11: Repeat same steps for Destination Connection Also



Once we have developed our packages like above, if we want to deploy to another server like QA or Production , We just need to Create One Environment Varible at Server level and need to change connection strings  values in Configuration table 

Note : Once we create Environment Varible at new Environment , we need to restart that machine to identify in SSIS Packages

SQL SERVER – RUN T-SQL SCRIPT FILES USING SQLCMD WITHOUT OPEN IN SSMS

In Software World, Source of Knowledge is requirement

J.

Today I got big SQL Script (600 MB) to execute in SSMS. But due to big file it was not opened in SSMS. So I was searching for solution to execute this file without opening .sql file in SSMS, I found solution in by using sqlcmd we can able to run a Transact-SQL script file. By using SQLCMD we can execute any type SQL Scripts.


To demonstrate, how to use SQLCMD. I have taken simple SQL Script and saved in Notepad and given name as SQLCmdSample.sql and Save the file in D Drive


SQL SERVER – HOW TO DELETE ALL TABLES DATA EXCEPT TOP N RECORDS IN EACH TABLE?

I always likes logical work than regular boring work. So when I am browsing on Google, I found some interesting question like how to delete all tables’ data in SQL Server except top N records in each table?

Here is the script for delete all tables data SQL Server DB except top N records in each table

 
Note:

Considering all tables do not have any inter dependency on each other


SQL SERVER -- SQL SERVER 2016 WHAT'S NEW ?

1      Query Store
a)      Captures queries, query plans, runtime statistics, etc.
b)      Can enforce polices to direct SQL Server Query processor to execute in specific manner
c)      Contains query work load history
d)      Helps system-wide or database level performance analysis and troubleshooting
e)      New system views are added to support Query store

2      Live Query Statistics
a)      Live Query plans with system resources consumption such as CPU, memory for all running queries in the plan
b)      You can drill down further into live running queries from its query plan
c)      Helps you watch change of statistics during query execution

SSIS – CONFIGURE ENVIRONMENTS AND ENVIRONMENT VARIABLES

As we see above, we can create folder in SSISDB. Each folder we create, it will automatically create two subfolders called Projects andEnvironments 
Projects: A place holder for deployed projects inside Folder
Environments: A placed holder for environments like DEV and QA
To create an environment, right click on the Environments subfolder and click on Create Environment menu item as shown below:




















SSIS – DEPLOY DEVELOPED SSIS PROJECT TO INTEGRATION SERVICES CATALOG

After development of our SSIS package to SSISDB, to deploy SSIS Project,just right click on project file and choose Deploy

















www.CodeNirvana.in

Powered by Blogger.

Translate

Total Pageviews

Copyright © Journey to MSBI World With Kiran Reddy | Blogger Templates | Designed By Code Nirvana