Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Monday, August 3, 2020 
 
slxdeveloper.com Community Forums  
   
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
 Data & Imports Forums - SSIS/DTS
Forum to discuss using SQL SSIS or DTS to perform SalesLogix imports. View the code of conduct for posting guidelines.
Forums RSS Feed


 Back to Forum List | Back to SSIS/DTS | New ThreadView:  Search:  
 Author  Thread: SSIS OLE DB Command
Martin Wickens
Posts: 9
 
SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 29 Apr 09 5:56 AM
I am going through the process of moving all of our DTS packages to our new company format of SSIS. We have a lot of packages that are using data driven queries with the saleslogix adaptor so that the data will sync out to our remote users. I am trying to use the OLE DB Command object to replicate this process in SSIS and am getting errors returned of
"Error: 0xC0202009 at Data Flow Task, OLE DB Command [34]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF. "
for each line to be updated.

The really strange thing is that the data within the database is being updated. Is there any setting that I am missing or is there a better way of doing these updates?

Thanks

Martin
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 29 Apr 09 8:50 AM
Not sure about that error, but where is the source data coming from?

Have you tried just mapping the data you need from an OLEDB source to an (SLX) OLEDB destination, or is there a load of complex stuff happening that makes that unsuitable? If so, please describe the requirement in a bit more detail.

I have always used OleDbDataAdaptors within a Script component when it comes to ensuring that data syncs out correctly and would be interested to know if 'pure' SSIS packages - by which I mean those using the standard data flow components and no scripts - sync out OK.

Phil
[Reply][Quote]
Martin Wickens
Posts: 9
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 29 Apr 09 10:14 AM
The source is coming from another table within the same saleslogix database and I'm using the a saleslogix connection to get this data. It is a pretty straight forward dataflow object really. I am getting a list of IDs from one table and then updating another table where the IDs match. It is a pure update process and there are no inserts happening. I have played about with all the different conection combinations and it does work using native SQL connection but obviously this wouldn't sync.

There is nothing else complex going on so hopefully it isn't getting too confused with something I am doing. If using the standard flow data componenst isn't the way to go, could you give me an example or point me in the right direction of how to do this in a script.

Thanks,

Martin
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 29 Apr 09 10:30 AM
If you can write that as a single UPDATE command, I would suggest creating an Execute SQL task on the Control Flow pane and doing it all in one hit. No need for Data Flow task.

Configure the connection (use the SLX OLEDB connection manager that, presumably, you've already defined), leave resultset as None and enter the SQL Statement in the SQL Statement property (who'd have thought ...?)

As I suggested before, if this works, please check TEF generation is working OK.

Phil
[Reply][Quote]
Martin Wickens
Posts: 9
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 29 Apr 09 10:58 AM
Hi Phil, Thanks for the help so far.

I am having to use a data flow as I can't do a bulk update as I am referring to another table to give me which records to update and the SLX OLEDB doesn't seem to like multiple tables in an update statement.
When I try this it come up with an error of failed to parse SQL, although the statement is fine if I am using a normal OLEDB driver.

Life would be so much easier if we didn't have remote users!

Martin
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 29 Apr 09 11:44 AM
Yuk, I was able to reproduce that error. Seems that the OLEDB Command component is expecting the SLX OLEDB Provider to behave in a certain way and it doesn't. Did you keep getting codepage warnings too?

So back to the Script task. If you're new to this stuff, or unfamiliar with VB.NET, this may take some work.

To get you started, I've included a general Update sub below. You should be able to paste this into your Public Class Scriptmain section. It assumes that an open SLXOLEDB called objSLXConn is available for use. The arguments of the sub should speak for themselves.


Private Sub UpdateDB(ByVal Fieldname As String, ByVal TableName As String, ByVal NewVal As String, ByVal Where As String)
Dim strSQL As String, modified As Boolean
'Build the select SQL string to bring back the records to be updated
strSQL = "Select * from " & TableName
If Not (Where Is Nothing Or Where = String.Empty) Then
strSQL = strSQL & " Where " & Where
End If

