Difference between Merge and Union All Transformation in SSIS (SQL Server Integration Services)

Merge	Union All
Accept two datasets only for input	Accept more than two datasets for input
Requires both datasets to be sorted	Does not required sorted input
Semi blocking Transformation	Semi blocking Transformation

Derived Column Transformation in SSIS (SQL Server Integration Services)

The Derived Column transformation creates new column values by applying expressions to transformation input columns.

We are using AdventureWorks database.You can download it from here. We have First Name,Middle Name,Last Name column in Person.Person table.
We can combine those three column and make a new column Full Name with the help of derived column.

Create a new SSIS project .Add a Data Flow Task and then add an OLE DB Source.

Now Edit Ole DB source editor as below -

We have three columns available in the OLE DB Source editor -

Now add a derived column transformation after the OLE DB Source.GO to the Derived Column Trasnsformation editor.Add a new Derived Column Full Name.Add expression  FirstName + " " + REPLACENULL(MiddleName,"") + " " + LastName

Now execute the package 

You may check the result from the SSMS - 

SQL Server Integration Services (SSIS) Interview Questions and Answers

                      SQL Server Integration Services Questions and Answers

1. Discuss why you would use checkpoints and how they affect the execution of an SSIS package

Answer:-When checkpoints are enabled on a package, if the package fails it will save the point at which the package fails. This way you can correct the problem and then rerun from the point that it failed instead of rerunning the entire package. The obvious benefit to this is if you load a million record file just before the package fails you don't have to load it again.

Answer:-The account that runs SQL Agent Jobs likely doesn't have the needed permissions for one of the connections in your package. Either elevate the account permissions or create a proxy account. To create a proxy account you need to first create new credentials with the appropriate permissions. Next, assign those credentials to a proxy account. When you run the job now, you will select Run As the newly created proxy account.

2. Discuss why you would use transactions and how they affect the execution of an SSIS package.

Answer:-If transactions are enabled on your package and tasks, then when the package fails it will rollback everything that occurred during the package. First make sure MSDTC (Microsoft Distributed Transaction Coordinator) is enabled in the Control Panel  Administrative Tools Component Services. Transactions must be enabled not only on the package level but also on each task you want included as part of the transaction. To have the entire package in a transaction, set TransactionOption at the package level to Required and each task to Supported.

3. What techniques would you consider to add notification to your packages? You're required to send e-mails to essential staff members immediately after a package fails.

Answer:-This could either be set in a SQL Agent Job when the package runs or actually inside the package itself with a Send Mail Task in the Event Handlers to notify when a package fails. You could also recommend third-party tools to accomplisIf you have a package that runs fine in SQL Server Data Tools (SSDT) but fails when running from a SQL Agent Job what would be your first step in troubleshooting the problem .

h this.

4. Explain what breakpoints are and how you would use them.

Answers:-Breakpoints put pauses in your package. It's a great tool for debugging a package because you can place a breakpoint on a task and it will pause the package based on execution events. A situation where I have used breakpoints is when I have a looping container and I want to see how my variables are changed by the loop. You can place a watch window on the package and type the variable name in. Set a breakpoint on the container and then stop after each iteration of the loop.

5. Discuss what method you would use for looping over a folder of files and loading the file contents into a database.

Answer:-This would require a Foreach Loop using the Foreach File Enumerator. Inside the Foreach Loop Editor you need to set a variable to store the directory of the files that will be looped through. Next, select the connection manager used to load the files and add an expression to the connection string property that uses the variable created in the Foreach Loop.

6. What techniques would you consider when adding auditing to your packages? You're required to log when a package fails and how many rows were extracted and loaded in your sources and destinations.

Answer:-This could be done by creating a database that is designated for package auditing. Track row counts coming from a source and which actually make it to a destination. Row counts and package execution should be all in one location and then optionally report off that database. This reporting database could allow you to see package execution trends and calculate other interesting metrics about package executions. There are also third-party tools that can accomplish this for you.

7. In the SSIS Data Flow what is the difference between synchronous (non-blocking) transformations and asynchronous (blocking) transformations.

Answer:-A synchronous transformation is one in which the buffers are immediately handed off to the next downstream transformation at the completion of the transformation logic. A common example of a synchronous component is a Derived Column Transformation.

A transformation output is asynchronous if the buffers used in the input are different from the buffers used in the output. An asynchronous transform is blocking because it must first consume all the data flow rows first prior to sending any to the next task. A common example of an asynchronous component is an Aggregate Transformation.

8. How can you configure your SSIS package to run in 32-bit mode on a 64-bit machine when using some data providers which are only available on the 32-bit machines.

Answer:-In order to run an SSIS package in Visual Studio in 32-bit mode, the SSIS project property Run64BitRuntime needs to be set to False. The default configuration for this property is True. This configuration is an instruction to load the 32-bit runtime environment rather than 64-bit, and your packages will still run without any additional changes. The property can be found under SSIS Project Property Pages  Configuration Properties Debugging. When running a package from a SQL Server Agent job, you must check the property "Use 32 bit runtime" on the Execution Options tab of the job.

9. How is a Data Flow path different from a precedence constraint  

Answer:-Precedence constraints are used to control the order of operations of task execution in the Control Flow. These constraints can run a task based on the success, failure, or completion of the previous task. They can also be made dynamic with the evaluation of an SSIS expression. A Data Flow path controls the direction of data movement inside the Data Flow. These allow for the developer to send error rows down special paths, while successful rows may be loaded into a table.

10. What are annotations and why should they be used in your package development

