harveystephenb
asked on
SSIS Data Transform Design
I am trying to design a part of an SSIS package (Data Flow Task) that uses an excel spreadsheet for its input and output. I am good on the source/destination setups.
I want to take one column from the input spreadsheet (userid) and use that in a query that returns the corresponding employee id from a database, replacing the userid with employee id in the output spreadsheet.
Can you advise me on the appropriate data transformations to use for this?
I am using the BI studio for SQL Server 2005.
I want to take one column from the input spreadsheet (userid) and use that in a query that returns the corresponding employee id from a database, replacing the userid with employee id in the output spreadsheet.
Can you advise me on the appropriate data transformations to use for this?
I am using the BI studio for SQL Server 2005.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Safest way is to import the Excel sheet in to a table (temp table in sorts) and do queries from it. Reasoning is you can clean the data during import and dictate the data type of the data you're importing (can be some ambiguity when dealing with Excel columns).
please let us know if you have problem in implementing vdr1620 method on Lookup Transformation ,or if you want to use carsRST method about temp table.
ASKER
I haven't been able to get the Lookup transformation to join with the User_Name input. I get "Error at Data Flow Task [Lookup[1225] input column "User_Name" has data_type which cannot be joined."
I tried inserting a data conversion transformation in between the Excel source and the Lookup data transformation changed the output column type from DT_WSTR to DT_STR, DT_Text, and DT_NText.
Still same error message.
Was I destined to fail by following this advice?
I tried inserting a data conversion transformation in between the Excel source and the Lookup data transformation changed the output column type from DT_WSTR to DT_STR, DT_Text, and DT_NText.
Still same error message.
Was I destined to fail by following this advice?
problem is: DT_NTEXT can not be used for lookup,
ASKER
Should not DT_STR work?
the Lookup approach was an appropriate solution to your problem..and it would solve the issue that you have posted..
DT_STR should work.. In order to convert DT_NText to DT_Str..you should convert it to text and then str as below
(DT_STR,Length,1252)(DT_Te xt,1252)(C olumnName)
Make sure that both the Source and Lookup column that you are trying to join have same data type
DT_STR should work.. In order to convert DT_NText to DT_Str..you should convert it to text and then str as below
(DT_STR,Length,1252)(DT_Te
Make sure that both the Source and Lookup column that you are trying to join have same data type
DT_STR should work correctly.
do you receive any error? paste exact error message here
do you receive any error? paste exact error message here
ASKER
The column data type coming out of the excel file shows at DT_WSTR with length 255. I tried using a data conversion transformation to change it to DT_STR with length 50.
The join column from the table I am querying from in my Lookup transformation is nvarchar(50).
I tried casting it in my query to varchar(50) hoping for a match.
I am not sure what you mean by
In order to convert DT_NText to DT_Str..you should convert it to text and then str as below
"(DT_STR,Length,1252)(DT_T ext,1252)( ColumnName )".
Where do I plug in such syntax?
The join column from the table I am querying from in my Lookup transformation is nvarchar(50).
I tried casting it in my query to varchar(50) hoping for a match.
I am not sure what you mean by
In order to convert DT_NText to DT_Str..you should convert it to text and then str as below
"(DT_STR,Length,1252)(DT_T
Where do I plug in such syntax?
ASKER
Exact error message is:
I get "Error at Data Flow Task [Lookup[1225] input column "User_Name" has data_type which cannot be joined."
I get "Error at Data Flow Task [Lookup[1225] input column "User_Name" has data_type which cannot be joined."
Ok..I thought that you were trying to map a Ntext column to Str ..so i suggested to use the exp in derived column..never mind
Since,its a WSTR to nvarchar..you don't need change the column datatypes ...If you did that's fine too.. Some times it just happens that though you change the datatype the metadata of the column does not change ..so try to remove the column and add it again ..That might solve (if you are mapping right data types)
Also, use advanced editor and check the datatypes of th input and output columns..see the output columns have the data type that you have converted them to be... ( Rt click on transformation and Select Advanced editor)
Since,its a WSTR to nvarchar..you don't need change the column datatypes ...If you did that's fine too.. Some times it just happens that though you change the datatype the metadata of the column does not change ..so try to remove the column and add it again ..That might solve (if you are mapping right data types)
Also, use advanced editor and check the datatypes of th input and output columns..see the output columns have the data type that you have converted them to be... ( Rt click on transformation and Select Advanced editor)
also you can change datatype of input columns, in sources, right click , advanced editor, output column, change data type.