Solved

SSIS Data Transform Design

Posted on 2010-08-17
12
819 Views
Last Modified: 2013-11-10
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.



0
Comment
Question by:harveystephenb
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 33455077
You will need to use a lookup transformation in between your source and destination To lookup for Employee Id that matches the User Id

http://msdn.microsoft.com/en-us/library/ms141821.aspx

In the Lookup
General Tab
Specify how to handle matching rows - Select Ignore Failure

Connection Tab
 you will need to select the Database and Then Select SQL Command from drop down and Write Down your SQL like SELECT UserId ,EmployeeID FROM TableName


ColumnsTab
Map the UserId of the Source To Destination and Check the box beside EmployeeId

And Lastly Map Matching output from Lookup to Destination
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33455281
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).

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33457170
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.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:harveystephenb
ID: 33569148
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?
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33569235
problem is: DT_NTEXT can not be used for lookup,
0
 

Author Comment

by:harveystephenb
ID: 33569463
Should not DT_STR work?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33569542
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_Text,1252)(ColumnName)

Make sure that both the Source and Lookup column that you are trying to join have same data type

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33569551
DT_STR should work correctly.
do you receive any error? paste exact error message here
0
 

Author Comment

by:harveystephenb
ID: 33569636
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_Text,1252)(ColumnName)".

Where do  I plug in such syntax?



0
 

Author Comment

by:harveystephenb
ID: 33569667
Exact error message is:

 I get "Error at Data Flow Task [Lookup[1225] input column "User_Name" has data_type which cannot be joined."
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33569713
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)  
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33569736
also you can change datatype of input columns, in sources, right click , advanced editor, output column, change data type.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now