Solved

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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

929 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

8 Experts available now in Live!

Get 1:1 Help Now