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!
|
|
Provider and DTS
Posted: 15 Mar 06 11:41 AM
|
| Hi, newbie here. I'm trying to use DTS to import a mailing list into a v6.2 database. I'm having trouble connecting to the SalesLogix OLE DB Provider. Is there an online resource that could help me out? |
|
|
|
Re: Provider and DTS
Posted: 15 Mar 06 12:10 PM
|
Not yet 
I would like to put up more general info on using DTS for SalesLogix imports. I use DTS (and now SSIS) for all SLX imports and completely love it. I don't know why anyone would want to do things any other way!
But for now, what specifically is the problem you are having? Maybe I could help?
-Ryan |
|
|
|
Re: Provider and DTS
Posted: 15 Mar 06 1:27 PM
|
I was looking for a connection string - found it.
Great resource Ryan! |
|
|
|
Re: Provider and DTS
Posted: 15 Mar 06 1:40 PM
|
lamictal pregnancy category lamictal pregnancy cleft lip read
FYI folks, without memorizing connection string formats or Googling for connection string samples, a quick and dirty way of creating a connection string to any data source on any recent Windows operating system (no other software required except the DB provider) is to create a text file called "something.udl" (no .txt extension) and double-click it to configure it, then after clicking "OK", open it in Notepad.
For a SalesLogix OLE-DB provider connection string, double-click the file you created and select "SalesLogix OLE-DB Provider" on the left-most tab. Then on the second tab fill out the same connection information used by SalesLogix Client or Administrator. Be sure to check "Allow saving password" if you want the password as part of the connection string. Test it, click on "OK", then open the file in Notepad. |
|
|
| |
|
Re: Provider and DTS
Posted: 07 Apr 06 11:11 AM
|
fiogf49gjkf0d Ryan,
we too have strated using DTS rather than Scribe. However, whilst straight inserts work we are having problems with updates. For example the following SQL will not parse:
UPDATE FTA_ACCOUNT
SET SUN_ACCOUNTID = 'D' + sysdba.PADL(acc.INTERNALACCOUNTNO, 8, '0')
FROM FTA_ACCOUNT fac
INNER JOIN ACCOUNT acc
ON fac.ACCOUNTID = acc.ACCOUNTID
WHERE fac.SUN_ACCOUNTID IS NULL
It seems a fairly large limitation that the UPDATE...FROM statement does not parse. Am I correct in thinking that putting the update statement in a stored procedure will mean that the updates will not sync out? We are just about to embark on a large project that I had planned to use a large amount of DTS to import into SLX. I am now concerned that this is not going to be practicle if the update statement is so limited. |
|
|
|
Re: Provider and DTS
Posted: 07 Apr 06 5:31 PM
|
fiogf49gjkf0d Bear in mind that this type of update statement is not a limitation of using DTS, but of the SLX provider. You'd run into the same issue no matter which tool you used to perform the update.
There are other routes you can take to accomplish this in DTS and not run into this issue. Instead of an UPDATE FROM, try using a Data Driven Query task (DDQ). This has a very fast execution time and is easy to use. However there are some issues with using a DDQ with the SLX provider.
When using a Data Driven Query task in DTS and using a SLX Provider connection for the bindings table, DTS/Enterprise Manager will crash when attempting to parse parameters from the insert/update/delete/select queries for parameter binding.
Workaround:
Add a SQL connection to the SLX database in the DTS package, set up the Data Driven Query task normally, using the SQL connection for the bindings. Then set up the queries and allow it to parse out the paramter list as it normally would. Then change the bindings connection to the SLX Provider connection and the queries and parameters will remain and all is good.
It only crashes when it first parses parameters from the queries, after that (once they are set up) all is fine. It would be great to not have to do the workaround. I get this every time with any connection on any machine, any other DTS users see this same thing? I don't often use a DDQ in DTS since my own custom DTS toolkit provides the same functionality, but it would be great for OOTB DTS for this to work correctly. I'd have to verify again if it happens with SP2 & SP3 still (haven't tested those recently so I can't remember), although I just reverified with SLX 6.2.1 and it is reproduceable 100% of the time for me.
Good luck. |
|
|
|
Re: Provider and DTS
Posted: 10 Apr 06 11:30 AM
|
mixing viagra and weed mixing adderall and weed fiogf49gjkf0d I'm glad you said that. I always gave up on the DDQ in DTS. I didn't realise it was due to a SLX OLEDB binding table.
However, I've just tried this on a 6.2.3 DB, and could quite happily pare the params without the MMC dying on me. So maybe it's fixed now. |
|
|
|
Re: Provider and DTS
Posted: 10 Apr 06 11:38 AM
|
fiogf49gjkf0d Good to know. Sometimes you get so used to a workaround that you forget to check if you still need it or not  |
|
|
|
Re: Provider and DTS
Posted: 18 Aug 06 8:03 AM
|
fiogf49gjkf0d Just A quick place to look I found this site which lists a few different connection strings. I use this as a source for different databases..
www.connectionstrings.com
|
|
|
|
Re: Provider and DTS
Posted: 12 Mar 07 9:54 PM
|
fiogf49gjkf0d Hi Ryan,
Do you have a working example on how to do this with SSIS and SLX v7?
Thanks.
Robby |
|
|
|
Re: Provider and DTS
Posted: 13 Mar 07 1:07 PM
|
fiogf49gjkf0d Originally posted by Robby Thesman
Do you have a working example on how to do this with SSIS and SLX v7? |
|
Nothing posted yet. There's really very little differences when using SSIS/DTS for SLX than how you deal with any import using SSIS/DTS. Especially when using the AUTOINCREMENT feature for all SLX table ID values.
Do you have specific questions about using SSIS for a SLX import, or just looking at getting started? A great resource for SSIS is http://www.sqlis.com/ (it's earlier DTS couterpart site is http://www.sqldts.com/) |
|
|
|
Re: Provider and DTS
Posted: 13 Mar 07 5:15 PM
|
fiogf49gjkf0d Thanks Ryan... I'm just looking to get started with it. Had a success with DTS using some scripting but couldn't yet figure it out with SSIS. |
|
|
|
Re: Provider and DTS
Posted: 13 Mar 07 8:24 PM
|
fiogf49gjkf0d Hi Ryan,
After turning the AUTOINCREMENT on, it works OK.
Thanks again. |
|
|
|
Re: Provider and DTS
Posted: 26 Nov 07 4:25 PM
|
Ryan,
My hats off to you.... for helping us out on these DTS issues. I kept changing the connection (to the SLX OLEDB Provider) in the Data Driven Query Task as opposed to the connection (crash..crash). Once I changed it in the connection... all was fine.
I owe you one.... hopefully in May!
Cheers,
Ron |
|
|
|
Re: Provider and DTS
Posted: 28 Nov 07 11:04 PM
|
Originally posted by Ron Buchanan
Ryan,
My hats off to you.... for helping us out on these DTS issues. I kept changing the connection (to the SLX OLEDB Provider) in the Data Driven Query Task as opposed to the connection (crash..crash). Once I changed it in the connection... all was fine.
I owe you one.... hopefully in May!
Cheers,
Ron |
|
Hehe. Glad I could help you Ron! We'll have to meet up in May.
-Ryan |
|
|
|
Re: Provider and DTS
Posted: 12 Dec 07 3:21 AM
|
I've got this mostly working, but am struggling with autoincrement when the SLX OLEDB provider is not on the same server as SQL Server. If I have SSIS running on server A, SLX OLEDB on server B and SQL Server 2000 on server B, all works fine. I insert into a table (e.g. activity) specifying values for all the columns except the ID column (e.g. activityid) which has autoincrement enabled, and the ID is automatically generated, the data is inserted, and everything is good.
When I try the same thing with SSIS on server A, SLX OLEDB on server B and SQL Server 2000 on server C, I get "cannot insert null value into activity id" errors.
Is this a known issue with the OLEDB provider? We're using v6.2.6. |
|
|
|
Re: Provider and DTS
Posted: 12 Dec 07 6:31 AM
|
| OK - ignore that post. I've tried a very basic SSIS package on server A, and the insert to SQL server C via SLX OLEDB on server B works just fine. Looks like there's something amiss in SSIS at a deeper level... |
|
|
|
Re: Provider and DTS
Posted: 12 Dec 07 10:26 AM
|
Hi Mark,
SSIS/DTS will attempt to include the ID column in the update/insert statements. If they're there, SLX won't auto-create them. You have to be very explicit and make sure that SSIS/DTS is not including these.
Anyway, hope that helps getting things going in the right direction.
-Ryan |
|
|
|