ssis questions

1	What is the MaxConcurrentExecutables property on a Package level?	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
2	What is the Engine Thread property of Data Flow Task?	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
3	What are the Precedence Constraints in SSIS, and where and why have you used them?	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”
4	What is the difference between the Success and the Completion value of Precedence 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.
5	What is the DelayValidation property of Data Flow Task? Why does one use this property?	indicate wheather the validation of the exectuable delayed until runtime.
6	What is RetainSameConnection Property on Connection? Why is it used?	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
7	If we create a temp table in SSIS Package and want to use it in other tasks, which properties do we need to use?	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.
8	What is data Viewer in SSIS? Is data viewer available in Control Flow or Data Flow?	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
9	I am running my package for debugging and I do not want to load data into any destination. Which transformation can I use to ensure that the data goes nowhere else?
10	What is the difference between Checkpoint and Breakpoint in SSIS?	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.
11	Will my package run successfully by using SQL Server Agent if I have data viewers and Breakpoint enabled?
12	What are different ways to execute your SSIS Package? Can I run a SSIS Package by using a Stored Procedure?	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
	What is the variable value at runtime?	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
	w many ways can you do INSERT-UPDATE?	I’ve always wanted to do a benchmark comparison so I can proudly say that my “normal” way is the best in terms of run time.  1.STAGE-TSQL: Use the data flow to bring the raw data into staging, and use do the INSERT-UPDATE in TSQL. This is my “normal” way. And the best way according to my testing. The name STAGE-TSQL implies: 1) two steps are involved, 2) raw data is staged first, 3) INSERT-UPDATE are done in TSQL only.  2.UPDATE-ALL: Only one step is involved. INSERT-UPDATE is done in one data flow step. 1) Transformation OLE DB Command is used for UPDATE, 2) Destination OLE DB Destination is used for INSERT.  3.UPDATE-STAGE: Two steps are involved. 1) INSERT is done in the data flow step, 2) but the matching rows are saved to a staging table, and UPDATE is done in TSQL using the matching rows.  4.UPDATE-DELTA: similar to number 2 UPDATE-ALL. In stead of directly sending all matching rows to transformation OLE DB Command for UPDATE, the Script Component transformation is used to determine if there are actually changed rows. Send data to Transformation OLE DB Command only if there are changes in the matching rows. ex1:trun_stg_TSQL,Stag_TSQL(DFT),Trg_TSQL ex2:trg_mychangingtable_upd_all(DFT)-(1oledb_Src,2lookup,3nomatch o/p oledb_Dest,4.match o/p oledbcmd) ex3:trun_upd_stg,trg_mychangingtable_upd_stg(DFT)(1oledb_Src,2lookup,3nomatch o/p oledb_Dest,4.match o/p oledbdest_Stg),trg_mychangingtable_upd ex4:(1oledb_Src,2lookup,3nomatch o/p oledb_Dest,4.match o/p>sc(script component transf(check any changed data)-> oledbcmd,else moved to rowsampling(ignore))
	Lookup	lookup is case senstive I’ve always chosen to use the Upper function for comparison and preserve the case for data for two good obvious reasons. 1.I like Full cache and  2.As a developer, how the servers are configured is totally out of my control Note (provided the SQL Server uses case-insensitive collation, which is the default setting of SQL Server).