DTS problem with AS400 flat file download

I'm having trouble importing a text file that was ftped down from an AS400 fixed length flat file. The text file appears to be formated correctly when I open it (1 record per line fixed format) but when I try to import it ASCII delimited it doesn't appear to be in that format and I'm not familiar at all with the AS/400 file formatting. This seems to only be a problem when the last field in the file is variable length. If the last field is a char 1 field it imports OK. Any idea on what I'm doing wrong with the import? I'm I going about this all wrong? Is there an easier way to get data into SQL Server from this AS400 flat file?
Any help would be greatly appreciated!!
Thanks,
DeidreDo you have IBM Client Access installed on your SQL Server? If so, you can create an ODBC connection to the AS/400. I do this for several AS/400 files at work and it couldn't be easier.|||No I don't have IBM Client Access installed on our SQL Server that's why we were ftping to a text file then trying to import it. I was trying to find a download for the IBM Client Access on IBM's website but I guess that's something you have to buy extra?|||I was under the impression that Client Access is something provided when you purchase an AS400. What kind of terminal emulator do your users use now to access the 400? The ODBC connection is what you really need more than all of the emulation that Client Access provides.

I hope I'm not beating around the issue here, but if you can get an ODBC connection, it would be much easier than the text file download.|||No thanks for the help! I think that would be alot easier too - if I can get the ODBC connection.
I know nothing about the AS400 side and I think that is my main problem. There is another group that handles the AS400 system and I just need a file they created to import into my system. I've been trying to get information from the other group with not alot of luck that is why I was trying the text file download. I will try to see if they can get me Client Access.|||You haven't mentioned how your doing the "import"

Is this AS400 file from DB2?

Also, what's the lrecl and how many rows?|||Do you have a file layout for the AS400 file? If the file is fixed width you will have to set the width of each column manually based on the file layout.|||You mention ascii delimited when referencing dts, but you also mention that the file is fixed length file - which is it. You have the option to import a fixed length field file as well in dts.|||Even easier...

If the file is less than 8000 bytes...dump it in to a temp table with 1 column..you can then parse it out, do validity checks, ect...

CREATE TABLE myTable99(Col1 varchar(8000))
GO

BULK INSERT myTable99 FROM 'd:\data\filename.txt'
GO

SELECT COUNT(*) FROM myTable99
GO|||I am importing using the dts wizard. I did try the fixed length ANSI option and it doesn't work either. It seems that all the fields are fixed length except the last field but none of the row delimiter options work for me. When I choose {CR}{LF} it catches the end of the first row then wraps the other rows that are longer than the first row. If I try any of the other row delimiter options I get this message "Could not find the selected row delimiter within the first 8 kb of data. Is the selected row delimiter valid?" but I see a double vertical line separating each row.
I am having this problem on all the files except 1 that I am trying to import from the AS400. The one that I am not having problems with last field is a 1 char field not variable length.
The AS400 guy said the file is not DB2 its just a AS400 fixed length flat file and I have the file format for all the files. The simples one is one with 2 fields- 1st field 2 chars - 2nd field 30 chars - variable row count.
Any other suggestion?
Thanks again for all the suggestions,
Deidre|||So it looks like it is pipe dilimited. Have you tried that delimimter yet?|||I don't see pipe as a row delimiter option - I've tried all the options I see - {CR}{LF}, {CR},{LF},Semicolon, Comma, Tab, Vertical Bar and none of them work.
Deidre|||In the Row Delimiter drop down you can add delimiters. Just click in the drop down and put in a pipe.|||Although vertical bar may be the same as pipe. Just to see if you can get it in a database, try importing it into access. The wizard is a little easier.|||It would be a lot easier if you could post a sample...

You say

but I see a double vertical line separating each row.

Do you mean row? Or Column? Quite different you know...

How much data are we talking about?

Did you try my example...should be pretty easy|||SHICKS -I tried entering the pipe delimiter in the row delimiter but it still didn't work. It imported in to Access great just used the wizard fixed width options and set the column breaks after the 2 and 32 chars.

