Search This Blog

Loading...

Sunday, August 14, 2016

Interview Questions on SSIS

This post will be helpful to all the SSIS Developers who are looking for change or wanted to build their carrier in SQL Server Integration Services.

Below interview questions will help you out to crack interviews in SQL Server Integration Services irrespective of how much experience you have

1. What is Control flow and Data flow in SSIS?
2. SSIS Architecture
3. Current SSIS Project your working on, can you create a diagram showing control flow of your SSIS package?
4. Best ways to optimize your SSIS Package?
5. Difference between Merge and Union ALL transformation?
6. What are dimension and facts, How you are loading dimension and facts in your DW?
7. How you are performing Incremental load in your database?
8. What is SCD and it types, which SCD Type you are applying in your DW?
9. Can you call temp tables in your SSIS Package? If yes then how?
10. What's the most complex thing which you have faced in SSIS?
11. Different type of errors in SSIS, Error Handling in SSIS?
12. Break points in SSIS?
13. Check points in SSIS?
14. Event handler and its usage in SSIS?
15. How you are deploying your package in SSIS?
16. Project Deployment in SSIS? 
17. Blocking, Semi blocking and non-blocking transformations in SSIS?
18. SCD transformation brief explanation?
19. Term extraction transformation in SSIS?
20. Isolation properties. Default Isolation property in SSIS?
21. Derived column with an example?
22. Type of Enumerator in for each with an example?
23. Difference between For and for each in SSIS?
24. Character map transformation and its usage?
25. Transactions in SSIS?
26. How to schedule a SSIS package on daily basis?
27. Type of configuration and its usage in SSIS?
28. Environmental variable in SSIS?
29. How to load multiple sheets in a single destination and then moving it to Achieve folder?
30. Calling child package in Parent Package, how to use Parent package variable in child package?
31. Difference between lookup and merge join transformation?
32. When you have used script task in your SSIS Package?

There are lots of other questions also that an Interviewer can ask these are just the common question in most of the companies.Hope these questions will help you out.All the best. 

Note: For Interview Question on SQL Server and SSRS Read my Previous Posts:




Sunday, July 31, 2016

Interview Questions on SSRS


This post will be helpful to all the SSRS Developers who are looking for change or wanted to build their carrier in SQL Server Reporting Services.

Below interview questions will help you out to crack interviews in SQL Server Reporting Services irrespective of how much experience you have
  1. What is the difference between SSRS 2005, SSRS 2008,2012, 2014 & SSRS 2016? 
  2. What is Sub Report, How to Pass Main Report Parameter to sub Report? 
  3. What is Dashboard, how to create Dashboard in SSRS ? 
  4. Explain what is the difference between Tabular and Matrix report? 
  5. What is the difference between Drill Down and Drill through Report? 
  6. How many ways you can deploy a Report in Report Server? 
  7. What is difference between Report Server in Native Mode vs Report Server in Share point mode? 
  8. What is Report Server db and Report Server temp DB? 
  9. What is the difference between IIF and Switch condition? 
  10. How to apply Alternate Colour on Rows in SSRS? 
  11. Can we write custom code in SSRS ? IF yes then why and where we can write? 
  12. What is Linked Report and its usage? 
  13. What is the difference between Report Server and Report Manager? 
  14. Difference between Query Parameter and Report Parameter? 
  15. What is the use of list in SSRS with an example ? 
  16. What is caching and Snapshot in SSRS, what is the difference between them? 
  17. What are subscriptions and Type of subscriptions? 
  18. How to find longest running Reports ? 
  19. How to debug Reports which are taking lots of time to Preview? 
  20. What is cascading of Parameters in SSRS? 
  21. Does changing the Parameter order will affect the Report ? 
  22. Report builder and it’s usage ? 
  23. Difference type of charts in SSRS, Can we plot secondary Y axis in the chart? 
  24. What are the different types of data sources in SSRS? 
  25. What is document map in SSRS?
There are lots of other questions also that an Interviewer can ask these are just the common question in most of the companies.Hope these questions will help you out.All the best. 

Note: For Interview Question on SQL Server Read my Previous Post- http://www.sqlandssrssolutions.com/2016/06/interview-questions-on-sql-server.html

Thursday, June 30, 2016

Interview Questions on SQL Server

This post will be helpful to all the SQL Server Developers who are looking for change or wanted to build their carrier in SQL Server.

