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