SSIS various tasks description

A task can best be described as an individual unit of work. We can also develop our  own tasks, but here are the current ETL Tasks available us to out-of-the-box:

ActiveX Script Task: Executes an ActiveX script in SSIS package. This task is only to facilitate conversion of legacy DTS packages that use this deprecated scripting method.

Analysis Services Execute DDL Task: Executes a DDL Task in Analysis Services. For example, this can create, drop, or alter a cube (Enterprise and Developer Editions only).

Analysis Services Processing Task: This task processes a SQL Server Analysis Services cube, dimension, or mining model.

Bulk Insert Task: Loads data into a table by using the BULK INSERT SQL command.

Data Flow Task: This very specialized task loads and transforms data into an OLE DB, and now, optionally, an ADO.NET Destination.

Data Mining Query Task: Allows us to run predictive queries against Analysis Services data - mining models.

Data Profiling Task: This exciting new task allows for the examination of data to replace our ad - hoc data profiling techniques.

Execute DTS 2000 Package Task: Exposes legacy SQL Server 2000 DTS packages to our SSIS package.

Execute Package Task: Allows us to execute a package from within a package, making our SSIS packages modular.

Execute Process Task: Executes a program external to our package, such as one to split our extract file into many files before processing the individual files.

Execute SQL Task: Executes a SQL statement or stored procedure.

File System Task: This task can handle directory operations such as creating, renaming, or deleting a directory. It can also manage file operations such as moving, copying, or deleting files.

FTP Task: Sends or receives files from an FTP site.

Message Queue Task: Sends or receives messages from a Microsoft Message Queue (MSMQ).

Script Task: This task allows us to perform more .NET - based scripting in the Visual Studio Tools for Applications programming environment.

Send Mail Task: Sends a mail message through SMTP.

Web Service Task: Executes a method on a Web service.

WMI Data Reader Task: This task can run WQL queries against the Windows Management Instrumentation. This allows us to read the event log, get a list of applications that are installed, or determine hardware that is installed, to name a few examples.

WMI Event Watcher Task: This task empowers SSIS to wait for and respond to certain WMI events that occur in the operating system.

XML Task: Parses or processes an XML file. It can merge, split, or reformat an XML file.

If your database user login is assigned to the db_ssisoperator role, which has only read access and does not have write access to a package stored in msdb, what are you able to do with the package?

Ans.  Because you are assigned the db_ssisoperator role, you can execute the package inside msdb, but you cannot delete the package from the msdb store or import packages into the store

What information stored in the package definition would cause a security risk if it were found?

Ans.  Although a package fi le does not contain data, it does contain the schema details about input sources and destinations. Even if these sources and destinations cannot be accessed, this information can be a security risk because it exposes table  and column names.

Can you configure the security of a package so that you can store a password in clear text in the underlying .dtsx fi le in the file system?

Ans. no, sensitive information such as connection passwords cannot be stored as clear text in the package fi le in the fi le system. The only way to store the connection password in the fi le is to encrypt the sensitive information by setting the ProtectionLevel to EncryptSensitiveWithPassword or EncryptSensitiveWithuser- Key. A better choice is to set the ProtectionLevel to DontSaveSensitive and use an SSIS confi guration to store the connection password.

To run a stored procedure within a SQL Server database, what task would you choose?

The Execute SQL Task can run a stored procedure within SQL Server or any relational database for which you have an installed data provider. The syntax of the statement entered in the Execute SQL   ask will be in the native language of the underlying database.

What is the difference between a control flow task and a control flow container?

Control flow tasks perform operations, whereas containers coordinate and group tasks.

Example: a foreach Loop Container can look through the fi les in a system folder, and a file System Task embedded within the container can then move the files to a new folder location.