Below interview questions will help you out to crack interviews in SQL Server irrespective of how much experience you have
  1. Difference between Clustered Index and Non Clustered Index?
  2. What is Column Store Index?
  3. Difference between Primary, foreign and unique key?
  4. Difference between Function, SP and view?
  5. What are Joins, Explain all type of joins with an example?
  6. Difference between CTE, Temp Table and Table variable?
  7. Difference between Delete, Drop and Truncate?
  8. Best ways to optimise your Stored Procedure?
  9. Why to use SP when you can directly write the SQL Query?
  10. What is Trigger, where you have used Trigger?
  11. What is Cursor, did you ever used Cursor, why to avoid cursor, Alternate of Cursor?
  12. Best optimization techniques and Performance tuning in SQL?
  13. What is covered Index and Filtered Index?
  14. What is Normalisation, Type of Normalisation, Your Project DB is in which normalised form and why?
  15. What is Data ware house?
  16. What is Data Mart?
  17. Difference between Database, Data ware house and Data Mart?
  18. What are facts and dimension?
  19.  Difference between SQL 2005, 2008 R2, 2012, 2014 and 2016?
  20. Ranking Functions in SQL with difference between Row-number,Rank and Dense Rank ?
  21. Pivoting and UnPivoting of Data in SQL?
  22. Difference type of Isolation's level in SQL and their use?
  23. What is deadlock,blocking and dirty Read ?
  24. Different type of System database and their usage?
  25. Different type of locks in SQL ?
  26. What is materialised view with an example?
  27. What is the difference between Union and Union All, which is good for optimization?
  28. What are transactions, How you are doing transactions in your's sp ?
  29. What are ACID Properties with an example of each property ?
  30. Common SQL queries
  • Delete Duplicates
  • Max Salary Department Wise
  • 2nd Highest Salary
  • Recursive CTE Code
  • Update Gender Column Male to Female and Female to Male
There are lots of other questions also that an Interviewer can ask these are just the common question in most of the companies.Hope these questions will help you out.All the best.


Monday, May 30, 2016

Lead and Lag Function in SQL


I often heard in various forums about the use of lead and Lag Function, since most of the developers are still working on 2008 R2, so they are not aware of these two Lead and Lag functions.Below I will explain the Lead & Lag Function with a simple example

SQL Server 2012 introduces new analytical function LEAD() and LAG(). These functions accesses data from a subsequent row (for lead) and previous row (for lag) in the same result set without the use of a self-join.

First I will explain what exactly Lead and Lag is with an example

Below is a list of 5 records from A to E

A, B, C, D and E

 As per Lead definition- “Lead Accesses data from a subsequent row in the same result set without the use of a self-join”

So in our example Since A is the first value so B is leading A Similarly C is leading B Next D is leading C and E is leading D and since E is the last Record and doesn’t have any subsequent Row so the Lead Value of E is NULL as shown in below table structure

Name   LeadValue
A          B
B          C
C          D
D          E
E          NULL

Now using the same example I will show the Lag values

As per LAG definition- “Accesses data from a previous row in the same result set without the use of a self-join”

So in our example Since A is the first value so A doesn’t have any lag value so it will have LAG Value as NULL. Now Since A is coming before B so B has a lag value as A Similarly C has lag values as B, next D has a Lag value which is C, and E has lag value which is D as shown in below table structure

Name   LagValue
A          NULL
B          A
C          B
D          C
E          D

Now, I will use the above example to use Lead and Lag Function in SQL Server

--Test table Script with above example

create table #t(id int identity(1,1), name varchar(100))

insert into #t values('A'),('B'),('C'),('D'),('E')

--Using Lead & Lag Function to Get the Leading & Lagging value
SELECT *,
LEAD(Name) OVER (ORDER BY ID) LeadValue,
LAG (Name) OVER (ORDER BY ID) LagValue
FROM #t




This is just a basic and very simple example of Lead and Lag function, in real time scenario’s there will be many situations where these functions are quite handy.

NOTE: Lead & Lag functions only works with SQL 2012 & above versions



Saturday, April 30, 2016

How to Display a variable value in SSIS


Some time in order to debug variables in SSIS you want to see what’s the values that the variable is holding or you want to check the Result set which is stored in the variable.

If you want to show or check the variable value or want to show the value inside a Message box than it can be done through Script task.

Below I will create a test table and will insert a row into the table and will display the ColB value i.e. Hello How are You in SSIS using SQL Execute Task and Script task.

Table script

create table tbl (ColA varchar(50), ColB varchar(50))

insert into tbl  values('Test Message','Hello How are You')

Now we will open SSIS and will drop a SQL Execute task and a Script task in the Package

Follow the below steps

       Double click on SQL execute task

Create a data connection to the database where above table created

In SQL Statement add -select * from tbl

 Select Resultset as Single Row as below Screenshot



  Go to Result tab in the Right side

Add a variable with message and ResultName make it as 1 (Index of your column) Since we will show ColB value in the Message box as below screenshot


Next  connect the SQL Execute task to script task

Now, double click on Script task

Select Read only variable as User Message which we created above as below screenshot


Click on edit script and add Message box (MessageBox.Show(Dts.Variables["Message"].Value.ToString());) inside Main Function as below Screenshot



  Save and Click ok

 Run your Package this will display your variable value in the Message box as below.

Please write in Comments If you'r stuck with any step or need any help.



Sunday, March 27, 2016

For Container in SSIS with example


In this post I will show you how to configure For Container in SSIS with an example. This post will gives a clear picture on For container in SSIS & How it is differ from For Each(Previous Post) 

