1

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?

enter image description here

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.

Community
  • 1
  • 1
Pritesh
  • 1,938
  • 7
  • 32
  • 46

2 Answers2

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

enter image description here

  1. In the expression write your SQL Query

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

enter image description here

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 .

  1. 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

praveen
  • 12,083
  • 1
  • 41
  • 49
0

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

Diego
  • 34,802
  • 21
  • 91
  • 134
  • sorry, I dont get it, did u try to implement my solution? – Diego Aug 21 '12 at 14:03
  • You can do what the poster asked, it is just not as easy as doing it the way he tried. But that is why he asked the question. You can either do it the way that @Praveen mentioned, or you can use oledb with the query as a variable. – William Salzman Aug 22 '12 at 14:10