How to redirect bad data Rows from Flat File Source to avoid Package failure?

Suppose we want to load data from flat file on frequently basis. There is Emp_Id Column that should have only integer or numeric data. If somehow the wrong data is found in that column we do not want to fail the package instead of that we want to redirect those rows to error output.

We can redirect error rows from Different sources, transformations and destinations as well. In our example we are using flat file source.

First Create connection to text files by using Flat File Connection Manager. I have used below data for initial testing

Emp_Id,Name

1,vipin

2,biru

ABC,XYZ – bad data which should not insert in to destination table

Change the data type to Numeric in Flat File Source.

Then change the Properties of Error and Truncation to Redirect row of selected column as shown below

Now run the package , As we can see that the error row is redirect to error output and rest of rows went to success output.

How can we call Variable Values from Parent Package to Child in SSIS?

First we need to create two packages, one named Master, and another named child. Then we will create a string variable named "strValue". In the parent package we will provide the value of variable is "Parent". We will pass this value to the child package. In the Child package we will provide the value of variable is "Child”, which will be replace by the parent package.

Master Package:-

the Execute Package task in the Master Package and configure it for browsing to Child Package.

Then go the Child Package, we need to use the Package Configuration by right clicking on Control flow task.  Select the Parent Package Variable as a Configuration type. In the Parent Variable field enter the “strValue", this was the name of the variable we created on the parent package, then click next.

Click on the plus next to the "strValue" variable and then the plus next to the properties folder under it. Then click on value so it is highlighted. This tells SSIS where to copy the value of the parent variable. Click next and finish.

To see the value of parent package variable in Child package.

Add the script task on Child package to show the value of master package value

This will cause a popup box to appear showing the value of the variable. The value will be parent if you run the parent package. The value will be child if you run the child package alone.

Questions Related to Control Flow Task

1.       What is Data Flow Task
Ans:-As the name suggest, Data Flow Task is nothing but flow of data from Source to the Destination.That's what exactly ETL does,Extract-Transform-Load of data.Most of the ETL is done in the data flow task.

2.       If you need to run some SQL script( stored procedure, DML,DDL ) which task you will use in Control Flow
Ans:-We can use Execute SQL Task to run SQL Script .

3.       If you have create object type variable that you want to use in script task later, how you will load data into Object Type variable in Control flow, which task will help
Ans:-

4.       If your database in Full Recovery mode cans you use Bulk Insert Task to load data? What are requirements to use Bulk Insert Task ?
Ans:-In Full Recovery mode all Bulk operations are FULLY logged.you can  minimize logging for a set of bulk operations by switching the database  to the bulk-logged recovery model temporarily for bulk operations.Large data imports can cause the transaction log to fill rapidly if the full recovery model is used.

5.       If your company is using Ftp site to receive daily data files, which task you will use to download/upload/Delete files on FTP Site ?
Ans:-FTP Task

6.       What is difference between Script task and Script component
Ans:-The basic functionality of the Script Task and Script Component is to extend the functionality of SSIS by writing your own custom scripts if no built-in transformation full fills your requirements. Though the purpose is same for both items, where these tasks are used is different. The Script Task is used in control flow to accomplish almost any general-purpose task, like connecting to web services, sending out HTML formatted mails.The Script Component is used in the data flow designer to extend the functionality of the source, transformation and destination and typically runs for each row. In SQL Server 2008 you can use either Visual Basic .NET or C# to write code for the Script Task or Script Component.

7.       What script languages are available for you to do scripting in Script task and script component
Ans:- In SQL Server 2008 you can use either Visual Basic .NET or C# to write code for the Script Task or Script Component.

8.       What is difference between ReadOnlyVariables and ReadWriteVariable in Script task ?
Ans:-
9.       Can you do debugging in Script task or Script component in SSIS 2012 ?
10.   You have create 5 packages, you want to call all of them in one package, which task you can use to call them ?

11.   You have deployed your SSIS Package to Integration Services Server, Can you still call them in Parent Package ?
Ans:-Yes we can call them in Parent package.

