Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Monday, June 24, 2024 
 
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: Generating SLX DBIDs in SSIS
Patrick Sullivan
Posts: 13
 
Generating SLX DBIDs in SSISYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 29 Jun 07 4:14 PM
Ok I've been having issues doing this and it looks like others have as well. Here's the solution I have some up with for generation IDs in SSIS for use in import processes.
For me the data I am importing includes accounts and addresses (legacy system does not have separate address data). I run a simple Select statement as my OLE DB source then sort the data and join it with my phone number data.

Next I add 2 Derived Columns using the Derived Column Transform (i tried just adding them as nulls in my select statement but SSIS sees null as a Int Data type in the meta data) the columns look like this:
Column Name Derived Column Expression Data Type Length Code page
AddressID NULL(DT_STR,12,1252) string [DT_STR] 12 1252 (ANSI - Latin I)
AccountID NULL(DT_STR,12,1252) string [DT_STR] 12 1252 (ANSI - Latin I)


I then send the data set into a Script Component Configured for Transformation (a popup option as soon as you add the Script Component to the Package). Add all the columns to the Input (might just need the 2 not sure) and make the 2 derived columns ReadWrite. Click on the Inputs and Outputs section in the Left tree view and name the input Something meaningfull (Accounts, etc)
Now the Meat of the script component the actual script.

Click script and you can see on the right side some options. Here you can add your SLX Connection string if it is stored in a package variable. for this ill just hard code it for now in the script. Click on the "Design Script" button and a VB.net editor will open.

Add an "Imports System.Data.OleDb" below is the class with some information removed for privacy
Public Overrides Sub IndAsAccountAddress_ProcessInputRow(ByVal Row As IndAsAccountAddressBuffer)
strSLX = "Provider=SLXOLEDB.1assword=somepasswordersist Security Info=True;User ID=username;Initial Catalog=SLXAPP;Data Source=ServerName;Extended Properties=""PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;AUTOINCBATCHSIZE=1;"""
connSLX = New OleDbConnection(strSLX)
connSLX.Open()

cmdAddress = New OleDbCommand(String.Format("slx_DBIDs('{0}', {1})", "ADDRESS", 1), connSLX)
readerAddress = cmdAddress.ExecuteReader()

Do While readerAddress.Read
Row.ADDRESSID = readerAddress.GetString(0)
Loop

readerAddress.Close()
cmdAddress.Dispose()

cmdAccount = New OleDbCommand(String.Format("slx_DBIDs('{0}', {1})", "ACCOUNT", 1), connSLX)
readerAccount = cmdAccount.ExecuteReader()

Do While readerAccount.Read
Row.AccountID = readerAccount.GetString(0)
Loop

readerAccount.Close()
cmdAccount.Dispose()

connSLX.Close()
End Sub
End Class
[Reply][Quote]
Guy Barrett
Posts: 63
 
Re: Generating SLX DBIDs in SSISYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 03 Jul 07 10:56 AM
Holy Moly!

I'll give it a go as soon as I get a chance. It makes sense to the likes of me and you that have to deal with this on a daily basis, but it's hellishly complex for what should be the extremely simple task of throwing the data directly into SLX through the OLE DB provider and not providing the primary key ID (as such, forcing the OLD DB provider to do it).

Well done though Patrick, I'll give it a crack!
[Reply][Quote]
Patrick Sullivan
Posts: 13
 
Re: Generating SLX DBIDs in SSISYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 03 Jul 07 11:01 AM
The advantage for me doing it this way is when i load my data I am using SQL Server Destination objects.

I believe if you use an OLE DB Destination and enable the AutoIncrement it would also work that way. There you run into the generation of all the Transaction files for the remote users. Since we are cutting new databases anyway this isn't an issue.

So far in my test runs on this the performance hasn't been great. I am actually running out of virtual memory on my local machine when i run around 200,000 records. We're hoping its not an issue on the servers with 4gb of ram etc.
[Reply][Quote]
Patrick Sullivan
Posts: 13
 
Re: Generating SLX DBIDs in SSISYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 11 Jul 07 5:47 PM
So i think I found out why i was having memory issues, I was not overriding the AcquireConnections, PreExecute, and ReleaseConnections methods. I believe the code above is opening a new connection for every row (EEEK!@) so now it looks more like the code below:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.OleDb

