11/22/2024 8:51:28 PM
|
| |
| 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!
Forum to discuss using the SalesLogix OLE DB Provider from external applications (including usage of built-in provider procedures). View the code of conduct for posting guidelines.
|
|
|
|
Cursors in SSIS using SLX OLEDB Provider
Posted: 15 Jul 08 8:50 AM
|
Good day,
I need to run an update on my products table where I list in one field all the colours for a particular product style. So I have a product extension table that holds all the fields I need, and I need to update the "ALL_COLOURS" field using a cursor that fetches each style, and then for each style updates the "ALL_COLOURS" field by adding each colour for that style.
I created a nested cursor that runs in SQL Server Management Studio and works like a dream. I then created an SSIS package to do this, as I will need to update these on a regular basis. Using the SQL Server Native OLEDB provider, the procedure parses and executes with no problems. However, once I change the connection to the SLX Provider, the parse and execution fails. I need to run this update using the SLX Provider, or else this column will not be synced out to remotes.
Does anyone know what to do to get this working?
Thanks very much
Ina |
|
|
|
Re: Cursors in SSIS using SLX OLEDB Provider
Posted: 15 Jul 08 6:29 PM
|
It would require a rewrite, but why not use a DataAdaptor in a SSIS script task to do this (also using a connection via the SLX OLEDB Provider). That should work.
I suspect that cursors are not supported in the provider, so you need to script it, or somehow rewrite it.
Are you sure that you cannot write the queries without using cursors?
Phil |
|
|
|
Re: Cursors in SSIS using SLX OLEDB Provider
Posted: 15 Jul 08 7:14 PM
|
Thanks, Phil
Unfortunately I know very little about SSIS, as yesterday was the first day I used it, so when you talk about DataAdaptor I am a bit lost. If you can tell me more about it, I would really appreciate it.
I don't know of a way to do a normal update without using some sort of loop, so I guess what I will do is to create a button from which to call the report that requires this. Then I will update the fields using SLX scripting before I display the report.
Regards
Ina
|
|
|
|
Re: Cursors in SSIS using SLX OLEDB Provider
Posted: 15 Jul 08 7:30 PM
|
That's probably a good solution, because SSIS is a big topic to start from scratch!
I use it whenever I need to have some sort of scheduled process running against a SLX database - either internally (eg just running some SQL) or externally (data coming from another source).
In simple terms, you create 'Connections' to data sources (eg to a SLX database (using the SLX OLEDB Provider, of course), to an Excel spreadsheet, to a CSV file).
Then you create a task or several tasks which use the data referenced by the connections. Exactly what 'use' means is limited only by your imagination. You have the full power of VB.NET available to you.
A DataAdaptor is part of the .NET object model and is a construct used for processing and updating data.
Phil
|
|
|
| |
|
Re: Cursors in SSIS using SLX OLEDB Provider
Posted: 15 Jul 08 7:43 PM
|
No problem - it's a fantastic tool. Your initial learning curve will be a bit steep though. Let me know if you have teething troubles and I'll try to help you out.
Phil |
|
|
|
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!
|
|
|
|
|
|
|
|