Solved

SSIS - Update a Sybase Table

Posted on 2015-02-17
33
141 Views
Last Modified: 2016-02-15
Hi,

I am unable to update a Sybase table using SSIS 2008. I have added an "OLE DB Command" to the data flow and entered the SQLCOmmand of:

UPDATE Table
SET Col1 = ?
WHERE Col2 = ?

But I get the warning
SSIS-Warning.jpg
Please can someone advise how I can update a Sybase table using SSIS?
0
Comment
Question by:spen_lang
  • 19
  • 13
33 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40614165
No idea.  Give us the above T-SQL but with the variable values populated.
0
 

Author Comment

by:spen_lang
ID: 40614177
Hi Jim,

This is how the T-SQL looks in the SQLCommand field... The parameters would be Name(Greg) and ID (1)
0
 

Author Comment

by:spen_lang
ID: 40614236
UPDATE Table
SET Col1 = 'greg'
WHERE Col2 = 1
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614305
You have an UPDATE statement while is expected a SELECT. You need to return some data otherwise you'll get this error.
0
 

Author Comment

by:spen_lang
ID: 40614315
So how do I update the table? Should I do:

UPDATE Table
SET Col1 = 'greg'
WHERE Col2 = 1

SELECT * FROM Table

Basically I am trying to import data from a csv file into a Sybase 11 table. The csv data has been validated and split to point where i need to import the data.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614321
I don't have a full view of your package but I think you are using the wrong component to perform what you want.
Or you are using the correct component in a wrong way. Can't tell without knowing more.
0
 

Author Comment

by:spen_lang
ID: 40614332
My package looks like this:
SSIS Package
Basically I am importing a CSV file into a Sybase table.

If the record does not exist in the destination table then insert it else update the record in the destination table. It is the update that I cannot get to work...

Let me know if you require any more info...
0
 

Author Comment

by:spen_lang
ID: 40614340
Here is the OLE DB Command
OLE DB Command
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614387
Column Mappings are ok? I mean, Name and Unit_ID are mapped to the result of the previous task, right?
0
 

Author Comment

by:spen_lang
ID: 40614399
Name and Unit_ID are columns that I am attempting to set in the destination table. THe question marks are referring to input columns in the previous task. It is the ? that seem to be the issue...
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614402
Check in the Column Mappings tab if they are really mapped. If not then it's a very good chance that is the issue.
0
 

Author Comment

by:spen_lang
ID: 40614415
I have checked the Column Mappings tab in the OLE DB Command and it has the "Available Input Columns" listed but no "Available Destination Columns".

SSIS-Package.JPG
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614428
Ok, we have something here.
So, you should have in the destination something like Param0 and Param1 (one for each variable). I don't know why aren't available but I suggest you to recreate the OLEDB command.
0
 

Author Comment

by:spen_lang
ID: 40614458
I have tried this, I have even tried creating the External columns manually and then mapping them to the input columns, but still I get the same warning/error. Very frustrating as this is the simplest part of the package.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614472
I don't have SSIS installed so I went to this MSDN article. Check the steps 11, 12 and 13:

11.Expand OLE DB Command Input, and then expand External Columns.


12.Verify that External Columns lists a column for each parameter in the SQL statement. The column names are Param_0, Param_1, and so on.

You should not change the column names. If you change the column names, Integration Services generates a validation error for the OLE DB Command transformation.

Also, you should not change the data type. The DataType property of each column is set to the correct data type.


13.If External Columns lists no columns you must add them manually.

◦Click Add Column one time for each parameter in the SQL statement.

◦Update the column names to Param_0, Param_1, and so on.

◦Specify a value in the DBParamInfoFlags property. The value must match a value in the OLE DB DBPARAMFLAGSENUM enumeration. For more information, see the OLE DB reference documentation.

◦Specify the data type of the column and, depending on the data type, specify the code page, length, precision, and scale of the column.

◦To delete an unused parameter, select the parameter in External Columns, and then click Remove Column.

◦Click Column Mappings and map columns in the Available Input Columns list to parameters in the Available Destination Columns list
0
 

Author Comment

by:spen_lang
ID: 40614493
I have tried this but still i get the warning message
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614519
You could map the columns to Param_0 and Param_1?
0
 

Author Comment

by:spen_lang
ID: 40614525
Yes I could map the columns but still it gave me that warning...
0
 

Author Comment

by:spen_lang
ID: 40614526
SSIS-Package.JPG
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614529
You have 2 parameters, right?
You did it only for the Name but you need to do the same for the Unit_ID.
0
 

Author Comment

by:spen_lang
ID: 40614538
Sorry i now only have one parameter to simplify the query.

UPDATE BSLNG_UnitTracking SET Name = ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614551
Then double-check if you didn't keep a '?' somewhere because it still complaining about it.
0
 

Author Comment

by:spen_lang
ID: 40614557
I only have the one ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614570
Just for testing, can you set the command to:
 SELECT * FROM BSLNG_UnitTracking WHERE Name = ?
0
 

Author Comment

by:spen_lang
ID: 40614600
This has the same error...

SELECT * FROM BSLNG_UnitTracking WHERE Name = '123'  works OK. So definitely the ? causing the isssue
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614642
I don't have good news for you, specially because I've found an old question with the same issue and no solution was provided: http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_27762830.html

This can also be a bug of the OLEDB object. Can you try to find which version are you running and if there's an hotfix available or a new version released?
0
 

Author Comment

by:spen_lang
ID: 40614653
OK, thanks for all your help
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40614659
Also check which OLEDB object are you using. By this MSDN article "The OLE DB destination does not support parameters. If you need to execute a parameterized INSERT statement, consider the OLE DB Command transformation."
0
 

Author Comment

by:spen_lang
ID: 40616007
I am using the OLE DB Command
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40616035
Try to create a Stored Procedure that does the Update and then in the OLE DB Command you call the SP with '?' in the parameters and the you'll map the values with the parameters name (@Name and @Unit_ID )
0
 

Author Comment

by:spen_lang
ID: 40616040
Same problem if I use a stored procedure, it really doen't like the ?
0
 

Accepted Solution

by:
spen_lang earned 0 total points
ID: 40668063
In the end as a "work around" I had to insert the data into a staging table in Sybase and then run an insert/update script from the staging table into production table.
0
 

Author Closing Comment

by:spen_lang
ID: 40679739
Answers did not get result required
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

747 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

10 Experts available now in Live!

Get 1:1 Help Now