Public Class ScriptMain
Inherits UserComponent

Dim strSLX As String = Nothing
Dim connSLX As OleDbConnection
Dim readerAddress As OleDbDataReader
Dim cmdAddress As OleDbCommand
Dim readerAccount As OleDbDataReader
Dim cmdAccount As OleDbCommand
Dim vars As IDTSVariables90 = Nothing

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
VariableDispenser.LockForRead("SLXConnectionString")
VariableDispenser.GetVariables(vars)
strSLX = Convert.ToString(vars("SLXConnectionString").Value)
vars.Unlock()

connSLX = New OleDbConnection(strSLX)
connSLX.Open()
End Sub

Public Overrides Sub PreExecute()
cmdAddress = New OleDbCommand(String.Format("slx_DBIDs('{0}', {1})", "ADDRESS", 1), connSLX)
cmdAccount = New OleDbCommand(String.Format("slx_DBIDs('{0}', {1})", "ACCOUNT", 1), connSLX)
End Sub

Public Overrides Sub IndAsAccountAddress_ProcessInputRow(ByVal Row As IndAsAccountAddressBuffer)
readerAddress = cmdAddress.ExecuteReader()
readerAccount = cmdAccount.ExecuteReader()

Do While readerAddress.Read
Row.ADDRESSID = readerAddress.GetString(0)
Loop

Do While readerAccount.Read
Row.AccountID = readerAccount.GetString(0)
Loop

readerAddress.Close()
readerAccount.Close()
End Sub

Public Overrides Sub ReleaseConnections()
connSLX.Close()
End Sub
[Reply][Quote]
Patrick Sullivan
Posts: 13
 
Re: Generating SLX DBIDs in SSISYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 23 Jul 07 9:43 AM
Ok so after awhile of doing things in the Above manner I've found the performace quite lacking and have come up with a much faster solution.

First Instead of using a Script Component as a transformation I use it as a source now. First as you fill a mid-tier table with prospective accounts to migrate/import fill a row count value for the method call in the script. Add that variable as a readonly variable in the script component properties. Next the script. Fairly simple.

Here's the class:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.OleDb

Public Class ScriptMain
Inherits UserComponent

Dim strSLX As String = Nothing
Dim connSLX As OleDbConnection
Dim reader As OleDbDataReader
Dim cmd As OleDbCommand
Dim vars As IDTSVariables90 = Nothing

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
VariableDispenser.LockForRead("SLXConnectionString")
VariableDispenser.GetVariables(vars)
strSLX = Convert.ToString(vars("SLXConnectionString").Value)
vars.Unlock()

connSLX = New OleDbConnection(strSLX)
connSLX.Open()
End Sub
Public Overrides Sub PreExecute()
cmd = New OleDbCommand(String.Format("slx_DBIDs('{0}', {1})", "ACCOUNT", Variables.CompanyRowCount), connSLX)
End Sub
Public Overrides Sub CreateNewOutputRows()
reader = cmd.ExecuteReader()

Do While reader.Read
AccountIdsBuffer.AddRow()
AccountIdsBuffer.ID = reader.GetString(0)
Loop

reader.Close()
End Sub

Public Overrides Sub ReleaseConnections()
connSLX.Close()
End Sub
End Class

The issue now is you have 1 table storing say 100 IDs for the 100 acounts you are migrating
ACCOUNTID
BD123456778S
ETC...

And a Table with the account data and a null ACCOUNT ID
ACCOUNT ACCOUNTID Address1 ETC....
Some Company NULL Test 1

And if you look at at it there is no way to link the data up and fill the ACCOUNTIDs in table 2 with the ids in table 1. SQL 2005 to the rescue here. Using the ROW_Number() functionality included in the new version of TSQL we can join these tables. Below is a sample script which will work with the AdventureWorks database.
Just pretend the column SLXID is an actual ID from the OLE DB Provider.

