CODEWITHSUNDEEP
ssis
Lucky
Lucky

Reputation: 383

SSIS - Add package variable to existing collection

I have an SSIS package in which I am returning the results of a query into a collection. My query is returning 8 columns. For example:

     first name
     last name
     employee
     id
     city
     state
     zip
     hire-date

I am iterating through each item in the collection and do some manipulation in a For-each loop container.

I need to pass in a 9th element to the collection which is a package variable.

 Example: Active_Flag

How would I go about that? One approach I am thinking is to read the collection into an array in a script task and add the 9th element to it.

I tried this, but the collection couldn't read it and I keep getting a

"Error: The enumerator failed to retrieve element at index "9".

Any ideas how I could go about this?

Upvotes: 1

Views: 428

Answers (1)

Ferdipux
Ferdipux

Reputation: 5246

There are several approaches to your problem:

  • Think twice - do you really need 9th row? You are iterating with a For-Each loop over an Object variable, extracting 8 rows into package variables and doing something. Adding 9th package variable to Loop logic and existing 8 variables seems easier then repackaging Object variable with OLEDB collection.
  • If adding 9th column makes more sense to you, you can do it in SQL query. Create package variable type String with EvaluateAsExpression=true and Expression property like
    ="Select ..., '"+(DT_WSTR, 10)@[User::YourVariable]+"' AS [ninethcol] from ..."
    This sample adds string data, you can modify it to return desired data type.
    Then set SQL Task where you extract your data with SQL Source from variable. This will inject 9th column with flexible content to the query.

Upvotes: 1

Related Questions