--
Brett -- This works but I still don't see why I can't do it will a dts import:
CREATE TABLE #tblTemp(Col1 varchar(32))
GO

BULK INSERT #tblTemp FROM 'C:\test.txt'
GO

SELECT * FROM #tblTemp

insert into tblStageMajor
(sMajorCode,
vsMajorDescription,
dtImportDate)
select substring(Col1,1,2), substring(Col1,3,30),getdate()
from #tblTemp

drop table #tblTemp

I attached the file that goes along with this as an example of what I'm trying to import. I have several different files but this is the smallest. 2 columns 32 char width total - 27 rows - 1st column 2 chars -2nd column 30 chars.
The largest files is still only 147 char total row width, variable rows count(around 10,000 is normal), 12 fields.

Thanks,
Deidre|||I don't see no stinkin pipes (|)|||but I see a double vertical line separating each row
I agree with Brett - no vertical lines in that file. It is a little early in my timezone to see double anything yet - you haven't been sipping from the champagne bottle a little early, have you ? :-)|||Originally posted by rnealejr
I agree with Brett - no vertical lines in that file. It is a little early in my timezone to see double anything yet - you haven't been sipping from the champagne bottle a little early, have you ? :-)

And what time zone is that?

Also I tried to import that file...if you play around with the end of line parameter, you'll see that there's some kind of garbage at the end...|||OK I loaded it using my method, and everything looks fine

bizzare...try this

select LEN(Col1) from #tblTemp
SELECT '123456789012345678901234567890'
UNION ALL
select Col1 from #tblTemp

I thought I'd see extra bytes at the end...but I don't..

Hey ANOTHER reason not to use DTS...they just keep stacking up...|||That garbage is 0D0A - typical carriage return/linefeed ending|||Well DTS doesn't like it...did you try to import the test.txt file?|||Yep. Just add the control characters to the delimiter area.

dsweatman - Try the following in your dts script as a transformation:

Function Main()
strSource=DTSSource("Col001")
DTSDestination("Col001") = left(strSource,2)
DTSDestination("Col002") = right(strSource,len(strSource) - 2)
Main = DTSTransformStat_OK
End Function|||Check this out...I added 1 line...it looks like it's taking the line length definition from the first row...

bizzaro...

EDIT: And I just imported it Excel and Access...NO PROBLEMS...

DTS, you quirky little thing...

I even cut and pasted all the rows in to another file..same problem...|||I got it to work if I set the text file options to comma delimited.
and did the following in the transformation.

Originally posted by rnealejr
Function Main()
strSource=DTSSource("Col001")
DTSDestination("Col001") = left(strSource,2)
DTSDestination("Col002") = right(strSource,len(strSource) - 2)
Main = DTSTransformStat_OK
End Function

Thanks everyone!!|||Happy to help and Happy New Year !

Actually, in your example, any of the delimiters work - as long as your row delimter is set for cr/lf.|||Originally posted by dsweatman
I'm having trouble importing a text file that was ftped down from an AS400 fixed length flat file. The text file appears to be formated correctly when I open it (1 record per line fixed format) but when I try to import it ASCII delimited it doesn't appear to be in that format and I'm not familiar at all with the AS/400 file formatting. This seems to only be a problem when the last field in the file is variable length. If the last field is a char 1 field it imports OK. Any idea on what I'm doing wrong with the import? I'm I going about this all wrong? Is there an easier way to get data into SQL Server from this AS400 flat file?
Any help would be greatly appreciated!!
Thanks,
Deidre

hi,

first you confirm have specified carrage return while creating text file
in AS/400, If you are using client access system will take care
regarding carriage return
if you are using CPYFRMIMPF than you should specify carriage return

regards
NANAIAH
MUMBAI

DTS package Error

I fixed my own problem. I just recreated the package from
scratch and it worked. I don't know why I had that error
in the first place - so if anyone knows, please share your
response with me. Thanks.
Mary

