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 Execution Order

I have a package that has 12 data pump tasks all executing in parallel.

It is transferring raw data from an AS400 DW to a MSSQLSvr Staging area.

Each pump task on completion assigns values to a set of global variables, then having done this passes these as parameters to a sproc which inserts them into a table.

This seems to work for 4 or 5 of the pump tasks but, the rest of the rows in the table are all the same because the remaining pump tasks are all executing before the sprocs.

Is there a way to make sure that the entire set of job steps completes, before starting another job set of steps while still keeping them running in parallel.

I had wondered if there was a way to use the PumpComplete phase of each pump step to fire off the sproc, but can't see how you execute the step.

Any ideas would be much appreciated.Darren Green suggested
Why not take a simper approach and only populate your progress list as
tasks start executing. You could drive this quite happily off events.

To determine order of execution you would need to enumerate all steps as
constraints are held by the task they go to, not from.

For each step, enumerate the PrecedenceConstraints collection, to get
the PrecedenceConstraint objects. The StepName is the preceding step, So
if a step as no PrecedenceConstraints it is the start step. Not sure
that this guaranteed to 100 accurate either as in theory you can change
the basis and result to in effect be a "On Preceeding Step Not Run", and
have a circular reference, but I suspect DTS itself may have the same
problem as you in this case, so probably not worth worrying about for
the start step, but perfectly valid elsewhere.

DTS Package - AS400 data load to SQL Server

I'm trying to convert numeric dates on the AS400 to smalldatetime in
SQL. I'm able to customize to match format but when I save the
tranformation I get the following error. I don't know why they sould
let you customize the field if you cant convert. Any ideas?
"Error Description: TranformationDateTimeString('X') column pair 1
(source column 'Y'(DBTYPE_NUMERIC), destination column 'Z'
(DBTYPE_DBTIMESTAMP)): Cannot convert source datatype to datetime
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!If this is an import, try dumping it to a temp table then let the MS drivers
do the job natively. What drivers are you using for this? client access?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

dts only selected rows

I have to dts rows by timestamp. For example if my dts downloaded at 10 am then in the next run i want to grab rows updated in the AS400 after 10am. what is the best way to go in ssis?

thanks...

kushpaw

You'll have to store the timestamp somewhere - perhaps in a file or a table, and then use a dynamic SQL statement using that timestamp.

-Jamie

|||

Perhaps, consider to use some sort of execution/audit tables and have your packages to get data extraction timeframesfrom there each time .

Rafael Salas

|||

There is a timestamp field in both sql and as400 tables. I can select the last timestamp from the destination table and query source for that timestamp. Is that what u mean?

-kushpaw

|||

kushpaw wrote:

There is a timestamp field in both sql and as400 tables. I can select the last timestamp from the destination table and query source for that timestamp. Is that what u mean?

-kushpaw

Yeah something like that. That would work.

|||thanks for your suggestion Rafael. this would be an ssis way, just what i was looking for.

DTS Jobs Troubles

I'm trying to run DTS packages (import AS400 information) on a job and it fails, but when I program a DTS package job that imports from SQL Server to SQL Server it does not fail.
What can I do?
I hope your answer
Thanks
Cristopher SerratoCheck your connectin string to AS400.
is it a DB2 server ?
Do you have DB2 Connectivity kit installed ?

Originally posted by cjserrato
I'm trying to run DTS packages (import AS400 information) on a job and it fails, but when I program a DTS package job that imports from SQL Server to SQL Server it does not fail.

What can I do?

I hope your answer

Thanks

Cristopher Serrato|||Originally posted by Ben Mansouri
Check your connectin string to AS400.
is it a DB2 server ?
Do you have DB2 Connectivity kit installed ?

I CHECKED MY CONECTION AND IT'S GOOD, EVEN IF I PLAY THE DTS PACKAGE, IT WORKS BUT WHEN I PUT IT INTO A JOB IT FAILS.

I DOWNLOADED THE SERVICE PACK 3A, BUT IT DOES NOT WORK YET.

WHAT COULD IT BE SO?|||Take help from this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) to schedule a DTS package.|||Is there any transformation envolved on DTS ?

Originally posted by Satya
Take help from this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) to schedule a DTS package.|||Originally posted by Ben Mansouri
Is there any transformation envolved on DTS ?

No, the only one that I got is

AS400 -> SQL SERVER 2000