DTS Select using record position

Hi there, it has been a while since i have posted. I am in a
situation where I am stumped. I am learning to build a dts package
where I am connecting to a table in an AS400. This database is being
maintained by an outsourced company and therefore I can't change the
table structure or even ask them to. Anyway, this table currently has
about 104,000 records. I am building a package to check it and pull
out the most recent records and put them where they go in my SQL
Server 2000 tables. The only way I can think of to get the most
recent records is to use a global variable in the package to remember
the record count and then get those records from that record position
on. Problem is, I have no idea how I would go about selecting records
from a record position. Does anybody have any ideas or should I be
using a different approach? There are no time stamps to work from. I
was told that the AS400 records, including updates, are appended to
the table, which is why I thought this approach made sense. I would
truly appreciate any help.phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0403031553.6e5f7de7@.posting.google.com>...
> Hi there, it has been a while since i have posted. I am in a
> situation where I am stumped. I am learning to build a dts package
> where I am connecting to a table in an AS400. This database is being
> maintained by an outsourced company and therefore I can't change the
> table structure or even ask them to. Anyway, this table currently has
> about 104,000 records. I am building a package to check it and pull
> out the most recent records and put them where they go in my SQL
> Server 2000 tables. The only way I can think of to get the most
> recent records is to use a global variable in the package to remember
> the record count and then get those records from that record position
> on. Problem is, I have no idea how I would go about selecting records
> from a record position. Does anybody have any ideas or should I be
> using a different approach? There are no time stamps to work from. I
> was told that the AS400 records, including updates, are appended to
> the table, which is why I thought this approach made sense. I would
> truly appreciate any help.

How would you know where the recorded position is? Does the AS/400
have a row number field, or something similar? And what happens if
rows are deleted in the source table?

Assuming that the AS/400 table has a primary key, then you might be
able to create a linked server from your MSSQL server, and then use a
query like this to insert all the rows which don't exist:

insert into dbo.MyTable (col1, col2, ...)
select col1, col2, ...
from AS400...Table t
where not exists (select * from dbo.MyTable mt
where mt.PrimaryKey = t.PrimaryKey)

Updates would be more difficult, as you'd need to have some way of
identifying the rows which have been modified. You might want to
consider looking at heterogeneous replication for this, so that
changes on the AS/400 are automatically replicated to the SQL Server,
although you would probably need a third-party tool for this.

Simon

Dts Record Count

I have a dts package that export data from a database to a text file. I could like to create a validation script to coun the data in the database that should be exported to file and then re-count again after the data is in the text file. Also count the number of columns that is exported.

Is there a way to do that within a dts package?

Help!

LystraYou ought to be able to do that using DTS Global Variables (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_82nn.asp).

-PatP

DTS Parsing Delimited Record

