I have some code that I am using to import a flat file into a bunch of
related tables. I am trying to make it so that new records are added to the
related tables of a new value appears. However, my code thinks that every
value is new and unique and creates a new entry for it. For example, one of
the tables should have about 20 records in it. Instead, it comes out at
2428, the same number of lines in the text file. Here is my code:


Dim InBound, InBoundID
InBound = DTSSource("Col004")
InBoundID = -1

Dim server
Set server = CreateObject("ADODB.Connection")
server.Open DTSGlobalVariables("ConnectionString").Value

Dim ib
Set ib = CreateObject("ADODB.Recordset")

ib.Open "SELECT * FROM [TOFC].[dbo].[CITTAS_InBound_RailYard] WHERE
InBoundRailYard = '" & InBound & "'", server
If ib.RecordCount = -1 Then
server.Execute "INSERT INTO [TOFC].[dbo].[CITTAS_InBound_RailYard]
([InBoundRailYard]) VALUES('" & InBound & "')"
ib.Requery
End If
InBoundID = ib.Fields("InBoundRailYardID")
ib.Close

Set ib = Nothing
Set server = Nothing

DTSDestination("InBoundRailID") = InBoundID
Main = DTSTransformStat_OK


To my eye, it looks like if the value is already in the table
CITTAS_InBound_RailYard, then its ID should be copied to the destination
column. For some reason, the INSERT is called for every record. Any help is
appreciated.

Also, how do you reset the identity value to 1 again? Because of all of
these extra records being inserted, the value is over 5000. I don't want to
have to worry about an overflow in the future, so being able to reset this
would be nice.

Thanks in advance.

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

Here is a row of data:

"02999029Q119";"0930";"PAC";"SEAIL";"SFWRR";"1545" ;"SF198";"LOS ANGELES,
CA";82.00;"PROVIDENCE, RI";"GRANDE VISTA, CA";"4";" "

The code references the column that contains "SEAIL".

"Chris Lieb" wrote:

So you have a flat file that contains n tables worth of data?

With flat files I always choose to BULK it into a staging table of the
same structure. I then use TSQL to do my comparisons and queries.

Can you not do that here?

Because you have included the DTS NG I presume you are doing this is an
Active Script task.

How you decide to BULK the data in is up to you.

Allan


"Chris Lieb" <ChrisLieb (AT) discussions (DOT) microsoft.com> wrote

"Allan Mitchell" wrote:

I don't have the permissions to use the Bulk Insert task. (It isn't easy
being new. Bare bones permissions, always begging IT for more.)

The flat file contains one table of data, I am just parsing it into multiple
related tables.

Yes, I am. I have simmilar tasks for other fields, and they are all
exhibiting this behavior.

I don't know what is going on with my script. It would be easier to figure
out the bug if there was a script debugger to use, but, alas, MS didn't
provide us with one. The only possibility that I can come up with is that
the quotes in the flat file are messing up the comparisons in my WHERE
clauses. Outside of that, I don't know why my queries never return a row.

Chris

OK So use the DataPump task to get the file in and then use TSQL to do
the fancy stuff.

Allan



"Chris Lieb" <ChrisLieb (AT) discussions (DOT) microsoft.com> wrote

I am not very familliar with the structures of TSQL. How would you implement
the record check that I use to insert new entries into other tables and
retrieve the relevant ID? I can get the easy string manipulation stuff taken
care of, but I don't know how to get the more complicated stuff done.

Thanks

Chris

"Allan Mitchell" wrote:

So you have a source staging table and a destination table

If the keys match in each table then the row exists already
If the key in the staging table is not in the destination table then the
row is new
If the key is in the destination table and not in the staging table then
the row has been deleted


You have to be able to compare what you want to bring in and what you
already have.

Once you have loaded the master table then you can join onto this to get
the IDs of the newly inserted rows with your staging table and you can
then load the child tables.

Allan




"Chris Lieb" <ChrisLieb (AT) discussions (DOT) microsoft.com> wrote