DTS Newbie

Posted on 2006-04-03
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
    LVL 30

    Expert Comment

    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
    INSERT INTO AddressTable (SomeKey,STRT1, CITY, STATE)
    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

    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

    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

    LVL 30

    Accepted Solution

    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

    Thanks much - this is very helpful!  
    LVL 30

    Expert Comment

    If you have any troubles post back.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now