Answer:-An annotation is a comment that you place in your package to help others and yourself understand what is happening in the package. Often annotations are used for identifying the version number of a package and a list of changes that have been made in the package. Using annotations can help clearly define what the package is intended for, who developed it, and what changes have been made.

11. What feature in SSIS can be used to make a package reconfigure itself at runtime to add the current date to the end of a file name?

Answer:-This can be done with SSIS expressions and variables. Concatenating the GETDATE() with a filename will produce this result. This also requires a casting function to bring together a datetime value with the string value of the filename.

12. Which task allows you to perform common file operations and name one limitation it has  

Answer:-The File System Task is capable of copying, deleting, moving, and renaming directories or files. One limitation that this task has is that it does not participate in transactions. So if you turn on a transaction on the entire package with a File System Task that deletes a file, you will not recover that file during a rollback.

13. Name one reason why you would have to choose a Merge Join over a Lookup Transformation

Answer:-The Lookup Transformation can only join data from OLE DB connections, so if you need to join data from a Flat File, then you must use a Merge Join Transformation to accomplish this.

14. Explain what buffers are and how the SSIS Data Flow uses them

Answer:-Consider buffers like buckets of memory resources that can be used by SSIS. Data flows out of a source in memory buffers that are 10 megabytes in size or 10,000 rows (whichever comes first) by default. As the first transformation is working on those 10,000 rows, the next buffer of 10,000 rows is being processed at the source.

15. How can Data Quality Services be used with SSIS to clean incoming address data with known issues

Answer:-Data Quality Services requires that you define a Knowledge Base that can define what makes a good address value and what makes a bad address value. Once the Knowledge Base is set up you can integrate it into SSIS using the DQS Cleansing Transformation in the Data Flow of your package. Incoming rows will be evaluated by DQS and output corrected records that will be sent to a destination.

16. What is Team Foundation Server and how can it integrate with SSIS

Answer:-Team Foundation Server (TFS) is an enterprise software development life cycle suite and project management repository consisting of collaborative services, integrated functionality, and an extensible application programming interface (API). TFS allows developers to easily manage code developed and to work together on solutions through a process of checking in and checking out files while they are under development. TFS tightly integrates with Visual Studio, making it easy for developers to manage their code in the same application they use for developing SSIS packages.

17. In what scenarios would you potentially utilize a Script Component in your SSIS Data Flow design

Answer:-The Script Component in the SSIS Data Flow can be used as a custom Source, Transformation, or Destination. You may need to use a Script Source when you are loading unusual file formats. For example, the data does not follow standard column and row formats. You may use a Script Transformation if the native transformations that are provided to you don't solve the data extraction problem you have. For example, the Script Transformation may be used to apply regular expression formatting to your data being extracted. You may need to use the Script Destination if your required output of data does not follow traditional column and row formats. For example, your business is requiring a file sent to customers that has summary rows after each category of data that would cause a break in the traditional row and column format of the source data.

18. You need to run a SQL Server stored procedure inside your SSIS package to start an update process. How can you run a stored procedure in the Control Flow and also pass values into any parameters it may have

Answer:- Using the Execute SQL Task you can run SQL Server stored procedures. If the stored procedure has parameters, those are identified in SSIS with a question mark. You can then pass values into the question mark(s) using the Parameter Mappings page of the Execute SQL Task Editor.

19. What does the FastParse do and which Data Flow components can it be enabled on ?

Answer:- The FastParse property can be turned on to skip validation steps that SSIS does when evaluated date, time or numeric data. Turning this property on can in some cases significantly help performance. FastParse can be enabled on either the Flat File Source or the Data Conversion Transformation. It is turned on at a column level, so for each column of the appropriate data types you must change the FastParse property to TRUE.

    20.What are the difference between Merge and Union All Transformation?
          Answer

        

  

     

      

Solution - Create a package to open calculator in SSIS (SQL Server Integration Services)

1. Create a new SSIS project .
2.Add a Execute Process Task on the control flow tab.
3.Go to Execute Process Task editor on the executable write calc.exe.

4.Now execute the package and you will see calculator opening.

You can download solution for this exercise.

Solutions - Using a variable to count Employees imported from Excel in SSIS (SQL Server Integration Services)

Download and Copy the Employee_Data excel file to C:\Exercise_SSIS11.

1. Create a new SSIS project named Exercise_SSIS11.
2. Add a new variable Employee_Count in the variable window.

       .

        3.Create a new excel connection

   4.Add a data flow task

  

   5.Add an excel source.

  6. Add a rowcount and configure with variable

7.Add a script task

8.Add below code in script task

9. Execute the package 

You can download the project solution.

SSIS (SQL Server Integration Service) Exercise

1. Copy data from Employee excel to sql server table.
2. Export data from person.person table to excel file.
3. Copy data from Employee flat file sql server table.
4. Export data from person.person to flat file.
5. Create a package to view free space on C drive (Hint. use WMI Data Reader Task).
6. Create a package to find any new incoming files on C:\SSIS\CheckForNewFiles. (Hint. Create the folder and use WMI event watcher task)
7. Create a package to send mail.(Hint. Use send mail task)
8. Create a package to copy files from C:\SSIS\SourceTxt to C:\MyPackage\DestinationTxt (Create those folder and place example.txt files on source folder)
9. Create a package to open calculator.(Solutions) 
10. Create a package - take person.person table as source.add one column on the fly using derived column Merge First name and last name ,create full name  as a new column.Load the data to a destination sql server table.
11. Using a variable to count Employees imported from Excel. (Solutions)
12. 
    

SSIS (SQL Service Integration Service) 2016 new features

AlwaysOn Support for SQL Server Integration Services

SQL Server Integration Services Incremental Deployment

SQL Server Integration Services Always Encrypted support