DTS Newbie

Posted on 2006-04-03
Medium Priority
Last Modified: 2013-11-30
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!
Question by:tinatsunami
  • 4
  • 2
LVL 30

Expert Comment

ID: 16366279
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.

Author Comment

ID: 16366476
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
LVL 30

Expert Comment

ID: 16366779
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 30

Accepted Solution

nmcdermaid earned 2000 total points
ID: 16366885
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.

Author Comment

ID: 16366987
Thanks much - this is very helpful!  
LVL 30

Expert Comment

ID: 16367018
If you have any troubles post back.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question