What Is A Task?
A task is very much like a method of any programming language which represents or carries out an individual unit of work. There are broadly two categories of tasks in SSIS, Control Flow tasks and Database Maintenance tasks. All Control Flow tasks are operational in nature except Data Flow tasks. Although there are around 30 control flow tasks which you can use in your package you can also develop your own custom tasks with your choice of .NET programming language.
A task is very much like a method of any programming language which represents or carries out an individual unit of work. There are broadly two categories of tasks in SSIS, Control Flow tasks and Database Maintenance tasks. All Control Flow tasks are operational in nature except Data Flow tasks. Although there are around 30 control flow tasks which you can use in your package you can also develop your own custom tasks with your choice of .NET programming language.
What Is A Precedence Constraint And What Types Of Precedence Constraint Are There?
SSIS allows you to place as many as tasks you want to be placed in control flow. You can connect all these tasks using connectors called Precedence Constraints. Precedence Constraints allow you to define the logical sequence of tasks in the order they should be executed. You can also specify a condition to be evaluated before the next task in the flow is executed.
These are the types of precedence constraints and the condition could be either a constraint, an expression or both Success (next task will be executed only when the last task completed successfully) or Failure (next task will be executed only when the last task failed) or Complete (next task will be executed no matter the last task was completed or failed).
What Is A Container And How Many Types Of Containers Are There?
A container is a logical grouping of tasks which allows you to manage the scope of the tasks together.
These are the types of containers in SSIS
Sequence Container - Used for grouping logically related tasks together
For Loop Container - Used when you want to have repeating flow in package
For Each Loop Container - Used for enumerating each object in a collection; for example a record set or a list of files.
Apart from the above mentioned containers, there is one more container called the Task Host Container which is not visible from the IDE, but every task is contained in it (the default container for all the tasks).
 |
