Solved

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

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 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

17 Experts available now in Live!

Get 1:1 Help Now