Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Monday, January 20, 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: Just trying to get a simple DTS Package Setup
Julie Sheridan
Posts: 4
 
Just trying to get a simple DTS Package SetupYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 26 May 09 8:33 PM
Hi,
I am trying to setup a very simple DTS Package but not matter what I try I receive the following error message:

"Could not connect to source component"
"Error opening rowset"

I am using the SalesLogix provider as the source and destination, test connections run fine, I indicate the table to copy to and from. (I setup a simple two field table to just try to troubleshoot.) Once I execute the package this error always appears. Everything else within SalesLogix runs fine and has for quite some time.

Any suggestions are greatly appreciated.

Thank you in advance.
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: Just trying to get a simple DTS Package SetupYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 26 May 09 9:15 PM
It's been a while (I use SSIS now), but I remember that I always used ActiveX scripts in DTS to do anything which relied on the SLX provider. Integration between the standard DTS task components and the SLX provider was not, erm, completely implemented, to be euphemistic about it.

What is your actual requirement here? I presume it's not just copying records ... doing this would result in issues with IDs, for one thing.

I can help you with some code, I'm sure.

Phil
[Reply][Quote]
Julie Sheridan
Posts: 4
 
Re: Just trying to get a simple DTS Package SetupYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 26 May 09 9:37 PM
Phil,

First, thanks so much for the reply.

I am actually trying to do some pretty simple stuff.

I need to update one column in a table from another database every few hours but need to make sure this gets written to TEFs for the remote clients. I already have a relationship established between SalesLogix DB and this second database on another SQL server. (The other DB will contain the Account ID and Opportunity ID from SalesLogix as it is the sysdba.Opportunity table I need to update.)

Second task is to populate/update views I have in SalesLogix to just display data from the other financial database which I already have coded to obtain the correct Account ID - as it is a one to many type view to show financial data for each Account in the accounting system.

Let me know what you think.

Julie
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: Just trying to get a simple DTS Package SetupYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 26 May 09 10:47 PM
No problem.

Your second task: sorry if I am stating the obvious, but if these are true views which are pulling data from another system, your remotes will not be able to see them.

Task 1 sounds simpler than it is, I'm afraid.

The way that I have done this is via an ActiveX script, or scripts, as I mentioned before. Here is an overview of the method I used:

1) Use a SQL connection (not SLXOLEDB) to execute a query, returning a recordset of all the records that you need to update (for simplicity, I'll assume that this recordset contains OpportunityID and a field called F1, also on Opportunity).

2) Loop through this recordset one record at a time, updating the records as you go (this time using an SLX OLEDB connection). You perform the updates explicitly, row by row, to ensure that they are sync'd to remotes. Here is a code snippet:


'Pull the SLX connection string from a global variable - then only need to change the GV once when the server changes
strConn = DTSGlobalVariables("SLXConnectionString").Value

Set objConn=CreateObject("ADODB.Connection")
objConn.open strConn

'Pull the SQL connection string from a global variable - then only need to change the GV once when the server changes
'This is needed to execute SELECT across servers
strConn2 = DTSGlobalVariables("SQLConnectionString").Value

Set objConn2=CreateObject("ADODB.Connection")
objConn2.open strConn2

'We then loop round the resultset and execute an UPDATE command for every occurrence

'First, set the SQL that will retrieve the required resultset

strSQL2 = "cross database select query here returning OpportunityID and F1"

'Run this SQL using a SQL connection
set objRS2 = CreateObject("ADODB.Recordset")

'Open the recordset and cycle round it, updating as we go
objRS2.open strSQL2, objConn2, 3 '3 - adOpenStatic - try to avoid recordset locks
do
with objRS2
do while not (.bof or .eof)
strSQL = "update Opportunity set F1 = '" & .fields("F1").value & "' where OpportunityID = '" &_
.fields("OpportunityID").value & "'"
objConn.execute strSQL
.MoveNext
loop
.close
.open strSQL2, objConn2, 3 '3 - adOpenStatic - try to avoid recordset locks
end with
loop until (objRS2.bof or objRS2.eof)
objRS2.close


Obviously, variables are used to hold the connection strings.

Don't forget to tidy up at the end:


'Tidy up
set objRS2 = nothing
objConn.close
set objConn = nothing
objConn2.close
set objConn2 = nothing
[Reply][Quote]
Julie Sheridan
Posts: 4
 
Re: Just trying to get a simple DTS Package SetupYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 27 May 09 9:24 AM
Thanks so much.

For the second item I was going to pass data from the other SQL database into other tables in the SalesLogix DB using a scheduled stored procedure run once a day and I was hoping the SalesLogix database could contain views that look at this data that is also synced out to the remotes. Sorry for the confusion.
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: Just trying to get a simple DTS Package SetupYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 27 May 09 9:50 PM
When the stored procs run, any data which they transfer will not be sync'd to remotes. The fact that you have created a view of which the SLX provider is aware does not mean that the data referenced by that view will be sync'd out.

So the remotes would see the view, but not the data.

To get the data sync'd out, you need to do row-by-row explicit INSERTS to SLX tables using the SLX provider, as exemplified in my previous post.

By explicit, I mean of the following form:

INSERT INTO OPPORTUNITY(F1, F2, Fn)
SELECT 'str1', 'str2', 'strn'


rather than

INSERT INTO OPPORTUNITY(F1, F2, Fn)
SELECT Ex1.F1, Ex1.F2, Ex1.Fn
FROM ExternalTable Ex1
WHERE [condition]


Hope that makes sense!

Phil
[Reply][Quote]
Julie Sheridan
Posts: 4
 
Re: Just trying to get a simple DTS Package SetupYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 27 May 09 10:00 PM
Got it!
The update is running great thanks to your suggestions so I have to just get cracking on the insert tasks
Thanks
Julie
[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): 1/20/2020 6:24:12 AM