DTS Newbie

I am new to DTS...and my question is 2 parts.  This is an automated process I am setting up in DTS which needs to run daily.

Part 1 - I have an input file (in excel) that I need to either apply updates or inserts (based on LastName and FirstName matches) into an Oracle Table.  The problem is the Excel file has one of its columns that contains all the address data that needs to be broken out into 5 different address fields:
STRT1, STRT2, CITY, STATE, ZIP.  The user has finally agreed to separate all the address parts with the a comma, but they cannot export that address into an excel file with the different addr fields, so I need to deal with that myself.   I am loading all the data into a temp table to start with.

Within the transformation, I am assuming I need to use ActiveX to actually map the transformation - can you give me an example of the syntax that I would use to pull the address data from a single column in Excel (WORK_ADDRESS) and map to STRT1, STRT2, CITY, STATE, ZIP with commas as the delimiter?

Part 2 - After I apply the updates/inserts from the temp table I created above to the appropriate database tables, I need to set up the process in DTS where I need to generate another 2 other files, which need to be SFTP'd to a vendor.  We use SecureFX as our FTP tool.  IDTS allows you to nicely transfer a file using normal FTP, but it looks like to use SFTP I have to write an ActiveX script (which I am also new to) in order to run the SecureFX client via command line.

If someone could give me an example of how to write the ActiveX script to call an FTP process, I can figure out the specifics for our specific SFTP tool.  Or does someone know a better way to do what I am trying to do?

Thank you!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I usually avoid ActiveX scripts because they can get large and difficult to debug.

I prefer to use stored procedures to perform the tasks you have described.

For example, within a DTS package:

1. Import your Excel file into a staging table - a table that matches the layout of the excel file + additional address fields (using a data pump)
2. Run a stored procedure that splits out the adress data (not a trivial exercise!!!), (using an Execute SQL Task)
3. Run a stored procedure that applies the inserts/updaets, (using an Execute SQL Task)
4. Export the required files, using a data pump
5. FTP the files using an execute process task

Thats the high level explanation. Now I will explain each step in detail:

1. Hopefully this is self explanatory

2. Here's an example stored procedure that will split some data out into more fields. Cleaning Address fields is NOT a trivial task - usually there is no rhyme or reason to the source data. This SP is just a very trivial example of what you could do.

CREATE PROC p_SplitAddressField

UPDATE StagingTable
STRT1 = LEFT(Address,CHARINDEX(Address,' '))

3. I assume you want to match on some key and if the key matches you want to update, and if it doesn't you want to insert?

CREATE PROC p_RefreshAddresses

-- Update existing records
UPDATE AddressTable
STRT1 = StagingTable.STRT1,
STRT2 = StagingTable.STRT2,
CITY = StagingTable.CITY,
STATE = StagingTable.STATE
FROM StagingTable
WHERE StagingTable.SomeKey = AddressTable.SomeKey

-- insert new records
FROM StagingTable
WHERE StagingTable.SomeKey NOT IN
    (SELECT SomeKey FROM AddressTable)

4. Hopefully self explanatory

5. If SecureFX can be run as a command line with switches then just put the correct command line into a batch file then run the batch file from an Execute Process Task.

The whole point of doing it this way is that every piece is modular and can be thoroughly tested.

This is admittedly a very quick sparse explanation but I like to ensure that the poster is around to respond before I put too much effort in.

So if this sounds like an agreeable solution, post abck and I will explain some more.
tinatsunamiAuthor Commented:
Unfortunately, I am under instruction to use the data transformation task for the break-out of the address fields.
I have already addressed the upd vs inserts in the sql task within DTS from my temp table to the real table I am updating (based on the assumption that I am using a temp table to store the excel file's data properly "broken out").   My issue is using DTS to get the data from the excel spreadsheet (which the nasty address column) into the temp table with the address data parsed out into the correct fields.  But, if you can delve into Step 2 a bit more for me ... break out at least 3 of the address fields so I can get a better understanding of how that works... I might be able to work with that.

And sorry, I didn't understand step 5 at all (I understand conceptually, but not to the level of detail to actually carry it out).

Thank you for your help
OK, I hope this person realises that ActiveX probably won't be supported going ahead with SSIS. It really is not the best solution, but I'll stop moaning about that now!

Here are steps to split out a field which is comma delimited into multiple fields using ActiveX

1. Set up your data pump between the source Excel file and the destination staging table.
2. In the transformation tab in the data pump, select your single source field (Address?) then select your destination fields on the right (using CTRL-click)
3. Press New then select ActiveX Script as the transformation type
4. Press the Properties button to open the ActiveX designer
5. You'll see get some default code. Just delete it and paste this in:

Function Main()
  Dim sAddress
  ' Put the full address into a working variable
  sAddress = DTSSource("Address")

  ' Assign the first segment delimited by a comma
  DTSDestination("STRT1") = Left(sAddress,InStr(sAddress,",")-1)

  ' Also remove this first segment from the working variable
  sAddress = Right(DTSSource("Address"),Len(sAddress) - InStr(sAddress,","))

  ' Assign the second segment delimited by a comma
  DTSDestination("STRT2") = Left(sAddress,InStr(sAddress,",")-1)

  ' Remove this second segment from the working variable
  sAddress = Right(DTSSource("Address"),Len(sAddress) - InStr(sAddress,","))

  ' Assign the second segment delimited by a comma
  DTSDestination("CITY") = Left(sAddress,InStr(sAddress,",")-1)

  ' Remove this second segment from the working variable
  sAddress = Right(DTSSource("Address"),Len(sAddress) - InStr(sAddress,","))

  ' And so on

  Main = DTSTransformStat_OK
End Function

This is not tested!!! but hopefully you get the idea.

Gotta run but I will post back shortly

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

That ActiveX method isn't the fastest but its probably the most illustrative. Basically you reference source and destination columns in your code and use VBScript functions to break the data up.

If the field contains less than four commas, or if the street names contain commas then your going to have to do something more sophisticated.

I am always wary of text fields that are manually enterd with no valiidation. Usuually about 90% of the code is in there to allow for exceptions in the data and only 10% allows for correctly entered data.

Regarding FTPing your data.

You'll need to go to your FTP programs documentation and find out if you can run it with command line switches.

You'll probably end up with something in this form:

FTP -S Server-U user -P password -F pathttosourcefile

Thats just an example but basically you just open notepad, paste the command line into it then save the file with a CMD extension.

Now you can just run that CMD file to perform your FTP - ie double click it in Windows Explorer, run it in a SQL Server job, or for our purposes, run it in an Execute Process Task in DTS.

You can just paste the path to the CMD file into a Execute process task in DTS and it will run it.

So look up 'command line' or 'switches' in the Secure FTP product documentation and it should have some syntax and maybe even some examples. If you post it here I can help.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tinatsunamiAuthor Commented:
Thanks much - this is very helpful!  
If you have any troubles post back.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.