I am running SQLServer 2000 and am parsing a delimited file. Each line
is delimited with a crlf, but each line also ends with a tilda (~) that
I do not want to process. Each line does not have the same number of
elements, but every line ends with a tilda. Is there a way to set the
input file parser to strip off the tilda (that is, define the end of
line marker to be ~+cr+lf ?
Thanks,
greg lindstromhi,
why don't use each line as 'fixed field' from the beginning till the end and
then, using VbScript and substring do what you want.
--
current location: alicante (es)
"tuba_ranger" wrote:

> I am running SQLServer 2000 and am parsing a delimited file. Each line
> is delimited with a crlf, but each line also ends with a tilda (~) that
> I do not want to process. Each line does not have the same number of
> elements, but every line ends with a tilda. Is there a way to set the
> input file parser to strip off the tilda (that is, define the end of
> line marker to be ~+cr+lf ?
> Thanks,
> greg lindstrom
>

DTS Only Brings in Half of Text File Records

All rows of a 1500+ record text file we have at work should be 512 spaces. Some are not and seem to be causing problems. I get half the records I should be getting on an import. When the DTS task comes across one of these records it takes it and appends them together and makes two rows one row. Any ideas? I tried making a table with one field of datatype char(1000) and tried to import into this table. I figured this would add any missing trailing spaces but no go.

ddaveWhat is the text file format? CSV? Pipe-delimited? Tab delimited? I'd suspect that you need to specify the end-of-line delimiter for DTS.|||It doesn't appear to have a delimiter, just fixed width. I am suspecting that there may not be a carriage return/line feed at the end of every other row. Is this a possibility? Aren't there text editors that can view this type of thing?

ddave|||I am suspecting that there may not be a carriage return/line feed at the end of every other row.Then the problem is with your file format, not with DTS. Who generates this file for you?|||It is our client and we KNOW that the client's extraction process is absolutlely infallable, don't we?:). I confirmed it is fixed length and the fact that the records are of varying length is throwing the process off somehow. We just found this out as we took our text editing software, UltraEdit, and appended trailing spaces to all files less than the 512 character standard set by the client. When we appended these characters manually the job ran correctly. Therefore the question becomes is there a way to get the text file into a table with one field and then append trailing spaces? I can handle the query part but I am having a tough time getting it into a table.

ddave|||When I try to import the flat file w/o manually appending spaces it only brings in the first 50 or so characters. I have tried importing into a table that has one column using enterprise manager. I tried changing it from char(1000) to nvarchar(1000) but still had problems.

ddave|||What happens if you import it into a spreadsheet first? DO you get all 1500+ rows?

If so, then you could DTS it into SQL Server from the spreadsheet.|||Well I didn't want to say it but my supervisor put it into MS Access first (again manually) and he gets all 1500+ records and all are the correct length. The closest thing we could probably do is to have the DTS job bring it into Access and then into SQL Server. M-I-C......K-E-Y..........

ddave|||Access has an excelent file import utility, but DTS should be able to do anything Access can do. There has to be a setting in DTS that affects this.
You say your file is fixed-width, but it still has to have record delimiters if not column delimiters.|||I think I resolved it. In the DTS job we originally have the text file import step as a fixed width. It is a fixed width file but for some reason it is truncating around 50 columns, which is where some blank spaces begin. There is ensuing data further to the right but anyway DTS seems to be reading it as the end of the record for some reason. I changed the import to delimited and selected tab. There are no tab delimiters but in this case DTS is bringing in the whole record through 512 characters into a single field table. The datatype is char(512). This seems to have solved it. Thanks.

ddave

DTS load master file to different tables

We need to load a "master" flat file to SQL Server tables. The file is a dump from mainframe. Based on a field called "record_type", each record in the file has different columns. I would use the following as an example (the real file is much more complicated than this, but you get the idea):

For instance, my file has:

20M02221984PAPepsi1000
23F11121987MD1000
01M09182003TXCocacola1100
34F03041970DC900

If "M", the fields are "age", "gender", "birthdate", "state", "salary"

If "F", the fields are "age", "gender", "birthdate", "state", "company", "salary"

We need to load the file (only one file) into two different tables, M_table, and F_table. But I have researched and discovered in DTS the source (TEXT file) can not be queried against to filter on the gender field.

Since each record may have different number of fields, I cannot really load the flat file into a "staging" table.

Does anyone has any idea on how to achieve this? Thanks in advance!!!It should be:

If "F", the fields are "age", "gender", "birthdate", "state", "salary"

If "M", the fields are "age", "gender", "birthdate", "state", "company", "salary"|||Since your data sets are obviously not fixed-length, you could use a temporary master table into which you import all data sets, e.g. into only one column of varchar(nnn).

Then use queries with string-functions to split the data into the correct number and type of columns for the respective tables M_table and F_table.|||kbk's solution is probably simpler (and therefore usually better). You may also consider a Data Driven Query task. It won't be entirely straightforward and it will require a substantial amount of work in the ActiveX script component (and it's performance will be slower).

But other than these drawbacks, it may work!

Regards,

hmscott

DTS import new record can be replicated

I had merge replication setup. The replication is work fine if I insert new records
from enterprise manager. But, if use DTS to import new record from Access,
those new records can not be replicated. However, I updated one of those new record content, this updated new record will be replicated.
I appreicate you input.
James Cheng
James,
I have seen this type of behaviour before when if you bulk insert the rows
and choose the defaults, then FIRE_TRIGGERS is false and consequently the
rows are not added to MSmerge_contents. Disabling Fast Load may cause the
triggers to fire on new inserts or run sp_addtabletocontents to include the
rows already existing and then resynchronise.
Regards,
Paul Ibison
|||check out
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"James Cheng" <JamesCheng@.discussions.microsoft.com> wrote in message
news:3FF3AE3C-F66D-468F-92A3-EA3E2F879599@.microsoft.com...
> I had merge replication setup. The replication is work fine if I insert
new records
> from enterprise manager. But, if use DTS to import new record from
Access,
> those new records can not be replicated. However, I updated one of those
new record content, this updated new record will be replicated.
> I appreicate you input.
> James Cheng

DTS Import from Text file - can't skip just one row

Hello,
I am trying to import a text file to a SQL table. The file is fixed length
format, but the first record is a header record and I want to skip it. If I
set the import wizard to skip 1 row, it skips the header AND the next row.
If I set it to skip 0 rows, I get the header AND the next record. I cannot
get it to skip just the header. I did NOT select "First row has column
names."
Any ideas?
Thanks,
- ArtHave you tried selecting the "First row has columne names"
at all? What happens when you do this? I have used this
a number of times when the column headers were actually in
the first row, but I am not sure what it will do with
random header data in te first row.
Also, is the header the correct fixed length? Maybe that
is why skipping the first row removes the first real data
row also? Or maybe there is a missing end of row
character?
Just some initial thoughts. I hope this helps somehow.
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hello,
>I am trying to import a text file to a SQL table. The
file is fixed length
>format, but the first record is a header record and I
want to skip it. If I
>set the import wizard to skip 1 row, it skips the header
AND the next row.
>If I set it to skip 0 rows, I get the header AND the next
record. I cannot
>get it to skip just the header. I did NOT select "First
row has column
>names."
>Any ideas?
>Thanks,
>- Art
>
>.
>|||Art,
just check the First row has column names, and do not skip any row.
Quentin
"Art Bragg" <abragg@.transedge.com> wrote in message
news:OztK6aeWDHA.3088@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to import a text file to a SQL table. The file is fixed
length
> format, but the first record is a header record and I want to skip it. If
I
> set the import wizard to skip 1 row, it skips the header AND the next row.
> If I set it to skip 0 rows, I get the header AND the next record. I
cannot
> get it to skip just the header. I did NOT select "First row has column
> names."
> Any ideas?
> Thanks,
> - Art
>

DTS import CSV record to multiple tables

Hi,

I am using DTS in SQL server2k

What is the best way to insert CSV records, where 1 record maps onto multiple tables with parent/child or PK/FK relationship.

eg.
CSV record
(ID, param1, param2, param3)

ParentTable(ID as PK)
Param1Table(ID as FK in ParentTable, param1)
Param2Table(ID as FK in ParentTable, param2)
Param3Table(ID as FK in ParentTable, param3)

Currently i am using multiple Transform Data tasks with ActiveX script. But it seems to work slow.

Thanks and regards,
HenryI think I would use a staging table, then use a sproc to slice and dice...