4/23/2024 8:32:52 AM
|
| |
| 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 SQL SSIS or DTS to perform SalesLogix imports. View the code of conduct for posting guidelines.
|
|
|
|
Generating SLX DBIDs in SSIS
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.1 assword=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 |
|
|
|
Re: Generating SLX DBIDs in SSIS
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! |
|
|
|
Re: Generating SLX DBIDs in SSIS
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. |
|
|
|
Re: Generating SLX DBIDs in SSIS
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 |
|
|
|
Re: Generating SLX DBIDs in SSIS
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.
|
|
|
|
Re: Generating SLX DBIDs in SSIS
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  |
|
|
|
Re: Generating SLX DBIDs in SSIS
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. |
|
|
|
Re: Generating SLX DBIDs in SSIS
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
|
|
|
|
Re: Generating SLX DBIDs in SSIS
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. |
|
|
|
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!
|
|
|
|
|
|
|
|