Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS OleDB command parameter problem Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:40 AM
Points: 6, 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?

Post #1597293
Posted Tuesday, August 5, 2014 2:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:40 AM
Points: 6, 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 ...
Post #1599514
Posted Tuesday, August 5, 2014 3:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1599523
Posted Tuesday, August 5, 2014 3:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:40 AM
Points: 6, 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)
Post #1599535
Posted Tuesday, August 5, 2014 4:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1599571
Posted Tuesday, August 5, 2014 5:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:40 AM
Points: 6, 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.
Post #1599600
Posted Tuesday, August 5, 2014 8:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:33 AM
Points: 856, Visits: 1,506
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

Post #1599769
Posted Tuesday, August 5, 2014 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:40 AM
Points: 6, 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.
Post #1599792
Posted Thursday, August 7, 2014 1:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:33 AM
Points: 856, Visits: 1,506
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?
Post #1600486
Posted Thursday, August 7, 2014 1:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1600487
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse