MaxConcurrentExecutables property in SSIS Packages

Let’s learn about the MaxConcurrentExecutables property in SSIS

1. It’s a property of Control Flow. The value of the property is an Integer that contains the number of threads that a package can create.
2. Gets or sets the maximum number of threads a package can create.
3. Default value is -1 that means add 2 to the number of processor and use that value to execute the number of tasks in parallel. For example , if server has 2 processors and the default value is specified, SSIS will allow upto 4 tasks to be run parallel.
4. Valid values are 1 or higher and -1
5. It is used when parallelism exists in the workflow or package. If workflow is a series of sequential precedence constraints than this property has no effect.
6. If the number of possible parallel tasks are more than the number of allowable parallel tasks then some of the tasks will have to wait till the parallel threads are available.

Happy Learning !!!

By Harsh Shah

Difference between Control Flow and Data Flow

Let us understand the basic difference between Control Flow and Data Flow in SSIS 2005. 

Control Flow:

1. Process Oriented
2. Doesn’t manage or pass data between components.
3. It functions as a task coordinator
4. In control flow tasks requires completion (Success.,failure or completion)
5. Synchronous in nature, this means, task requires completion before moving to next task. If the tasks are not connected with each other but still they are synchronous in nature.
6. Tasks can be executed both parallel and serially
7. Three types of control flow elements in SSIS 2005
   1. Containers
      • Provides structures in the packages
   2. Tasks
      • Provides functionality in the packages
   3. Precedence Constraints
      • Connects containers, executables and and tasks into an ordered control flow.
      • We can control the sequence execution for tasks and also specify the conditions that tasks and containers run.
8. It is possible to include nested containers as SSIS Architecture supports nesting of the containers. Control flow can include multiple levels of nested containers.

Data Flow

1. Streaming in nature
2. Information oriented
3. Passes data between other components
4. Transformations work together to manage and process data. This means first set of data from the source may be in the final destination step while at the same time other set of data is still flowing. All the transformations are doing work at the same time.
5. Three types of Data Flow components
   1. Sources
      • Extracts data from the various sources (Database, Text Files etc)
   2. Transformations
      • Cleans, modify, merge and summarizes the data
   3. Destination
      • Loads data into destinations like database, files or in memory datasets.

Happy Learning…!!!

By Harsh Shah

Difference Between SET and SELECT commands in SQL Server

SET	SELECT
Can assign the value of an expression to the variable	Can assign the value of an expression to the variable
Cannot retrieve data from data source. It can only retrieve data from the expressions.	Can retrieve data from data source and also uses other SELECT clauses (WHERE, FROM etc)
Use SET only when you want to assign value of a function result or constant to a variable	Can use for both i.e. retrieve value of function result

Example

–Variable declaration
DECLARE
    @vName VARCHAR(10),
    @vobject_id VARCHAR(30),
    @vName1 VARCHAR(30);

–assigning a single value
SET @vName1=’Test Value’;

–Selecting and retrieving multiple values in a single select statement
SELECT
    @vName=name,
    @vobject_id=object_id
FROM
    sys.objects
WHERE
    name =’sysrowsetcolumns’

–printing the retrieved values
SELECT  @vName1,@vName, @vobject_id

–By Harsh Shah