declare @temp table(id int IDENTITY(1,1), SLXID varchar(1))
INSERT INTO @temp
select 'A'
INSERT INTO @temp
select 'B'
INSERT INTO @temp
select 'C'
INSERT INTO @temp
select 'D'
INSERT INTO @temp
select 'E'
INSERT INTO @temp
select 'F'
INSERT INTO @temp
select 'G'
INSERT INTO @temp
select 'H'
INSERT INTO @temp
select 'I'
INSERT INTO @temp
select 'J'
INSERT INTO @temp
select 'K'
INSERT INTO @temp
select 'L'
INSERT INTO @temp
select 'M'
INSERT INTO @temp
select 'N'
INSERT INTO @temp
select 'O'
INSERT INTO @temp
select 'P'


SELECT LastName, ContactID, Contacts.RowNumber as CRowNumber, IDs.RowNumber as IRowNumber, IDs.SLXID
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY LastName, FirstName, MiddleName) AS 'RowNumber',
ContactID,
LastName,
FirstName,
MiddleName,
Suffix
FROM Person.Contact
) Contacts
INNER JOIN
(
SELECT
ROW_NUMBER()OVER(ORDER BY SLXID) as 'RowNumber',
id,
SLXID
From @temp
) IDs
ON Contacts.RowNumber = IDs.RowNumber


Switching to this method of generating IDs took a process that was taking close to 2 hours for just under 200k rows to just under 3 minutes.
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: Generating SLX DBIDs in SSISYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 23 Jul 07 6:17 PM
Thanks for sharing this info Patrick - good to know - when I get 30 minutes to work out what is going on!

Not quite the 'codeless' environment envisaged by SSIS developers though
[Reply][Quote]
Patrick Sullivan
Posts: 13
 
Re: Generating SLX DBIDs in SSISYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 24 Jul 07 9:27 AM
I wouldn't say they envisioned a codeless environment. The script component and script task are both very robust from what I have been reading. They definitely made most the common stuff quite simple etc but they did leave room for more advanced work when it is needed. Also the creation of custom objects seems to be much easier than ever. Anyway if you have any questions when you start messing with this let me know.
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: Generating SLX DBIDs in SSISYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 31 Jul 07 3:38 AM
So I've finally waded into this stuff myself - and now I understand most of what you're talking about. Having it formatted in the .NET IDE sure helps too.

I have a few questions though:

1) What's going on in this line?
String.Format("slx_DBIDs('{0}', {1})", "ACCOUNT", Variables.CompanyRowCount)
Specifically, what does the string.format do and what do the {0} and {1} parameters mean? Sorry, but it's been ages since I've done any .NET, so I'm very rusty and 'thinking in VBScript' at the moment.

2) You have a tasty loop:
Do While reader.Read
AccountIdsBuffer.AddRow()
AccountIdsBuffer.ID = reader.GetString(0)
Loop

But I cannot see where AccountIDsBuffer is declared in your script. I do not even know what sort of object it is!

I am interested in this, as I am looking at how to perform scripted INSERTs into SLX tables, once I have my new ID or IDs ready to go.

I thought that you might have used an OleDBDataAdaptor, but there is no .AddNew method (there goes that VBScript again!), so I am a bit lost as to how to do this elegantly.

I know that I can generate a long INSERT INTO blah blah SELECT gubbins etc SQL statement and then just execute it, but that would be a step backwards, I think. Any pointers?

Thanks for any assistance
Phil

[Reply][Quote]
Patrick Sullivan
Posts: 13
 
Re: Generating SLX DBIDs in SSISYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 31 Jul 07 9:29 AM
1) String.Format is a .Net thing. {0} and {1} are parameter placeholders and are actually replaced with the "ACCOUNT" and Variables.CompanyRowCount values. For example if I did a string.Format("Hello {0}", "World") the string is actually "Hello World".

2) the AccountIdsBuffer.AddRow stuff is based on the Name of the output of the Script Source Component. If you go to the edit screen and click on "inputs and Outputs" i believe the default is Output0 or something similar. THe buffer is where we need to add rows. SSIS creates these objects for you based on the name of the Output, so Say your output was ContactIDs. the Buffer to which we would add rows is ContactIDsBuffer.

This solution is mainly for large numbers of Accounts, etc will create all the IDs you need. I am using a mid-tier table/database for storing them. What I then do is use some SQL similar to the SQL in the post to update the IDs. The SQL Posted requires TSQL90 compatibility.
[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): 6/24/2024 8:35:21 PM