>--Original Message--
>I'm almost new to this one:
>I run a store procedure that extracts data from one table.
>When manually run, the results returned is what is
>expected. But when I try to create a dts package to
>export the data to a text file, I get this error:
>Error Description: Null accessors are not supported by
>this provider.
>Can someone help me with this? Thanks.
>Mary.
>.
>Its possible that the MDAC version of the EM used was either higher than
what is currently on the server.
Olu Adedeji
"Mary Moore" <anonymous@.discussions.microsoft.com> wrote in message
news:6f8a01c40215$fbb3f460$a501280a@.phx.gbl...
> I fixed my own problem. I just recreated the package from
> scratch and it worked. I don't know why I had that error
> in the first place - so if anyone knows, please share your
> response with me. Thanks.
> Mary
>

DTS launch from SQL Server Agent

Hi,

I want to build a task that does 3 things automatically at a fixed time interval:

1) Refresh the data (copy newly inserted data from a datamart to a datawarehouse)
2) Run a program (exe) to produce some external reports
3) Refresh an OLAP Cube with the new data so that users can access it

Basically, using SQL Server Agent, setting up steps 1 and 2 was pretty straightforward (for step 1, the main point of entry is a single sp).

I ran into some trouble figuring the easiest way to complete step 3. I created a DTS package with one object that updates the Cude on my server. This seems to be working fine. Then, I created step 3 in Agent and chose 'CmdExec' as the type of command. I then referenced the external exe 'dtsrun' to execute my DTS package.

Everything seems fine but I'm not sure I used the "best" way of doing things. Since the SQL Server Agent and DTS manager are both SQL Server tools (so is the Analysis services), I'm wondering if there wasn't an easier (thus more robust) way of completing my 3rd step?

Anyone? How is this done where you've worked?

Thanks,

Skip.Well, that is the technique that MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_9vck.asp) suggests.

You could always make the DTS Package into a SQL Agent job (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_71v2.asp), then run the job using sp_start_job (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_sa-sz_11uq.asp) (but that is deviant).

-PatP|||As always I suggest this kBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to help DTS to schedule as a job.

DTS Import not working after SP3a upgrade

I have a DTS package that is importing a fixed length file.
After installing SP3a (on PERSONAL & DEVELOPER edition) it will not
allow me to assign more than 125 fixed length column positions.
After hitting the Finish & OK button it just wipes out the column
settings.

I've reinstalled both Personal & Developer edition. DTS will allow
more than 125 prior to the service pack.You may be seeing a side effect of installing SP3 and the problem seen by
others after aplying this SP is here. There is a fix

814113 - FIX DTS Designer May Generate an Access Violation After You
Install SQL Server 2000 SP3
http://support.microsoft.com/defaul...KB;EN-US;814113

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"shinobi332" <darinmiller@.earthlink.net> wrote in message
news:afdc1036.0406011922.2359a387@.posting.google.c om...
> I have a DTS package that is importing a fixed length file.
> After installing SP3a (on PERSONAL & DEVELOPER edition) it will not
> allow me to assign more than 125 fixed length column positions.
> After hitting the Finish & OK button it just wipes out the column
> settings.
> I've reinstalled both Personal & Developer edition. DTS will allow
> more than 125 prior to the service pack.|||That worked perfectly.
Thanks Allan!!!

"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message news:<40bd7127$0$4589$db0fefd9@.news.zen.co.uk>...
> You may be seeing a side effect of installing SP3 and the problem seen by
> others after aplying this SP is here. There is a fix
> 814113 - FIX DTS Designer May Generate an Access Violation After You
> Install SQL Server 2000 SP3
> http://support.microsoft.com/defaul...KB;EN-US;814113
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "shinobi332" <darinmiller@.earthlink.net> wrote in message
> news:afdc1036.0406011922.2359a387@.posting.google.c om...
> > I have a DTS package that is importing a fixed length file.
> > After installing SP3a (on PERSONAL & DEVELOPER edition) it will not
> > allow me to assign more than 125 fixed length column positions.
> > After hitting the Finish & OK button it just wipes out the column
> > settings.
> > I've reinstalled both Personal & Developer edition. DTS will allow
> > more than 125 prior to the service pack.

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
>