I am trying to import a flat file of semi-colon delimited values into a
table. The table has many foreign keys that reference other tables. The
flat file has the data all explicitly stated instead of the ID numbers. I
want to make it so that if a new value is encountered, a new entry in that
related table is created. An example: The main table, CITTAS_Primary, has a
foreign key, DrayageID, that links to the table CITTAS_Drayage_Company, which
has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayage.
I can parse the entries that exist in the table already, but I am unsure of
how to insert new values using an ActiveX Script Task. I am currently using
Execute SQL tasks to retrieve the recordsets from CITTAS_Drayage and
CITTAS_Drayage_Company and then place the resulting rowsets into global
variables. Here is the code that I have so far:



Function Main()
Dim Company, CompanyID
Company = DTSSource("Col003")

Dim Charge, ID
Charge = DTSSource("Col009")
ID = -1

Dim comps, chs
Set comps = CreateObject("ADODB.Recordset")
Set comps = DTSGlobalVariables("DrayageCompany").Value
Set chs = CreateObject("ADODB.Recordset")
Set chs = DTSGlobalVariables("DrayageCharge").Value

Dim i
For i = 1 To comps.RecordCount
If comps.Fields("DrayageCompany") = Company Then
CompanyID = comps.Fields("CompanyID")
Break
End If
comps.MoveNext
Next i

For i = 1 To chs.RecordCount
If chs.Fields("DrayageCost") = Charge And chs.Fields("DrayageCompanyID") =
CompanyID Then
ID = chs.Fields("DrayageID")
Break
End If
chs.MoveNext
Next i

If ID = -1 Then
'Insert code to insert new record
'Get ID for new cost/company pair
End If

DTSDestination("DrayageCompanyID") = ID
Main = DTSTransformStat_OK
End Function



I want it to be able to insert new companies into CITTAS_Drayage and new
cost/company pairs into CITTAS_Drayage_Company.

Also, for some reason, it is complaining about using the RecordCount
property of the recordset. I would think that it would work since it is
straight from SQL Server Books Online DTS collection. If anyone knows what's
going on with this, please enlighten me, because it amkes no sense to me.

Thanks in advance, everyone.

--
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps

Hi Chris

I am not sure of the example you talk about but
DTSGlobalVariables("DrayageCompany").Value may not be a recordset.

John

"Chris Lieb" wrote:

BTW... I was going to add..

Rather than mess around with the activeX script it may be quicker (and IMO
easier) to load into a staging table, sort out the missing FKs and then load
into the live table.

John

"Chris Lieb" wrote:

You could use two lookups in your transform. One to test if the value
exists in the table and return its ID. If ( NOT ID > 0 ) Then execute a
second transform that performs an insert and returns the @Source_identity
of the newly inserted record. This would be your foreign key for the
transformed record.

-Mike Gercevich

--
Message posted via http://www.sqlmonster.com