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?