Solved

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

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Availablity Groups Shared Path 2 16
job schedule 8 21
DataType for bit Comparison 11 21
Need to find substring in SQL 5 19
In this article I will describe the Detach & Attach 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

821 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