| Daum Frequently Asked SSIS Interview Questions Answers |
What Are Variables And What Is Variable Scope?
A variable is used to store values. There are basically two types of variables, System Variable (like ErrorCode, ErrorDescription, PackageName etc) whose values you can use but cannot change and User Variable which you create, assign values and read as needed. A variable can hold a value of the data type you have chosen when you defined the variable.
Variables can have a different scope depending on where it was defined. For example you can have package level variables which are accessible to all the tasks in the package and there could also be container level variables which are accessible only to those tasks that are within the container.
What Is An Ssis Proxy Account And Why Would You Create It?
When we try to execute an SSIS package from a SQL Server Agent Job it fails with the message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account". This error message is generated if the account under which SQL Server Agent Service is running and the job owner is not a sysadmin on the instance or the job step is not set to run under a proxy account associated with the SSIS subsystem. Refer to this tip to learn more about it.
What is data Profiling task?
Data profiling is the process of analyzing the source data to better understand what condition the data is in, in terms of cleanliness, patterns, numbers or nulls, and so on. data profiling task usually be used at the beginning of the development cycle to support the design of the destination database schema. Note that this task is not used when you develop the normal recurring ETL packages.
What is the multicast Transformation in SSIS?
The Multicast transform, as the name implies, can send single data input to multiple output paths easily. You may want to use this transformation to send a path to multiple destinations sliced in different ways. The multicast transformation is similar to the Split Transformation because both send data to multiple outputs. However, you can not specify the conditions for which part of the data will be in which output in the Multicast transformation.
Move and rename the file at the same time
Tasks Required:
File System Task
Set the File System task to rename the file and point to the directory you’d like to move the file to. This enables you to rename and move the file in the same step.
Loop over an array of data in a table & perform a set of tasks for each row
Tasks Required: Execute SQL Task, Foreach Loop
Use an Execute SQL Task to load the array and send the data into an object variable. Loop over the variable in a Foreach Loop by use an ADO Enumerator.
Difference between Merge and Union All?
The Merge transformation can merge data from two paths into a single output. The Transform is usefull when you wish to break out your Data Flow into a path that handles certain errors and then merge it back into the main Data Flow downstream after the errors have been handled. it’s also useful if you wish to merge data from two Data Sources.
Note that the data must be sorted before using the Merge Transformation. you can do this by using the sort transformation prior to the merge or by specifying an ORDER By clause in the source connection. Also, the metadata must be the same for both paths. For example, CustomerID column cannot be a numeric column in one path and a character column in the other path.
The Union All Transformation works much the same way as the Merge Transformation, but it does not require the data to be sorted. It takes the outputs from multiple sources or transforms and combines them into a single result set.
OLE DB Command Transform?
The OLE DB Command Transform is a component designed to execute a SQL Statement for each row in an input stream. This task is analogous to an ADO Command Object being created, prepared, and executed for each row of a result set. The input stream provides that data for parameters that can be set into the SQL Statement that is either an In – line statement or a stored procedure call.
Execute package task?
The Execute Package Task enables you to build SSIS solutions called parent packages that execute other packages called “Child Packages”. You’ll find this capability an indispensable part of your SSIS development as your packages begin to grow.
Separating packages into discrete functional workflows makes for shorter development and testing cycles and facilitates best development practices. in SSIS, the child package is aware of the parent package parameters and can reach out and access those parameters – level configuration values. The majority of the configurable properties are in the Package tab of the Executable package Task Editor.
The first option is to provide the location of the child package. The options here are either File system and SQL Server. You can deploy SSIS package in the FIle system task as an .dtsx file, or within the msdb database of a SQL Server instance. if you select file system, you must first create a new connection manager connection to the child package.
If the child package is located in a SQL Server, you’ll need to provide the OLE DB Connection Manager for the SQL Server that holds your packages. In either case, browse to and then select the child package within the connection to set the package to execute in the task.
What is Manifest file in SSIS?
Manifiest file is the utility which can be used to deploy the package using wizard on file system and SQL Server database.
What is File system deployment?
File system deployment means to save package file on local or network drive. Then you can use SQL Agent job to schedule when the packages will run.
How to back up or retrieve the SSIS packages?
If your package is deployed on SQL Server then you can back up the MSDB database as all the package on SQL server deploys at MSDB.
What is a transaction in SSIS package and how to implement it?
Packages use transactions to bind the database actions that tasks perform into atomic units. and by doing this maintain data integrity. Al MS IS container types – packages the For loop, For each loop, and Sequence containers, and the task hosts that encapsulate each task can be configured to use transactions. IS provides three options for configuring transactions: Not supported, Supported, and Required.
Require indicates that the container start a transaction, unless one is already started by its parent container. if a transaction already exists, the containter joins the transaction For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence Container would start its own transaction. If the package were configured to use the Required option, the Sequence containter would join the package transaction.
Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Executable SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any taks fails. if the package does not start a transaction, the four execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.
Not Supported indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to Not Support transactions. For instance, if a package is configured to start a transaction and a For Loop Container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.
Checkpoint?
A checkpoint is the property in SSIS which enables the project to restart from the point of failure. When we set the property to true package create the checkpoint file which stores the information about package execution and use to restart package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time the package runs.
Can I run SSIS packages with SQL Server Express or Web or Workgroup editions?
I have looked at the SQL Server 2008 feature comparison matrix and it lists the express/web and workgroup editions as having the SSIS runtime. Does this mean it is possible to develop SSIS packages using the developer edition, and then deploy and run them on a server running one of the lowly SQL Server editions such as SQL Server 2008 Express edition?
You need dtexec to run SSIS packages from command line.
How can I manually fail a package in Integration Services?
I am running an Execute SQL Task statement in my SSIS package. The Execute SQL Task is running sql and checking that the tables have more than 1000 rows. If they have less than 1000 rows, I want to fail the package.
How do I force a fail inside of a SQL statement?
AFAIK, tasks in SSIS fail on error. So if your Execute SQL Task has a statment like so in it:
declare @count int
select @count = select count(*) from my_table
if @count < 1000
begin
raiserror(‘Too few rows in my_table’,16,1)
end
else
begin
— Process your table here
end
You should get the results you want.
No Process Is on the Other End of the Pipe
I receive this error when I try to connect to SQL Server 2005. I have enabled TCP/IP, Named Pipes, and restarted the server but that is not working.
FYI, I’ve just had the same error.
I switched to Windows authentication, disconnected, then tried to login again with SQL authentication. This time I was told my password had expired. I changed the password and it all worked again.
Data profiling is the process of analyzing the source data to better understand what condition the data is in, in terms of cleanliness, patterns, numbers or nulls, and so on. data profiling task usually be used at the beginning of the development cycle to support the design of the destination database schema. Note that this task is not used when you develop the normal recurring ETL packages.
What is the multicast Transformation in SSIS?
The Multicast transform, as the name implies, can send single data input to multiple output paths easily. You may want to use this transformation to send a path to multiple destinations sliced in different ways. The multicast transformation is similar to the Split Transformation because both send data to multiple outputs. However, you can not specify the conditions for which part of the data will be in which output in the Multicast transformation.
Move and rename the file at the same time
Tasks Required:
File System Task
Set the File System task to rename the file and point to the directory you’d like to move the file to. This enables you to rename and move the file in the same step.
Loop over an array of data in a table & perform a set of tasks for each row
Tasks Required: Execute SQL Task, Foreach Loop
Use an Execute SQL Task to load the array and send the data into an object variable. Loop over the variable in a Foreach Loop by use an ADO Enumerator.
Difference between Merge and Union All?
The Merge transformation can merge data from two paths into a single output. The Transform is usefull when you wish to break out your Data Flow into a path that handles certain errors and then merge it back into the main Data Flow downstream after the errors have been handled. it’s also useful if you wish to merge data from two Data Sources.
Note that the data must be sorted before using the Merge Transformation. you can do this by using the sort transformation prior to the merge or by specifying an ORDER By clause in the source connection. Also, the metadata must be the same for both paths. For example, CustomerID column cannot be a numeric column in one path and a character column in the other path.
The Union All Transformation works much the same way as the Merge Transformation, but it does not require the data to be sorted. It takes the outputs from multiple sources or transforms and combines them into a single result set.
OLE DB Command Transform?
The OLE DB Command Transform is a component designed to execute a SQL Statement for each row in an input stream. This task is analogous to an ADO Command Object being created, prepared, and executed for each row of a result set. The input stream provides that data for parameters that can be set into the SQL Statement that is either an In – line statement or a stored procedure call.
Execute package task?
The Execute Package Task enables you to build SSIS solutions called parent packages that execute other packages called “Child Packages”. You’ll find this capability an indispensable part of your SSIS development as your packages begin to grow.
Separating packages into discrete functional workflows makes for shorter development and testing cycles and facilitates best development practices. in SSIS, the child package is aware of the parent package parameters and can reach out and access those parameters – level configuration values. The majority of the configurable properties are in the Package tab of the Executable package Task Editor.
The first option is to provide the location of the child package. The options here are either File system and SQL Server. You can deploy SSIS package in the FIle system task as an .dtsx file, or within the msdb database of a SQL Server instance. if you select file system, you must first create a new connection manager connection to the child package.
If the child package is located in a SQL Server, you’ll need to provide the OLE DB Connection Manager for the SQL Server that holds your packages. In either case, browse to and then select the child package within the connection to set the package to execute in the task.
What is Manifest file in SSIS?
Manifiest file is the utility which can be used to deploy the package using wizard on file system and SQL Server database.
What is File system deployment?
File system deployment means to save package file on local or network drive. Then you can use SQL Agent job to schedule when the packages will run.
How to back up or retrieve the SSIS packages?
If your package is deployed on SQL Server then you can back up the MSDB database as all the package on SQL server deploys at MSDB.
What is a transaction in SSIS package and how to implement it?
Packages use transactions to bind the database actions that tasks perform into atomic units. and by doing this maintain data integrity. Al MS IS container types – packages the For loop, For each loop, and Sequence containers, and the task hosts that encapsulate each task can be configured to use transactions. IS provides three options for configuring transactions: Not supported, Supported, and Required.
Require indicates that the container start a transaction, unless one is already started by its parent container. if a transaction already exists, the containter joins the transaction For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence Container would start its own transaction. If the package were configured to use the Required option, the Sequence containter would join the package transaction.
Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Executable SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any taks fails. if the package does not start a transaction, the four execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.
Not Supported indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to Not Support transactions. For instance, if a package is configured to start a transaction and a For Loop Container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.
Checkpoint?
A checkpoint is the property in SSIS which enables the project to restart from the point of failure. When we set the property to true package create the checkpoint file which stores the information about package execution and use to restart package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time the package runs.
Can I run SSIS packages with SQL Server Express or Web or Workgroup editions?
I have looked at the SQL Server 2008 feature comparison matrix and it lists the express/web and workgroup editions as having the SSIS runtime. Does this mean it is possible to develop SSIS packages using the developer edition, and then deploy and run them on a server running one of the lowly SQL Server editions such as SQL Server 2008 Express edition?
You need dtexec to run SSIS packages from command line.
How can I manually fail a package in Integration Services?
I am running an Execute SQL Task statement in my SSIS package. The Execute SQL Task is running sql and checking that the tables have more than 1000 rows. If they have less than 1000 rows, I want to fail the package.
How do I force a fail inside of a SQL statement?
AFAIK, tasks in SSIS fail on error. So if your Execute SQL Task has a statment like so in it:
declare @count int
select @count = select count(*) from my_table
if @count < 1000
begin
raiserror(‘Too few rows in my_table’,16,1)
end
else
begin
— Process your table here
end
You should get the results you want.
No Process Is on the Other End of the Pipe
I receive this error when I try to connect to SQL Server 2005. I have enabled TCP/IP, Named Pipes, and restarted the server but that is not working.
FYI, I’ve just had the same error.
I switched to Windows authentication, disconnected, then tried to login again with SQL authentication. This time I was told my password had expired. I changed the password and it all worked again.