SSIS Interview Questions for freshers and experienced

Looking for SSIS Interview Questions for freshers and experienced then you are at right place, we (Freshers360 Team) provided all the frequently asked SSIS interview questions on this blog post, check all the interview question topic wise and do share with your friends

SSIS Interview Questions for freshers and experienced

Control Flow Interview Questions

  1. What is the control flow Task?
  2. What is the Data flow Task?
  3. Difference between Dataflow Task and Control Flow Task ?
  4. What is Execute SQL Task?
  5. Difference between Execute SQL Task and Execute TSQL-Statement Task?
    Variable
  6. What is the difference between for Loop and for each loop container?
  7. What are different iterations in For Each LOOP container?
  8. What is Sequence Container?
  9. What is Sequence Container and how it works?
  10. Difference between File System and FTP Task ?
  11. What is variable in SSIS, what are data types available for variable?
  12. What is Scope of Variable? How can we change Scope of variable in SSIS 2008 and SSIS 2012?
  13. What are expressions in SSIS? Where and Why do we need to use them?
  14. Can we write expression on Variable? If yes, how and where can you write them?
  15. Can we write expression on Connection Managers? If yes, where would you need to do that?
  16. What are System Variables in SSIS? Name few of them those you have used.
  17. Can we write expression on Precedence Constraint? if yes, how would you do it?
  18. What is the maximum length of expressions in SSIS 2008 and SSIS 2012?
  19. What is new Expression Task in SSIS 2012?
  20. What are Parameters in SSIS 2012? how are they different from Variables?
  21. Can you create two variables with same in name in SSIS Package?
  1. If you need to run some SQL script( Stored Procedure, DML and DDL ). Which task will you use in Control Flow?
  2. If you have created object type variable that you want to use in script task later, how will you load data into Object Type variable in Control flow? Which task will help?
  3. If your database in Full Recovery mode , can you use Bulk Insert Task to load data? What are the requirements to use Bulk Insert Task?
  4. If your company is using Ftp site to receive daily data files, which task will you use to download/upload/Delete files on FTP Site?
  5. What is the difference between Script task and Script component?
  6. What script languages are available for you to do scripting in Script task and script component?
  7. What is the difference between ReadOnlyVariables and ReadWriteVariable in Script task?
  8. Can you do debugging in Script task or Script component in SSIS 2012?
  9. You have create 5 packages, you want to call all of them in one package, which task can you use to call them?
  10. You have a lot of Tasks sitting in Control Flow task , you want to group them, which container can help you to achieve that?
  11. You got this assignment where you want to perform the same task 10 times, which loop will you use to do that?
  12. Name few of the tasks those are available to create Maintenance plan or those can perform DBA tasks such as Create indexes, take backup etc.
  13. In Execute SQL Task, what is Result Set ( Single Row, Full Result Set,XML) ?
  14. Which task would you use to execute dot batch files in SSIS?
  15. What type of Containers are available in SSIS Package? Explain two of them in detail.
  16. You need to create a directory with Date in SSIS Package, Which Task would you use that?
  17. You have deployed your SSIS Package to Integration Services Server, Can you still call them in Parent Package?
  18. If you load set of big files, after each load you want to zip them and put them into archive folder. Which task would you need to use to Zip them?
  19. Which task can you use to delete, rename, move files and folders?
  20. Can you rename and move file by using one task? which is that task and how will you do that?
  21. If your data flow task fails in Control Flow,which task will you use to send email on Error?
  22. Before you create your SSIS Package and load data into destination, you want to analyze your data, which task will help you to achieve that?
  23. What is WSDL and in which task you have to use it?
  24. You have loaded email addresses in Object type variable, now you want to send email to each of the address you have in Object type variable, which task will you use to loop through and send email one by one?
  25. There are 10 files in folder, we want to load them one by one, which tasks are required to perform this job?
  26. Which Task will you use to build Dynamic SQL in SSIS Package?
  27. How would you loop through queries which are stored in Excel file and run in SSIS Package?
  28. You don’t want to use Cursor in TSQL to loop through the Queries which you have saved in one of the SQL Server Table. How you can create an SSIS Package to loop through those queries and run one at at time in SSIS Package?
  29. Is it possible to call multiple stored procedures with Input Parameters in Execute SQL Task?
  30. You have set the ResultSet= Single Row in Execute SQL Task. What if no row will be returned by the query, Will you get an error or it will complete without error?
  31. How to Delete all the files from a folder expect the oldest file in SSIS Package?
  32. How to delete all files in a folder except Latest by using SSIS Package
  33. How to delete all files except current date files in SSIS Package