To know about Foreach Container in SSIS, Check my previous post on For Each Container -http://www.sqlandssrssolutions.com/2016/02/foreach-container-in-ssis-with-example.html/

So below I will create a simple package to demonstrate the use for loop container, which will insert a record in ForLoop table for every minute increment which we will configure in For Container as below.

Below is the step by step guide to configure For Container in SSIS.


First we will create a table in SQL, where we will insert a record for each minute till the For Loop ends.
--ForLoop Table Script
Create Table dbo.ForLoop
(
ID int identitY(1,1) not null ,

DT_Date datetime not null
)


    Once we will create the For Loop table next we will open Integration Service Project in SSIS and will drop a For container into it.

 Next give a Name to For Loop container for eg Looping Each  Minute from StartDate to EndDate

 Now we will start configuring the For container, so for that first we will create two variable in SSIS i.e. Start Date & EndDate as in screenshot


Next double Click on For Loop, Configure the for Loop as below Screenshot.



  Once done, You will get a warning message in the for Loop container as below.

Warning               1              Validation warning. Looping Each  Minute from StartDate to EndDate : The initialization expression is not an assignment expression: "@StartDate". This error usually occurs when the expression in the iterate expressions on the ForLoop is not an assignment expression.      forloop.dtsx       0              0

You can either Ignore it or set Delay validation to True to remove the warning message.

Once done with For Loop, next Drop an Execute SQL Task inside it.

Name Execute SQL Task like this- For Every Increment of minute Inserting Record in table

Double click on execute SQL Task and configure it as below Screenshot



 First create a connection to your Data source

Next Select  SQL Source Type- Direct Input

Next write the below query in SQL Statement

INSERT INTO ForLoop
 (DT_Date)
 VALUES(?)

Next click on Parameter mapping in Right hand side and add input variable StartDate as below



 Make sure the data type should be Date otherwise it will throw the below error

[Execute SQL Task] Error: Executing the query "INSERT INTO ForLoop
                         (DT_D..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OR
Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query “insert into ForLoop
                         values (?)” failed with the following error: “Invalid time format”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

Once done click ok and Run the Package, The package will run successfully as below.



Do a select * from dbo.ForLoop to verify the data, you will notice that for every minute there is a entry in ForLoop Table.

Explanation -While configuring For Loop below are three imp things.

Initialize expression-This is the Initialization expression from where our Loop will start i.e. StartDate= “27-03-2016 01:30”

Evaluate expression-This will Evaluate the expression till the loop ends i.e @StartDate<=@EndDate, This will run until the startDate become equal to endDate

Assign Expression-Every times when the For Loop runs it will add a minute in startdate until start date will be equal to End Date

Next in Execute SQL Task Every time when StartDate will incremented by 1 minute it will insert the Record in ForLoop table until the StartDate equals to EndDate i.e. 27-03-2016 01:30<= 27-03-2016 01:40
Therefore a total of 11 entries inserted into ForLoop Table when you will do a select * from dbo.ForLoop


Please write in comment box if in case you will face any Issue while performing the above steps, I will be happy to help you.



Saturday, February 27, 2016

Foreach Container in SSIS with example

I often see  Developers having issues or confusion with foreach container, Sometimes they faced issue with looping the files or asking questions on how to process each file and load each file records in the destination table

So below I will create a simple package to demonstrate the  use for each loop container which will process 3 text files and using data flow we will process each file and will insert Record into destination table. For Reference I have added the all 3 data files in the last.

Below are the steps 

First I have created three small text files and have kept in  SSIS. folder with Name (File1, File2 and File3)

Next I am creating a destination table in SQL where the three files data will get loaded

Create table EmployeeDetail
(ID varchar(100),
 Name varchar(100),
 Salary varchar(100)
)

Now Open BIDS

 –Add a SSIS Project

-Add a for each container in you Package

Add a variable SourcePath which will have the folder location where all files has been placed.

Add a variable FileName that will be a file Name as in screenshot.

Next double Click on For Each Loop

Click on collection

Go to expression

Select Directory and add variable source path as below screenshot


Next click on variable mapping

Map the FileName  variable as below Screenshot.


Once done click ok

Next Drop a Data flow task Inside Foreach container.

Double click on Data flow task

Add a Flat file Source and create a flat file connection 

Next select flat file connection properties by pressing F4 

Now Go to expression as highlighted in yellow and Select connection string and add file Name variable as in Screenshot

Click OK

Add an ole-db destination

Connect the Flat file source to SQL  destination and click on mapping in SQL Destination

Run the Package and It will run successfully without any Issue.

,
Once completed you will see all 3 files data will get loaded into your table.Do a check by doing 

Select  * from EmployeeDetail

Below I am sharing the three file test data so that you can use these file to test for each container in SSIS.


File1.txt

ID,Name,Salary
6,Vikas,1000
7,Amit,1100

File2.txt

ID,Name,Salary
6,Vikas,1000
7,Amit,1100

File3.Txt

ID,Name,Salary
4,A,4000
5,B,7000

Please write in comment box if in case you will face any Issue while performing the above steps, I will be happy to help you.