?
Solved

DTS Transform Data Task - How do you do it in SSIS?

Posted on 2011-03-07
5
Medium Priority
?
720 Views
Last Modified: 2013-11-10
After many frustrating hours with SSIS, I have given up trying to do what is a very simple task in DTS...

I have an Excel spreadsheet with 2 columns - PartNumber, and Quantity.  I use it to update an Oracle table we'll call PartMaster.  Pseudocode for the SQL Update command is something like:
----
Update Partmaster
Set PartMaster.Quantity = Excel.Quantity
Where Partmaster.PartNumber = Excel.PartNumber
----

I have tried dozens of SSIS tests to no avail, getting many different types of errors that I never had to worry about with DTS (like CodePage errors, Unicode conflicts, etc.).

Can anyone provide me with a complete SSIS example that will accomplish this?

Thanks!
0
Comment
Question by:spangdelta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 35065715
you can use a data flow task,
inside data flow task add an excel source and connect it to your source excel file
then add an OLEDB Command and set a connection with oledb connection to oracle DB,
then write your update command in the sql statement property of oledb command like this:
Update Partmaster
Set PartMaster.Quantity = ?
Where Partmaster.PartNumber = ?

Note that in oledb settings, question mark is parameter marker,
now you can map input columns to these parameters in column mapping tab.

0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 35066558
A while ago I wrote an article that explains how to import an Excel sheet into SQL Server.  It's not 100% what you're looking for but I'm sure it would be interesting for you to have a look at.

When setting up the destination, select your Oracle DB instead of SQL Server if you want.
In any case you'll need to manually make some modifications to the generated package to get your update statement to work (replace generated destination with an OLE DB Command that performs the update).

Here's the article: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_3248-Importing-Excel-Data-Using-Integration-Services.html
0
 
LVL 4

Author Comment

by:spangdelta
ID: 35068466
Reza -

How do I associate the query's question marks with parameters?  Don't I have to go to the "Input and Output Properties" tab of the OLEDB Command Editor and Add Columns for the 2 parameters?  If I don't, my 'Available Destination Columns" on the "Column Mappings" tab is empty.

Sorry - I have never done this before, so I'm looking for specific step-by-step help.
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 1500 total points
ID: 35071187
1. Define a Connection Manager Tab (If already exists then select it from drop down)
2. In Component Properties Tab in the SQL Command -- write the SQL as below
3. In Column Mappings tab - Map the Columns correctly ..you will have to map to parameters PartMaster.Quantity (Param0) and PartNumber(Param2)

Update Partmaster
Set PartMaster.Quantity = ?
Where Partmaster.PartNumber = ?


Make sure that the column Data types are the same, use a  Data conversion Task or Derived column to change the Data types


Hope This Helps
0
 
LVL 4

Author Closing Comment

by:spangdelta
ID: 35082263
Thank you vdr1620 !
The ordering of the parameters was my main problem.
No books that I'd bought stressed that the parms as defined in the 'Available Destination Columns' list on the Column Mappings tab needed to be defined in the same order as the question marks appear in the query.

Problem solved - thanks for everyone's help.
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

764 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