sql server dts: I am converting an Access Database to SQL. My problem is that I have
many different source fields I want to put in to one field
(CaseNotes,Notetext) in the DTSdestination but I want each to create a
new recordset. Right now the last transformation to take place is the
only one I can see when executed. Also it is creating a record for
each case number even if null even though I used and IF THEN statement.
I want to be able to have many rows for each case if the data is there
or nothing. Here is the code I have so far for one transformation:
Any and All suggestions would be most appreciated.

Thank you,

Amy

Thank You for your response, I think this will help, however, I am not
trying to merge the source fields into one record, I am trying to but
many source fields into separate records in one field. Would I still
use the same process you gave me? Sorry, to sound so ignorant...I have
been trying to figure this out for so long, my mind is no longer
working. Thank you again for your help.

Amy:

Have you looked into the DTSTransformStat_? constants? It sounds like you
want to do a many records to one record or one record to many records
operation and the way to do those is to use some of the
DTSTransformStat_SkipFetch or DTSTransformStat_SkipInsert constants. To see
the possible values, check into the BOL. You will probably have to use a
global variable to determine where you are in the process with each pass
through your ActiveX script.

Another option you can look into (if you are wanting to modify more than one
destination table) is to use a lookup. A lookup can be anything (select,
update or delete SQL statements).

Most of what you want to do can be done using ActiveX scripts (including
merging X fields into one field). To do this, in the Transform Data Task
Properties dialog on the Transformations tab click new and choose ActiveX
Script from the list. You can program your transform from there as you
wish. Make sure you include all your source and destination columns (may
have to cancel out of the script editing mode to the Transformmation Options
dialog to configure that then click on the Properties... button to edit the
script again). ActiveX scripts are powerful in DTS, but you will have to
brush up on your VBScript if you don't program in it often.

Scott

[quoted text, click to view]

Amy:

It would help us all if you used similar terminology. Let me explain:

A table is an object in a database that holds records/rows of information.
A table is a collection of records or rows.

A row/record is an object in a database that holds relivate information
(divided up into fields) about an "entity." A row is a collection of
fields.

Now, in DTS you will be transforming from a source to destination. A source
can be a table or rowset (subset of or selection from a "table") and the
destination will be a table.

Now that that is clear, what are you trying to do? E.G. Your statement that
you are trying to do a "many source fields into separate records in one
field" doesn't make sense using the above object heirarchy. Tables contain
rows/records which contain fields. Fields don't contain records. What it
sounds like you want is a one to many relationship which requires two tables
and a foreign key to join them. If that is what you want, you will have to
use a lookup (if it has to be done with only one data pump) because you have
one source table and two destination tables.

Unfortunately, I will not be able to respond till Monday. Maybe if you can
describe what you are trying using the above terminology, someone may be
able to pick up where I left off. Otherwise, I'll check back on Monday to
assist you further.

Scott

[quoted text, click to view]