12.   If you load set of big files, after each load you want to zip them and put into archive folder. Which task you need to use to Zip them
13.   Which task you can use to delete, rename, move files and folders
14.   Can you rename and move file by using one task, which task is that and how you will do that
15.   If your data flow task fails in Control Flow, which task you will use to send email on Error
16.   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
17.   What is WSDL and in which task you have to use it
18.   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 you will use to loop through and send email one by one
19.   There are 10 files in folder, we want to load them one by one, which tasks are required to perform this job
20.   You have a lot of Tasks sitting in Control Flow task , you want to group them, which container can help you to achieve that
21.   You got this assignment where you want to perform the same task 10 times, which loop you will use to do that
22.   Name few of the task those are available to create Maintenance plan or those can perform DBA tasks such as Create indexes, take backup etc.
23.   In Execute SQL Task what is Result Set( Single Row, Full Result Set,XML)

Questions on Configuration

1.       What is Configuration in SSIS
2.       Why we even need configuration
3.       What configuration types are available for us to use
4.       What is difference between Direct and Indirect Configuration
5.       If we use SQL Server Configuration, what table is created by configuration Wizard
6.       How many columns configuration table do have, which one are important for us
7.       For indirect configuration, do you create user variable or system variable to hold connection string?
8.       What is parent child configuration and where we need to use that
9.       Explain best practices for Integration Services Configuration
10.   Let’s say we have SQL Server Connection Name ‘MyConnection’,  when you will enable configuration which properties of  Connection manager you will choose (Description,EvaluateAsExpression,Expression,IncludeInDebug,Name,NameSpace,RaiseChangedEvent,ReadOnly,Value)

Questions on Logging

1.       What is logging in SSIS , How many type of logging available in SSIS, Which one you have used
2.       If you need to create a text type log file with timestamp, how you will do that
3.       If you use SQL Server Logging, which table will be used to store log information
4.       What are the few column names that sysssislog have to store log information
5.       Do you log all the events or prefer to choose few of them?
6.       Name few of Events you like to use in logging
7.       What is custom Logging and how it is different from built in Logging
8.       If you use timestamp in your text file logging , it creates multiple log files with each execution, how to avoid that and why single execution create more than one file
9.       If we are using SQL Server Logging, how often the records got deleted from syssislog table or we have to create some purge process

Questions on Variable and Expressions

1.       What is variable in SSIS, What data types are available if we can declare a variable in SSIS
2.       What is Scope of Variable, How we can change Scope of variable in SSIS 2008 and SSIS 2012
3.       What are expressions in SSIS, Where and Why we need to use them
4.       Can we write expression on Variable? If yes how and where you write them
5.       Can we write expression on Connection Managers, If yes where did you use them and why
6.       What are system variable in SSIS, Name few of them you have used
7.       Can we write expression on Precedence Constraint, if yes how you do it
8.       What is the maximum length of expressions in SSIS 2008 and SSIS 2012
9.       What is new Expression Task in SSIS 2012
10.   What are Parameters in SSIS 2012, how are they different from Variable

Questions on Destination

1.       Raw File Destination , where and why we use it
2.       What is difference between OLE DB Destination and SQL Server Destination
3.       What is Recordset Destination and where we need that to use
4.       Can we redirect rows from OLE DB Destination, if yes what are the important things need to remember while we configure that
5.       What does “Keep Identity”,”Keep Nulls”,”Table lock” and “Check Constraints” Check box means on OLE DB Destination
6.       What does Rows Per batch and Maximum insert commit Size mean to you in OLE DB Destination, how we use them and why we need to change values from default
7.       If there is trigger on table and we are loading data in that table by using SSIS, Access Mode properties is set to “ Table or View-fast Load”  , Will trigger fire? If not then what Mode(
Table or View,
Table or View-Fast Load,
Table Name or view name variable,
Table name or view name variable –fast load,
SQL Command) we can choose to load data as well trigger get fired

Questions on Transformations

