SSIS - Update a Sybase Table

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?
spen_langAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
spen_langConnect With a Mentor Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No idea.  Give us the above T-SQL but with the variable values populated.
0
 
spen_langAuthor Commented:
Hi Jim,

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

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
spen_langAuthor Commented:
UPDATE Table
SET Col1 = 'greg'
WHERE Col2 = 1
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have an UPDATE statement while is expected a SELECT. You need to return some data otherwise you'll get this error.
0
 
spen_langAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
spen_langAuthor Commented:
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
 
spen_langAuthor Commented:
Here is the OLE DB Command
OLE DB Command
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Column Mappings are ok? I mean, Name and Unit_ID are mapped to the result of the previous task, right?
0
 
spen_langAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
spen_langAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
spen_langAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
spen_langAuthor Commented:
I have tried this but still i get the warning message
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You could map the columns to Param_0 and Param_1?
0
 
spen_langAuthor Commented:
Yes I could map the columns but still it gave me that warning...
0
 
spen_langAuthor Commented:
SSIS-Package.JPG
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have 2 parameters, right?
You did it only for the Name but you need to do the same for the Unit_ID.
0
 
spen_langAuthor Commented:
Sorry i now only have one parameter to simplify the query.

UPDATE BSLNG_UnitTracking SET Name = ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then double-check if you didn't keep a '?' somewhere because it still complaining about it.
0
 
spen_langAuthor Commented:
I only have the one ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just for testing, can you set the command to:
 SELECT * FROM BSLNG_UnitTracking WHERE Name = ?
0
 
spen_langAuthor Commented:
This has the same error...

SELECT * FROM BSLNG_UnitTracking WHERE Name = '123'  works OK. So definitely the ? causing the isssue
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
spen_langAuthor Commented:
OK, thanks for all your help
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
spen_langAuthor Commented:
I am using the OLE DB Command
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
spen_langAuthor Commented:
Same problem if I use a stored procedure, it really doen't like the ?
0
 
spen_langAuthor Commented:
Answers did not get result required
0
All Courses

From novice to tech pro — start learning today.