moosetracker
asked on
SSiS SQL 2005: Passing a derived column into a variable
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.)
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.)
ASKER
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.
set result to this variable
and then you can loop through this variable or doing anything you want