1.       What is a SSIS Transformation
2.       If you need to get Username, Package Name, Package Start Time which transformation you will use
3.       Which Transformation can perform operations such as Sum, count, Group by
4.       If you need to convert Data Type from String to Integer which transformation you will use
5.       If you need to Add new column which transformation you will use
6.       Let’s say you have column Name, some of the record for Name column are null, if you want to replace Null with “Unknown” which transformation you will use
7.       What is syntax for writing IF ELSE in Derived column Transformation
8.       What is lookup transformation and why we use this transformation when we load Fact Table
9.       What are three modes of Lookup transformation and what criteria should be used to choose
10.   Can we insert record in Lookup Table by using Lookup Transformation
11.   Should you use drop down for Table choose in Lookup and how good/bad it is for performance
12.   What is multicast Transformation in SSIS
13.   What is conditional Split Transformation in SSIS
14.   What is major difference between Conditional Split and Multicast
15.   What is OLE DB Command Transformation, Where you use it and what are disadvantages/Advantages of using OLE DB Command Transformation
16.   What alternative methods you would like to use for OLE DB Command Transformation
17.   If you need to get the Row Count for record those loaded from Source to Destination, which transformation you will use.
18.   If you need to create sequence number with input records which transformation you will use
19.   Which transformation can be used as Source, Destination Or transformation
20.   What is Slowly Changing Dimension and which transformation can help you to load those tables
21.   How will you load SCD1 type table by using SSIS
22.   How will you load SCD2 type Table by using SSIS
23.   What are the best practices when you load huge SCD type table
24.   Which transformation require us to use SORT Transformation with it
25.   What are the alternatives of SORT Transformation if our source is SQL Server and we need to use Merge Join for multiple source
26.   What is IsSorted Property and why we use it
27.   How will you remove duplicate records in SSIS, which transformation can help with this task
28.   Let’s say if we have some reference data in Excel and we want to use that excel Data in Lookup Transformation , how we can achieve that without loading into staging or temp table
29.   What is Cache Transformation and which transformation can use Cache Transformation loaded data
30.   Why we need to use Cache Transformation
31.   Which transformation can be used to change column data to Lower case, Upper case
32.   What is difference between Copy Column Transformation and Derived Column Transformation
33.   Let’s say my table contains images, I am reading data from table in Data Flow task, Which transformation can help me to save those images to files
34.   Which transformation I can use in SSIS to import image files to Table
35.   I have a source file that contains 1000 records, I want to insert 15% records in TableA and remaining in TableB which transformation I can use
36.   What is difference between Row Sampling and Percent Sampling transformations
37.   Which transformation can be used to extract nouns only, noun phrases only, or both nouns and noun phases from text input column
38.   How different is Term Lookup from Lookup Transformation
39.   To Pivot or Unpivot input data which transformations are available in SSIS
40.   There is no Union Transformation in SSIS , How to perform UNION operation by using built-in Transformation
41.   What is a Merge Transformation OR If we have source S1 and Source S2, we need to merge them and want to get sorted output , which transformation we can use
Ans:-Merge Transformation:-As the name suggest it Merge the two input data into single output. The only requirement is that input data need to be sorted. Merge transformation allowed only 2 input sorted data.

In Below screen shot you can see that there are 2 input sources, both are sorted and by the help of Merge Transformation I am combining 2 sorted input data in to single output.

42.   What is Union All Transformation and whats the difference between Merge and Union All Transformation
Ans:-Union All Transformation: -This transformation works similar to Merge transformation ,only difference is that input data did not to be sorted and UNION ALL transformation can  combines data from multiple inputs into one output . The metadata of the columns from multiple inputs must match. This is similar to SQL union all operations.

As you can see that in below screen shot, I have taken 3 input data source and combine in to single output

 43.   What is a Merge Join Transformation?

Ans:-Merge Join Transformation: - The Merge Join transformation gives an output that is generated by joining two input datasets using a FULL, LEFT, or INNER joins. It is more over similar to SQL joins. Like Merge Transformation, input datasets of Merge Join Transformation need to be sorted .For example, you can use a Inner join to join a table that includes Employee information with a table that lists the .

44.   What is FindString function in Derived column
45.   Do we have MonthName and DayName functions available in SSIS so we can use them in expression, in Derived Column Transformation?

Questions on Sources

1.       Name few of the Sources available in SSIS
Ans:-ADO NET Source,Excel Source,Flat File Source,OLE DB Source,Raw File Source and XML Source.
2.       If we need to read XML Source file which Source we will use in SSIS
Ans:-XML Source
3.       What problems you have faced when you used Excel Source.
Ans:-(32-bit and 64-bit) Drivers Comparability issue, Truncation issue(row size)

4.       How will you handle a column such as comments that has more than 255 characters in excel at row number 1000.
5.       What is fast parse property in Flat File Source? What are the requirements and limitation to use this property

Basic SSIS Question and Answer

