SQL Server Integration Services Pacakge level property that defines that how many tasks can run simultaneously(parallel).

here -1 ,that means that It will be able to run Total Tasks=Number of processors+2.MaxConcurrentExecutables set to default value (-1)

Setting MaxConcurrentExecutables property value to low number to share Resources

Setting MaxConcurrentExecutables to higher value to run more Tasks in Parallel

The EngineThreads property is a property of each Data Flow task. ,The default is 5 in SQL Server 2005 and 10 in SQL Server 2008, with a minimum value of 2.

One thing we want to be clear about EngineThreads is that it governs both source threads (for source components) and work threads (for transformation and destination components). Source threads and work threads are both engine threads created by the Data Flow’s scheduler

Precedence constraint is used to direct the package execution flow. there are two options available to control the flow of package execution. They are Constraint and Expression.

Constraint are predefined conditional check in the package that we can use to link between control flow tasks.

Expressions are used to evaluate the user expressions to control flow of package execution.

we can also use these two options with different combinations of logical condition such as AND and OR as given in the section “Multiple constraint”

A success constraint will only allow the process to continue to the following task if the preceding task completed successfully, whereas a completion constraint will allow the process to continue as soon as the preceding task has  completed, regardless of the outcome.

indicate wheather the validation of the exectuable delayed until runtime.

Once the RetainSameConnection is set to true then the connection will remain and will be used by the entires tasks group without having the necessity to recreate new connection for each task

on the tasks and connection manager. The first property is a connection manager property. If you right click the OLEDB Connection Manager and choose properties you will see a property called RetainSameConnection. This must be set to "True". RetainSameConnection means that the temp table will not be deleted when the task is completed

The second property we must change is the Execute SQL Task property. If you right click on each task (Create Temp Table and Query) and choose properties you will see a property called DelayValidation. This must be set to "True". DelayValidation means that the task will not check if the table exists upon creation.

Data viewers can be used any time we want to see the data that is actually in the pipeline;  they stop the package temporarily until we shut them.  Also remember that the Row Count shape can be used as a “Dead End”.  It is useful during development when we want to see the output from a dataflow, but don’t want to update a table or file with the data.  available in DATA flow

We will be using Break Point in SSIS Package to view variable values at run time.Here are the variable I have used in package. I have changed the values for VarArchiveFullPath and VarSourceFullPath variables.  --Expressions are written on these variable those should evaluate at run time and change the values of these variable.

checkpoints, which lets you restart the package if it fails for any reason. During package execution, the last successfully completed task or container is noted in a checkpoint file, and the checkpoint file is removed if the package completes successfully. But if the package fails before completing, the checkpoint file remains available as a reference to the location from which to restart the package.

1. Run the package programmatically.Scripts are written using vb or C#.The main drawback is,the SSIS package should be installed in the same machine where the application runs.

2.Execute SSIS Package using DTEXEC.EXE Command Line Utility.

3.Execute SSIS Package using DTEXECUI.EXE Utility

4. Executing SSIS Package using SQL Server Agent Job

5 different ways to set values for variables

To summarize, here are the values I set for the variable varCubeName using 5 different ways: 1.Cube Name in Value: in the Variable Editor in the Value field  2.Cube Name in Expression: in the Variable Editor in the Expression field  3.Cube Name in Package Configuration: in the package configuration file  4.Cube Name from Execute SQL Task: in the Execute SQL Task 5.Cube Name in Script Task: in a scrip task

Conclusion on the precedence order  1.Cube Name in Expression: the value is set in the Variable Editor in the Expression field. The value set in this way has the highest precedence order and overwrites the values in all other ways. 2.Cube Name from Execute SQL Task: the value is set in the Execute SQL Task. It has the second highest precedence order. Cube Name in Script Task has the same precedence order. 3.Cube Name in Package Configuration: the value is set in the package configuration file. It can only overwrites the variable’s default value.  4.Cube Name in Value: the value is set in the Variable Editor in the Value field. This value is usually called the default value, because it can be overwritten by all the proceeding ways during runtime.

•If variable expression is sufficient, use variable expression only. •If you need to combine different ways to set variable values at runtime, only use these two combinations. Default value +  Execute SQL Task, or Default value +  Script Task

,4.match o/p>sc(script component transf(check any changed data)-> oledbcmd,else moved to rowsampling(ignore))

Errors typically fall into one the following categories: Data conversion errors, which occur if a conversion results in loss of significant digits, the loss of insignificant digits, and the truncation of strings. Data conversion errors also occur if the requested conversion is not supported. Expression evaluation errors, which occur if expressions that are evaluated at run time perform invalid operations or become syntactically incorrect because of missing or incorrect data values. Lookup errors, which occur if a lookup operation fails to locate a match in the lookup table. Error and Truncation Options Errors fall into one of two categories: errors or truncations. An error indicates an unequivocal failure, and generates a NULL result. Such errors can include data conversion errors or expression evaluation errors.   Parsing Data a) Fast Parse  1.Numeric Data Formats 2. Date and Time Formats b)Standard Parse Fast parse is available only when you use the Flat File source or the Data Conversion transformation. The increase in performance can be significant, and you should consider using fast parse in these data flow components if you can.