I am very new to DTS (as of today). I am able to pull the data that I want
based on my criteria. What I need to do though is to "Transform" the data
that is being pulled. I am able to do the simple things, like dates and
trims, etc. What I am not able to do is the more complex tasks.

Here's what I need to accomplish:

1. Replace hyphens with underscores
2. Split Account into two columns
a. First 9 chars = Entity
b. Last 5 chars = Account
3. Add columns and populate the column with an entry.
a. Category = Actual
b. DataSrc = XYZ
c. HR = NoPayrollID
e. IntCo = Non_InterCo
4. If the ProjectId field is null, enter the value NoProjectID

I have pretty strong background in VB coding. I go to the ActiveX Transform
option and it looks familiar, but I am just not familiar enough with the
syntax.

My original plan was to put the data into a txt file (that's what I'm doing
now) and then modify what I need using VB, but I would like to avoid that if
possible.

TIA
RC-

You have a number of options here and it depends on with which you are most
comfortable
Most SQL syntaxes have ways of doing what you want without reverting to
ActiveX transform as it will then be a Row*Row operation

SQL Server for Example

1. REPLACE()
2. SUBSTRING()
3. Not sure what you want here but you could enter a named expression
4. ISNULL()



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"RC-" <r_candee (AT) hotmail (DOT) com> wrote