Complex File Import

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, whic
h
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/AppsHi Chris
I am not sure of the example you talk about but
DTSGlobalVariables("DrayageCompany").Value may not be a recordset.
John
"Chris Lieb" wrote:

> 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, wh
ich
> has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayag
e.
> I can parse the entries that exist in the table already, but I am unsure o
f
> how to insert new values using an ActiveX Script Task. I am currently usi
ng
> 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 wha
t'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|||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:

> 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, wh
ich
> has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayag
e.
> I can parse the entries that exist in the table already, but I am unsure o
f
> how to insert new values using an ActiveX Script Task. I am currently usi
ng
> 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 wha
t'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