Data Flow Interview Questions

  1. What is data flow Task?
  2. What is the different type of data sources in Dataflow task?
  3. What is Difference between Ado.net Destination and Record set Destination?
  4. Explain XML data source and how it works?
  5. What isAnalysis Services Execute DDL task?
  6. What is Data profiling Task?
  7. What is data mining Query Task?
    What is Script Task?
  8. What is Script Component?
    What all the transformation you have used in dataflow task?
  9. Explain alternative method to use XML as a source?
  10. What is fuzzy Lookup and Fuzzy Grouping? Explain difference?
  11. Data Conversion and Derived column explain?
  12. What is Multicast?
  13. Difference between Union All and Merge Join ?
  14. Difference between OLEDB Destination, SQL Server Destination and SQL Server Compact Destination?
  15. Difference between Merge and Merge Join?
  16. What is Cache Transformation? How to use it Explain?
  17. What is Oledb Command transformation in SSIS used for?
  18. Explain the Lookup Transformation?
  19. Difference between SSIS and DTS?
  20. During an SSIS package execution, how are SQL database errors handled?  Give an example of a handled and unhandled error event in a well-designed package .
  21. What is a data viewer in SSIS and its uses?
  22. What to insert a new column in the dataset data flow?
  23. What is a multicast?
  24. Explain star schema, snowflake schema, fact and dimension table?
  25. What is lookup transformation?
  1. Difference between the data type DT_STR and DT_WSTR?
  2. What is slowly changing dimensions?
  3. How to use script component as Source and Destination?
  4. What are the languages available for coding in Script Task?
  5. How to Handle Object Variable in Script Task?
  6. What are the steps to use a package Variable in Script Task?

Also Check :- Top 100 sample resume objectives – Career objective example

Connection Manager Interview Questions

Question: What is Config File in SSIS?

How to configure package properties at runtime?
Question: What are the different types of Config options available in SSIS package?
Question: Explain Connection Manager?
Question: How to change the connection manager values at run time?
Question: How to add logging in SSIS?
Question: What are some ways in which a connection task can be set dynamically within an SSIS package?
Question: What are the ways to store logs in SSIS packages?
Question: What is annotation in SSIS Package?

Package Fundamentals

Transaction in SSIS Interview Questions

Question: How to implement Transaction in SSIS package?
Question: Can we implement transactions in SSIS package?
Question: What are different transaction Options in SSIS?
Question Which control flow tasks doesn’t support transactions?
Question: How to implement isolation level in SSIS package?
Question: What is chaos in isolation level options in SSIS?

Checkpoint Interview Questions

Question: What are the checkpoints in SQL Server?
Question: What is checkpoint file?
Question: How do checkpoints behave in case of For Loop and For Each loop?
Question: Case study how isolation works in SSIS?

Package Deployment Interview Questions

Question: How to deploy the packages SSIS?

Question: Where a package does deploys in Integration service database?

Question: What is Manifest file in SSIS?
Question: What is File System Deployment?
Question: how to back up or retrieve the SSIS package?
Question: What are the different databases in Integration services while login through SSMS?

Package Security Interview Questions

Name the two Integration Services Security Properties.

Question: How to implement the security in SSIS package?
Question: What is the protection Level in SSIS package and what are the different types of protection available?
Question: How to implement database level package security?
Question: What is event handler Tab in Design window?
Question: What is SSIS package?
Question: What are the other part tabs of package design window?

Performance of SSIS Package Interview Questions

Question: Explain the performance tuning tips for SSIS Package?

Explain parallel Execution and no of package can participate

Question: What areBlocking, Partial blocking and Row version transformation?
Question: What is parallelism in SSIS how to implement it?
Question: What are the properties which can be modified to maintain buffersize?
Question: Properties to modified packet size on network?

Question: What is Execution Tree?

Error Handling Interview Questions

Scenario Based Questions

Which task will you use in a package to find corrupt or incomplete information in data?

What is the alternative for ‘Data driven query’ task from DTS in SSIS?

How can we remove last row from a CSV file in SSIS in data flow task ?

What is a UDL and what advantages does it have?

What is ‘ragged right’ format in flat files?

Difference between Checkpoint and Breakpoint?

How to run your SSIS package on a schedule?

What is the difference between Conditional Split and Multicast Transformation?

How to use Stored Procedure in SSIS package in data flow task?
How to remove duplicate record in SSIS?

While you use sort transformation there is check box which says remove duplicates.

How can a “Data-driven query” task from DTS be implemented using the SSIS transform objects?


What are the four (4) migration tools available to Integration Services package designers that aid in migrating SQL Server2000 DTS packages?

New feature in SSIS 2008 vs SSIS 2005?

New feature in SSIS 2012 vs SSIS 2008?

Thanks for reading SSIS Interview Questions post hope it helped you to prepare ,please comment if any one of the topic or FAQ is missed in the list.

By SatishReddy Venkannagari

Satish Reddy V is a Cyber Security Engineer at a reputed MNC and blogs at Freshers360. I am passionate about Blogging, Digital Marketing, and Cyber Security.

Leave a Reply

Your email address will not be published. Required fields are marked *