SSiS SQL 2005: Passing a derived column into a variable

moosetracker
moosetracker used Ask the Experts™
on
Ok from what I can see you can not pass a derived column into a variable directly, but if you don't want to pass any data into a flat file or SQL Server, but just pull it from ODBC and alter it with derived column, you can move it to varibles...  HOW???...
Right now I have created an unwanted SQL table, have to clear it so I can put the Derived info into it.. So that I can pull it out to put in a variable.. There must be a better way.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
You can use variable, create a variable of Object type
set result to this variable
and then you can loop through this variable or doing anything you want

Author

Commented:
but the derived task does not seem to have a result set.. And it has create column or replace column, but not set variable.. what task will read in the derived columns that has the result set that is happy to just pull the derived coumns in and push them to a result set.
 
I do what I do so I can create the variables through the result task of the Oledb.. but I don't want to write to  a SQL table so I can read from sql just so I can put to the result task.

Once I got it into the variable I am working just fine with it.. I just don't like the way I am getting it there.
Consultant, Trainer
Commented:
just use a RecordSet Destination after Derived column transformation

Author

Commented:
It took a little research into RecordSet Destination to get this to work.. (and this is not a very well documented piece on the internet).. But the RecordSet Destination info was exactly what I needed.. And the info about the object variable, and looping was hints as to what I needed to do to make it work. So I just wanted to comment on for anyone else searching the internet.

Create a variable with the type of Object that will temporarily hold your variable(s).. (I was setting up for six).. In a data flow set the Recordset Destination as your destination. On the "Component Property" Tab make sure the variable points to your Object type variable.
Move to the "Input Columns" tab..  Check off the Input Columns you want to make a variable. Does not matter what the Alias is.. And ReadOnly for the UsageType is fine (although you would consider this a Write..) .. All the columns will go to your Object Type variable.

Now Move out of the Data Flow  and back to the Control Flow..  Add a forEach loop  (Even though the Object only holds one record with x number of columns..  Go to the "Collections" Section.  For Enumerator choose "ForEach ADO Enumerator".. In Enumeration Configuration, for the ADO object source variable choose you Object type variable.. Go to the Variable Mapping.. Choose each variable where you want each column to reside in the Variable dropdown. Then match it with the index number (starting with 0) for the column of the Object record.. (This is in the order you set them up from your Recordset Destination.)  

Author

Commented:
The answer lead me to exactly what I needed to use, but I need to do some research to figure out how to use it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial