Solved

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

Posted on 2011-03-07
5
718 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 500 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

Back Up Your Microsoft Windows Server®

Back up 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

Title # Comments Views Activity
MAC Dreamweaver connect to external MS SQL Server 2 39
SQL Server Agent "Access Denied" Error 3 35
T-SQL Query 9 35
Creating Scalar Function 3 17
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.

734 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