May 29, 2009 at 7:43 am
IN DTS I have a Execute SQL task that does a Select CurrStatus from xxxx. The output parameter is mapped as row value CurrStatus to GlobalVariableCurrStatus.
In a Transformation task I have a Transformation Script that has this statement
DTSDestination("Status")=DTSGlobalVariables("GlobalVariableCurrStatus").Value
Basically, I'm trying to get the value coming from my select into a global variables so that I can use it to load it into another table.
How do you do this in SSIS. I created a Execute SQL task. I created a variable. what next ?
May 29, 2009 at 7:49 am
Okay. Continuing on. In the Execute SQL Task Result Set, I added the ResultName=CurrStatus and VariableName=GlobalVariableCurrStatus.
It passed the parameter. Know I'm trying to pass this to the transformation.
May 30, 2009 at 7:41 am
Instead of setting the ResultName=CurrStatus, set ResultName=0 if it is the first column of the resultset.
--Ramesh
June 1, 2009 at 9:22 am
Thank you. My execute sql task (general) has the select statement. I then have the value go to a global variable (result set). I'm okay till point. How do I pass this to the data flow.
In dts (see attachment), I connect one column from a source table to a column on a destination table. On the destination table I have one column from which I have a
script that assigns the global variable to the destination column. How do I do this in SSIS? Do I do this w/ a script component, with derived column.. not sure how.
**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("CurrStatus")=DTSGlobalVariables("xCurrStatus").Value
Main = DTSTransformStat_OK
End Function
June 1, 2009 at 9:35 am
You can do this by adding a derived column transformation and then setting the output column value to this variable and then use this derived column in destination component within the data flow.
--Ramesh
June 1, 2009 at 10:06 am
On the Data Flow Task, I added OLE DB Source and an OLE DB Destination. In between I have a Derived Column Transformation. When I open this, I see variables and columns. Can you please be more specific on how to assign the global variable to the destination column ?
June 1, 2009 at 11:06 am
In the derived column name I left the text "derived column 1".
In the derived column I left "add a new column".
In the expression I added "@[User::xCurrStatus]" This is my Gloval variable.
In the data type I left it as INT.
When I click on the transformation between the derived transformation and the destination source, I see in the metadata, the four columns from the source plus the global variable.
When I click on the destination, I see 5 arrows. source to destination.
When I run it, I get a package validation error.
Error at data flow task. the script includes no code, edit the component to include code.....
June 1, 2009 at 11:17 am
I'm getting an error at pass variable.
June 1, 2009 at 4:44 pm
greengiant (6/1/2009)
In the derived column name I left the text "derived column 1".In the derived column I left "add a new column".
In the expression I added "@[User::xCurrStatus]" This is my Gloval variable.
In the data type I left it as INT.
When I click on the transformation between the derived transformation and the destination source, I see in the metadata, the four columns from the source plus the global variable.
When I click on the destination, I see 5 arrows. source to destination.
When I run it, I get a package validation error.
Error at data flow task. the script includes no code, edit the component to include code.....
The error message suggests that you have an empty script component in your dataflow ... can you remove it?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 2, 2009 at 7:12 am
I have removed it. That message went away and I am known getting the message
Error at Data Flow (OLE DB Destination) Columns "Derived Column 1", and CurrStatus cannot convert between unicode and non-unicode string data types.
Derived Column - Derived Column 1 is defined as Unicode String DT_WSTR.
Global Variable - gCurrStatus is defined as string.
June 2, 2009 at 7:45 am
I figured out why the error and I'm not sure if I corrected it.
I changed the type of the column on destination table from a char(8) to an nchar(8). Resolved the issue.
Do we have to have nchars for transformation w/ strings to work?
June 2, 2009 at 11:21 am
Once again, I would suggest that the error message gives the game away. Try changing the data type of your derived column to [DT_STR].
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 2, 2009 at 11:26 am
You just need to avoid mixing and matching your datatypes if you can. Working with DT_STR is just fine. Try using the Advanced Editor on your datasource and looking at the input and output properties - that shows you the datatypes that are assigned as your data comes into the pipeline and sometimes you need to make changes there.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 2, 2009 at 11:54 am
Thank you for all your help.
I would like to add that the data from the source is all dt_str.
The issue w/ the unicode error is happening on the global variable. The global variable is a string and it is transferred into a column of type char(8). When I changed the char(8) to nchar, the transformation worked. I guess my question is ... I did not want to change the table column to nchar, I was hoping I could do something on the expression like cast maybe.
June 2, 2009 at 5:30 pm
Did you try my suggestion of changing the derived column's datatype to DT_STR?
Then we should be keeping DT_STR from one end of the pipeline to the other and your destination should no longer need to be NCHAR.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply