SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS OleDB command parameter problem


SSIS OleDB command parameter problem

Author
Message
lode.vervaeck 33792
lode.vervaeck 33792
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 28
I have a OLE DB Command Task that should update a column of a number of rows in a table.
It works fine with target connections with SQL 2012 (Development Edition and Express).
However, it does not work on a SQL 2008 S3 Edition.

The error returned : ... Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". ...

I used 'sa' for each connection. All other packages in the same project execute fine.

The problem is that the parameters cannot be parsed correctly. Even these simple lines in the 'SQLCommand' parameter cannot be validated:
declare @PrefixComment nvarchar(1024)
set @PrefixComment=?

Setting the property 'ValidateExternalMetadata' to False does not help. The same error is shown at runtime.

Any suggestion?
lode.vervaeck 33792
lode.vervaeck 33792
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 28
Some additional info on this post:

This works ( a single SQL statement )
Update DeliveryConditions
set isdefault=1
where id=?

This does not work ( a multi-line statement)

declare @id int ;
set @id=? ;
 
if @id != -1
Update DeliveryConditions
set isdefault=1
where id=@id

In an SQL task, I could use a variable as the source type but I need the same type of updates in a OLE DB command ...
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62374 Visits: 13298
What do you mean with the S3 edition of SQL Server?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
lode.vervaeck 33792
lode.vervaeck 33792
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 28
I meant a SQL Server 2008 Service Pack 3. I read about service pack issues on the web, so I installed the service pack 3 (on a developer edition) to make sure I had the latest version.
(I have to migrate customer data (SQL 2000 and higher) to at least a SQL 2008 server)
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62374 Visits: 13298
Not sure why it doesn't work. What if you put the code in a stored procedure and you call that sp with the OLE DB command?

On a sidenote: the OLE DB command is really slow for larger data sets. Consider writing your updates to a staging table and then do a regulare UPDATE statement.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
lode.vervaeck 33792
lode.vervaeck 33792
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 28
Stored procedures are no problem (on the target database in this case).
I wanted to avoid creating objects in the source and target databases (or use linked servers on the staging database (source, target and staging db's are on separate machines)), as source and target db's are from customers...

Thanks anyway for you help.
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5326 Visits: 2692
lode.vervaeck 33792 (8/5/2014)
Some additional info on this post:

This works ( a single SQL statement )
Update DeliveryConditions
set isdefault=1
where id=?

This does not work ( a multi-line statement)

declare @id int ;
set @id=? ;
 
if @id != -1
Update DeliveryConditions
set isdefault=1
where id=@id

In an SQL task, I could use a variable as the source type but I need the same type of updates in a OLE DB command ...


Just to get the understanding, you are using OLEDB Target Destination, and you want to update a column value with a variable value?

If so you can do the following:

1) Drop a Derived Column Data flow item
2) Create a new column and Place the variable in expression with appropriate data type.
3) use that column in the target.

hope it helps
lode.vervaeck 33792
lode.vervaeck 33792
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 28
Briefly explained:
I a have source table and a target table that are already filled with the same data, except for a comment field.
In the source table, the comment field is a plain text. In the target table the comment field is a RTF text.

Upon migration, I use a data flow task that
1. reads the source table (id and comment)
2. Add a Derived Column Transformation in which I add fields that contain the prefix and suffix texts that will be used to transform a plain text into rtf comment.
3. Finally I use a OLE DB command that updates the target table rows. In this transform I use the following SQL command:

declare @PrefixComment nvarchar(1024),@PrefixLine nvarchar(100)
declare @SuffixComment nvarchar(10),@SuffixLine nvarchar(100)
set @PrefixComment= ?
set @PrefixLine = ?
set @SuffixLine = ?
set @SuffixComment=?


Update ManufacturingBoms
set Comment=@PrefixComment+@PrefixLine+replace(replace(replace(replace(cast(? as nvarchar(max)),'\','\\'),'{','\{'),'}','\}'),char(13)+char(10),@SuffixLine+@PrefixLine)+@SuffixLine+@SuffixComment
where id= ?

This works fine for SQL 2012 target databases (Development Edition and Express Edition) but fails when targetting a SQL 2008 S3 database.

I chose this solution as several tables contain comment fields that have to be converted. All prefix and suffiex text are defined in variables and are reused through out the packages.
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5326 Visits: 2692
Honestly, if any data transformation is required, it should be in Derived Columns, trust me on this, people have spends a lot of hours to diagnose the issue, if these kind of logic is written inside hidden in the flows, just like you are doing right now. as per your logic, all the relative data is coming in the data flow.

so i will go to my previous comments i-e

Create a Derive Column data flow, place all the logic in the expression by doing so you will get the updated column you wanted, just use that column in the oledb command.

Simple, readable and scalable.

Make sense?
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62374 Visits: 13298
twin.devil (8/7/2014)
...

Create a Derive Column data flow, place all the logic in the expression by doing so you will get the updated column you wanted, just use that column in the oledb command.

Simple, readable and scalable.



Did I just read scalable and OLE DB command in the same paragraph? ;-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search