1. What is ETL
2. What are important components of ETL Tool
3. Which ETL Tool you have used as ETL Developer
4. Which version of SSIS you have worked
5. What is DTS and how it is different from SSIS
6. What are few different features you have in SSIS 2008 and those were not available in SSIS 2005
7. What features are new in SSIS 2012 those were not in SSIS 2008
8. What is difference between Control  Flow and Data Flow in SSIS
9. What is parallel execution in SSIS and how many Data Flow Tasks a package can run in parallel
10. What is MaxConcurrentExecutables  property on Package level
11. What is Engine Thread property of Data Flow
12. What is Precedence Constraints in SSIS and where you have used them and why
13. What is difference between Success and Completion value of Precedence Constraint
14. What is DelayValidation properties of Data Flow Task , Why we need to use this property
15. What is RetainSameConnection Property on Connection, Why we use that
16. If we create temp table in SSIS Package and want to use further in other Tasks, which properties we need to set to be able to use temp table.
17. What is data Viewer in SSIS, Is data viewer available in Control Flow or Data Flow
18. I am running my package for debugging, I do not want to load data into any destination, which transformation I can use for test purpose as destination without writing data to anywhere
19. What is difference between checkpoint and breakpoint in SSIS
20. Will my package run successfully by using SQL Server Agent if I have data viewers enabled and breakPoint
21. What are different ways to execute your SSIS Package, Can I run SSIS Package by using a Stored Procedure
22. What type of deployment available for SSIS Package, explain all
23. What is difference between Package deployment and Project Deployment
24. Which version of SSIS track versions of SSIS Package deployed to Server
25. To run your SSIS Package, the Integration Services Server and SQL Server should be installed on same server, Is this correct statement?
26. What are different ways to run your package on schedule
27. What are Event Handlers in SSIS , On which Events you can run the event handler part, Name few of the Events available
28. Let’s say you have configured Event handler to send an email on error for data flow inside For each Loop, If error occurred in data flow you will get multiple emails, why is that? How we can handle those series or email coming for one error
29. How do you debug your package
30. Explain important steps for  performance tuning of your SSIS Package
31. If your package is scheduled to run every night at 10 PM. If package fails in production , where will you start to look for error
32. You have created a variable in SSIS, You used that variable in Data Flow task in Row Count transformation, if you want to display or see the value of variable after Data Flow execution how will you do that
33. You are looking at Control Flow Item and you cannot find Execute SQL task, how will you bring it back to Control Flow Items.
34. Your company is using some third party transformations and task, you need to add them to Control Flow Item and Data Flow Items , how will you do that
35. What are Attunity Drivers and why do we use them with SSIS
36. If you do not want to use Slowly Changing Transformation, Which T-SQL statement can help you to load SCD type table in one statement( insert, update, delete)
37. What is Annotation, Is it only available in Control Flow Pane or available in other Panes as well
38. Let’s say we have two Execute SQL Tasks in Sequence container, If any of them fail we want to roll back transaction. What setting we need to configure to achieve this.
39. What is TransactionOption property at Package level, Is this property only available on package level or It is also available on Container level,Task level
40. Can you create SSIS Package without using BIDS
41. You have excel file and you want to clear first cell of sheet1, which task you will use to do that
42. A third party software is available that you need to execute by using SSIS, which task can be used to run .EXE file
43. You need to load 500 million records in table; Table has cluster and non-cluster indexes already created. What will be your approach to load data
44. What are Asynchronous and Synchronous data flow components
45. What are Non-blocking, Semi-blocking and Fully-blocking components in SSIS?
46. What is Balanced Data Distributor( BDD)
47. What is Error Output? Can you redirect rows from Sources, Transformations and Destinations in SSIS?
48. If you need to check If File exists in Folder which Tasks you will use to find out that in SSIS
49. Let’s way you have created an Excel File by using Excel Destination. If you have to make the Header row bold, how you will do that in SSIS?
50. If you need to send HTML email, Can you use built in Send Mail Task? What other options you have? 
51. If you are using SQL Server Log Provider, Can you make the SQL Server connection dynamic?
52. If you need to watch a directory for a specific file to be added which Task you will use?
53. What is For-each Item Enumerator in For-each loop and where would you need to use this?
54. If you need to move a file and rename as the same time how will you do that?
55. Which Package property will help you to encrypt packaged to restrict access to the contents of a package?