Can we access user variable in query of Source in DFT?

Question

I am working on optimizing a Data Flow Task. I Ado.Net source firing a query like below.

Select Emp_id, EmpName, Salary from Employee.

After source I hace a derived column transform whcih adds a derived column with user variable value @[User::TestVariable].

Now I guess this derived column transform would be taking some time atleast so I was wondering if I can save that time by doing something like below at source.

Select Emp_id, EmpName, Salary, DerivColumn as @[User::TestVariable]
 from Employee

Is it possible to do something of this kind? if yes how?

Above is DFT I am working on how can i find out which component took how much time, so i can look to optimize that.

Answer 1

so you wish to add a new column to your dataset with a fixed value (contained on @[User::TestVariable]) to be inserted later on a destination, right? NO, you cant do what you are thinking because the scope is the database (where you execurte the query) and the variable is on the package.

Are you sure this derived column operation is take that long? It shouldnt. If it is, you could use a execute SQL task to insert this value on the DB into a temp table and the use it on your query

declare @aux int
select @aux = your_config_column from your_temp_table

Select Emp_id, EmpName, Salary, @aux as DerivColumn 
from Employee

it is kind of a messy solution, but it is worth it if the derived column is really taking that long

Answer 2

You can use the variable in ADO.NET Source .

1.In the property window of DFT task click the expression property and select the ADO.NET Source SQL Command

2. In the expression write your SQL Query

   Select LoginId,JobTitle," + (DT_WSTR,10) @[User::TestVariable] + " as DerivedColumn
   from HumanResources.Employee"
   

I don't think that your Derived Column is adding any overhead as it is a Non Blocking component (bt there are some exceptions to it )

In order to find the speed of individual components ,

1.Calculate the overall execution time for the package ,which you can find it in the execution result tab

  Overall Execution Speed = Source Speed + Transformation Speed

2.Remove the derived component and connect the source to the row transaformation.Now again see the execution time .This will give you the source speed .

3. Overall Execution Speed - Source Speed = Transformation Speed
   

SSIS is an in-memory pipeline, so all its transformations occur in memory.It replies heavily on buffer .In your case ,SSIS buffer caries 196,602 rows .This value is controlled by 2 properties DefaultMaxBufferRows and DefaultMaxBufferSize.MaximumBufferSize is 100MB.Now you need to calculate the estimated row size by calculating the column size in your table.Suppose adding your datatype length comes around 40 bytes then amount in bytes for 196,602 rows is

  196,602*40=7864080 ~ 7MB

which is less than DefaultMaxBufferSize 10MB.You can try increasing the DefaultMaxBufferRows to increase the speed .But then again you need to do all your performance testing before comping to a conclusion . I suggest you read this article to get a complete picture about SSIS performance