I am using a SSIS
Data Flow Task
to transfer data from one table to another. Column A in Table A contains a number, the last 3 digits of which I want to store in Column B of Table B.
First I'm trying to grab all of the data in Column A and store in a variable via a simple SELECT
statement SELECT COLUMN_A FROM TABLE_A
. However, the variable stores the statement as a string when I want the result set of the query. I have set the EvaluateAsExpression
property to False
but to no avail.
Secondly I want to be able to use the result of this query in the Derived Column
of my Data Flow
to extract the last 3 digits and store the values in Column_B in the destination. The expression I have is:
(DT_STR,3,1252)RIGHT(@User::[VariableName],3)
I want to store this as a string hence the (DT_STR,3,1252)
data type.
All I'm getting so far in Column_B of Table_B is is the last 3 characters of the SELECT
statement "E_A". There is a lot of useful information on the web including YouTube videos for things like setting file paths and server names as parameters or variables but I can't see many relevant to the specifics of my query.
I have used an Execute SQL Task
to insert row counts from flat files but, in this example, I want to use the Derived Column
tool instead.
What am i doing wrong? Any help is gratefully appreciated.
Thanks KeithL thats one solution I will use in future but I found another.
I dropped the variable and in the Expression
box of the Transformation Editor
did:
(DT_STR,3,1252)RIGHT((DT_STR,3,1252)Column_A,3)
.
In my question, I failed to cast Column_A from Table_A as a string. The first use of (DT_STR,3,1252)
simply sets the destination column as a string
so as not to use the same data type as the source which in my case was int
.
Its the 2nd use of (DT_STR,3,1252)
that actually casts Column_A from int
to a string
.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments