Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Saturday, April 27, 2024 
 slxdeveloper.com
 Community
 Members
 General
 Architect
 External
 Web
 Downloads
 Resources
4/27/2024 5:36:41 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!
 External Development Forums - General External Development
Forum to discuss general external development topic (related or not to SalesLogix development). View the code of conduct for posting guidelines.
Forums RSS Feed


 Back to Forum List | Back to General External Development | New ThreadView:  Search:  
 Author  Thread: SQL DTS Development
Corey Harbaugh
Posts: 31
 
SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 12:11 PM
fiogf49gjkf0d
I'm wanting to get some thoughts from other developers on how to generate table id's and logging when building a SQL Server DTS package. The curveball is that our SQL Servers are in a Common Environment which means that no application components can be installed on these servers.

Any ideas?

Thanks,

Corey
[Reply][Quote]
Ryan Farley
Posts: 2265
slxdeveloper.com Site Administrator
Top 10 forum poster: 2265 posts
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 12:55 PM
fiogf49gjkf0d
Quote:
Originally posted by Corey Harbaugh

The curveball is that our SQL Servers are in a Common Environment which means that no application components can be installed on these servers.


There are some good approaches you can use, however, there are some things to be aware of before you even get started.

If you cannot install the SLX provider on the SQL Server, this will limit some of what you can do. When you run a DTS package manually, it runs in the context of of the currently logged in user on the local machine you're running it on. That means, you can open Ent Mgr on your workstation, open a DTS package and run it, and it is relying 100% on resources on your machine, using your locally installed SLX provider etc, but making connections back to the SQL server. However, when you schedule the package to run automated, it is running in a service context on the SQL server itself. This means that you must have the SLX provider installed on the SQL Server for this scenario to work.

Make sense?

One thing you could do if you really are not able to install the SLX provider on the SQL server and need the package to run as an automated SQL job, if you had a workstation that had DTS installed on it as well as the SLX provider, you could kick off the package on the command line using the DTSRUN utility. Then it would run in the context of the machine you kick it off on, not the server.

As far as generating ID values, your easiest approach is to first, make sure you're on 6.2.3 or higher, then use the autoincrement key feature. Then you don't need to create the ID values, just omit them and let the provider create them for you (if you need to use the ID values you can use SQL views to join the newly imported data back to your source - this way you can get to the ID value that was created to import child data under that level if needed). See this article here for more info on table IDs: http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=87

-Ryan
[Reply][Quote]
Corey Harbaugh
Posts: 31
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 12:59 PM
fiogf49gjkf0d
Thanks
[Reply][Quote]
Jeremy Brayton
Posts: 491
Top 10 forum poster: 491 posts
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 3:19 PM
fiogf49gjkf0d
I found a technique that disregards the use of the autoincrement key feature. I don't remember where I found this technique and I've made very little modifications to the original source.

I like to keep DTS stuff in order, so when setting up a package I'll delete all the auto-generated transformations and start from scratch. I create an new transformation of the ActiveX Script type. I select no source columns and map the createdate, createuser, modifydate, modifyuser, and primary key columns. I paste this snippet as the VBScript code:

Function Main()
	Dim stringVersion
	Dim stringServerName
	Dim stringServerAlias
	Dim stringPassword
	Dim stringTableName
	Dim stringTableID
	Dim stringUserID
	Dim stringConnection
	Dim objectConnection
	Dim objectCommand
	Dim objectRecordSet
	
	stringVersion = "6.2"
	stringServerName = "SERVERNAME"
	stringServerAlias = "SALESLOGIX"
	stringPassword = ""
	stringTableName = "C_CUSTOMTABLE"
	stringTableID = stringTableName & "ID"
	stringUserID = "ADMIN"
	if (stringVersion = "6.2") then
		stringConnection = "Provider=SLXOLEDB.1;Data Source=" & stringServerName & ";Initial Catalog=" & stringServerAlias & ";User Id=Adminassword=""" & stringPassword & """ersist Security Info=True;Extended Properties=""PORT=1706;LOG=ON"""
	elseif (stringVersion = "6.1") then
		stringConnection = "Provider=SLXNetwork.1;Data Source=" & stringServerName & ";Initial Catalog=" & stringServerAlias & ";User Id=Adminassword=""" & stringPassword & """ersist Security Info=True;Extended Properties=""PORT=1706;LOG=ON"""
	end if
	Set objectConnection = CreateObject("ADODB.Connection")
	objectConnection.Open stringConnection
	Set objectCommand = CreateObject("ADODB.Command")
	objectCommand.ActiveConnection = objectConnection
	objectCommand.CommandText = "slx_dbids('" & stringTableName & "', 1)"
	Set objectRecordSet = objectCommand.Execute()
	DTSDestination(stringTableID) = objectRecordSet.Fields(0).value
	DTSDestination("CREATEUSER") = stringUserID
	DTSDestination("CREATEDATE") = Now
	DTSDestination("MODIFYUSER") = stringUserID
	DTSDestination("MODIFYDATE") = Now
	Main = DTSTransformStat_OK
End Function

You'll want to tweak stringVersion through stringUserID to adhere to your environment. It does not take into account the table conversion done for such tables as attachment (slx_dbids('ATTACH')) but you can modify the script to include the conversions if desired.

You may also notice that I only create one key per call to the script. This is a performance hit obviously but I've yet to see any serious problem with the limited imports I've done with it.

Personally I prefer this method as it doesn't require any modification in SalesLogix. I do remember 6.1's OLE DB Provider choked horribly in DTS but I believe I did find a way to make this work in that version. I doubt I would have included a connection string for it otherwise, but I've done dumber things.
[Reply][Quote]
Jeremy Brayton
Posts: 491
Top 10 forum poster: 491 posts
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 3:25 PM
fiogf49gjkf0d
The correct stringConnection ending for both versions is this:

";User Id=Admin;Password=""" & stringPassword & """;Persist Security Info=True;Extended Properties=""PORT=1706;LOG=ON"""

Note to self: include < nospace > when I want to represent ;P instead of .
[Reply][Quote]
Ryan Farley
Posts: 2265
slxdeveloper.com Site Administrator
Top 10 forum poster: 2265 posts
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 3:44 PM
fiogf49gjkf0d
That approach will definitely work, however, there is a huge performance hit with that. Not only for creating a single ID value each time, but for also creating a new ADO connection as well. I tend to never use script transformations as well in DTS. Using a script transformation, instead of just a column copy, forces DTS into a "linear" mode, instead of it's faster and optimized "set" mode. It has to perform processing for each row so it just does each row one at a time. One of DTS's strengths is the ability to work in "sets", doing many things at once and cascading the work instead of going through things one at a time.

If the import can take the time to wait for each connection and ID creation for every row, then this approach would work (although I would most definitely change the code to explicitly close the ADO Connection and Recordset objects as well as set them to Nothing), for automated imports that run on a schedule, the extra delay could cause the iterations to overlap. I'm not trying to bash on the idea or anything (auto-increment wasn't always an option), but if there's a newer feature available that works better, I'm all for using it. KWIM?

A good approach for dealing with the table ID values in pre-autoincrement times, is to create an extra field on the source table to hold the SLX table ID value. Then you can create a script task in the DTS package to fill that new column with the SLX table IDs. This way, the script tasks makes a single connection, can create as many table ID values as needed and then fill the values onto the source table. From then on it is just all straight transformations and you just bring in the new ID values with the source data.
[Reply][Quote]
Corey Harbaugh
Posts: 31
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 4:40 PM
fiogf49gjkf0d
I guess I just dont get it....I read the Table IDs artical. I use the slx provider connection in my dts to execute the statement below and I get an error about cannot insert nulls into C_PRODUCTIONID.

INSERT INTO SYSDBA.C_PRODUCTION (C_PRODUCTIONID,POLICY,
) SELECT @@identity, POLICY FROM SYSDBA.PRODLOAD

Any help?
[Reply][Quote]
Ryan Farley
Posts: 2265
slxdeveloper.com Site Administrator
Top 10 forum poster: 2265 posts
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 4:44 PM
fiogf49gjkf0d
Quote:
Originally posted by Corey Harbaugh

I guess I just dont get it....I read the Table IDs artical. I use the slx provider connection in my dts to execute the statement below and I get an error about cannot insert nulls into C_PRODUCTIONID.

INSERT INTO SYSDBA.C_PRODUCTION (C_PRODUCTIONID,POLICY,
) SELECT @@identity, POLICY FROM SYSDBA.PRODLOAD

Any help?


Exclude the ID field from the insert statement. You are telling it to insert a NULL into the field since you haven't inclused a value. You need to omit the field from the statement altogether. Know what I mean?
[Reply][Quote]
Corey Harbaugh
Posts: 31
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 4:48 PM
fiogf49gjkf0d
No dice...same error
[Reply][Quote]
Ryan Farley
Posts: 2265
slxdeveloper.com Site Administrator
Top 10 forum poster: 2265 posts
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 4:49 PM
fiogf49gjkf0d
Did you enable autoincrement for that ID field? It is "off" by default.
[Reply][Quote]
Corey Harbaugh
Posts: 31
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 4:51 PM
fiogf49gjkf0d
it is set to 'T'
[Reply][Quote]
Jeremy Brayton
Posts: 491
Top 10 forum poster: 491 posts
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 4:52 PM
fiogf49gjkf0d
I've only used this in single imports, usually during a major version upgrade so I don't consider anything you said as bashing. I found something that works but it obviously doesn't mean it's the best method (tm). As an example, my last import using this was from an Excel spreadsheet. The primary keys were created in order but the copy columns from the spreadsheet are not in row order, most likely due to the mixing of "set" vs. "linear" modes. That's not a problem to me but it may be a problem to others.

Normally I would include the close code for the connection and recordset but either I forgot it or there was some reason to leave it absent, such as DTS functioning strangely (which doesn't make any sense, but I do weird things when they appear to work).

I'd probably stick with this approach for single manual imports. Anything automated or reliant on performance will change my preference I'm sure.
[Reply][Quote]
Corey Harbaugh
Posts: 31
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 4:56 PM
fiogf49gjkf0d
My sql expression has errors....i tried it on another custom table and it worked.

thanks everyone.
[Reply][Quote]
Corey Harbaugh
Posts: 31
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 5:02 PM
fiogf49gjkf0d
I guess the provider doesnt like INSERT...SELECT.
[Reply][Quote]
Timmus Agersea
Posts: 328
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 20 Dec 06 6:06 PM
fiogf49gjkf0d
When using the provider and the autoincrement feature, you can not do select into. You have to get your source data and push it in one row at a time. Autoincrement does not support bulk operations.

Timmus
[Reply][Quote]
Corey Harbaugh
Posts: 31
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 21 Dec 06 8:56 AM
fiogf49gjkf0d
Any suggestions for bulk inserting?
[Reply][Quote]
Ryan Farley
Posts: 2265
slxdeveloper.com Site Administrator
Top 10 forum poster: 2265 posts
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 21 Dec 06 10:27 AM
fiogf49gjkf0d
Quote:
Originally posted by Corey Harbaugh

Any suggestions for bulk inserting?


You can't. However, you can optimize many other things to make the import load records faster. Do all your prep in views, so you don't have to do any lookups in the transformations. Make the flow of the package allow for running simultaneous tasks in parallel so you are writing to several tables at once, etc.
[Reply][Quote]
Corey Harbaugh
Posts: 31
 
Re: SQL DTS DevelopmentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 21 Dec 06 11:19 AM
fiogf49gjkf0d
That will work, I always make things so hard for myself

Thanks Ryan.....
[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 © 2024 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): 4/27/2024 5:59:26 PM