Dim adpSLX As OleDbDataAdapter = New OleDbDataAdapter(strSQL, objSLXConn)
Dim ds As DataSet = New DataSet
' Dim row As DataRow

Try
modified = False
adpSLX.Fill(ds)
For Each row As DataRow In ds.Tables(0).Rows
UpdateIfDifferentString(row, Fieldname, NewVal)
If row.RowState = DataRowState.Modified Then
modified = True
row("Modifydate") = Now
row("Modifyuser") = "ADMIN"
End If
Next

' Declare a command builder to create SQL instructions
' to create and update records.
If modified Then
Dim cb As New OleDbCommandBuilder(adpSLX)
adpSLX.Update(ds.GetChanges)
End If

Catch ex As Exception
' Write out an error, with the exception generating target site
' and the exception message
Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, "", 0)

' Always execute this code and return gracefully.
' Never return with an exception from script tasks.
Dts.TaskResult = Dts.Results.Failure
Finally
adpSLX.Dispose()
adpSLX = Nothing
End Try

End Sub


Ahh, it also assumes that you have written as sub called UpdateIfDifferentString - something I wrote to ensure that fields were updated only where they had changed (hence minimising sync traffic).
[Reply][Quote]
Martin Wickens
Posts: 9
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 01 May 09 4:54 AM
Yes I did keep getting codepage warnings, hopefully Sage will release a new adaptor that handles SSIS a bit more gracefully!

Thanks for the code it has definatley pushed me the right direction

Martin.
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 01 May 09 5:57 AM
No problem - post again if you run into any coding issues & I'll try to help ...

Phil
[Reply][Quote]
Rob Seiwert
Posts: 6
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 17 Jan 11 10:02 AM
fiogf49gjkf0d

Just wondering if you ever got SSIS to work using the pure functionality of SSIS with the SLX OLE DB provider. I have managed to get several different configurations to generate several different error messages.

[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Jan 11 1:41 AM
fiogf49gjkf0d

I didn't, though I haven't tried for a while.


It would be excellent if we could find a way!

[Reply][Quote]
Martin Wickens
Posts: 9
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Jan 11 8:24 AM
fiogf49gjkf0d

I never got it to work either. I eventually gave up and used the DTS2000 control within SSIS to do a data driven query. Not the ideal way at all but it works. I am hoping that maybe when next version of Saleslogix is released they fix this issue.

[Reply][Quote]
Martin Wickens
Posts: 9
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Jan 11 8:24 AM
fiogf49gjkf0d

I never got it to work either. I eventually gave up and used the DTS2000 control within SSIS to do a data driven query. Not the ideal way at all but it works. I am hoping that maybe when next version of Saleslogix is released they fix this issue.

[Reply][Quote]
Francois LE BRAS
Posts: 3
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 25 Jan 11 8:04 AM
fiogf49gjkf0d

Quote:
Originally posted by Martin Wickens

I am going through the process of moving all of our DTS packages to our new company format of SSIS. We have a lot of packages that are using data driven queries with the saleslogix adaptor so that the data will sync out to our remote users. I am trying to use the OLE DB Command object to replicate this process in SSIS and am getting errors returned of "Error: 0xC0202009 at Data Flow Task, OLE DB Command [34]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF. " for each line to be updated. The really strange thing is that the data within the database is being updated. Is there any setting that I am missing or is there a better way of doing these updates? Thanks Martin


No solution for me, except manual scripting in VBS files...
Francois

[Reply][Quote]
Rob Seiwert
Posts: 6
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 12 Apr 11 12:33 PM
fiogf49gjkf0d

Actually did get this to work. Thought I would mention one trick with SSIS. When editting the data flow rioght click the SLXOLEDB item and choose advanced edit. Under component properties set AlwaysUseDefaultCodePage to True and it works like a charm.

[Reply][Quote]
RJ Samp
Posts: 973
Top 10 forum poster: 973 posts
 
Re: SSIS OLE DB CommandYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 23 Apr 11 9:57 AM

Awesome! Just got a bunch of our stuff to work using this trick.


 


THANKS!

[Reply][Quote]
 Page 1 of 1 
  You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

   Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
 

 
 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2020 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 8/3/2020 12:41:15 PM