February 3, 2012 at 3:35 pm
Does any one out here know, how exactly to receving AS400 file from AS400 server.
Plz let me know, If is it possible to download a file from as400 to windows.
Thank you
February 6, 2012 at 12:35 am
What exactly do you mean with a file?
A database table, or an actual file, such as a flat file (.csv)?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 6, 2012 at 12:28 pm
Koen Verbeeck (2/6/2012)
What exactly do you mean with a file?A database table, or an actual file, such as a flat file (.csv)?
Looking for downloading a file (.XYZ extension) from AS400 server, right now.
I have no clue about table even...if u can explain, I would appreciate that ...Thank u
February 6, 2012 at 12:47 pm
quillis131 (2/6/2012)
Koen Verbeeck (2/6/2012)
What exactly do you mean with a file?A database table, or an actual file, such as a flat file (.csv)?
Looking for downloading a file (.XYZ extension) from AS400 server, right now.
I have no clue about table even...if u can explain, I would appreciate that ...Thank u
What does the file contain?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 6, 2012 at 1:23 pm
Phil Parkin (2/6/2012)
quillis131 (2/6/2012)
Koen Verbeeck (2/6/2012)
What exactly do you mean with a file?A database table, or an actual file, such as a flat file (.csv)?
Looking for downloading a file (.XYZ extension) from AS400 server, right now.
I have no clue about table even...if u can explain, I would appreciate that ...Thank u
What does the file contain?
Data like orders and stuff ..which is fixed width type. Does this matter really ?, as I have no idea regarding AS400 process. Thank you
February 6, 2012 at 2:48 pm
AS400 is a midrange operating system (think mainframe). You're not going to be able to hook up to it directly because it has a very different file structure (and they're usually locked down fairly tightly).
Your best bet will be to work with the AS400 admin to set up FTP ( or SFTP depending on what version they're on). They will give you FTP-style connection info (userID, PW, subdirectory if it's not the default): assuming it's NOT SFTP, you can connect in using the built-in Window FTP functionality.
If it IS SFTP, google for secure FTP clients and find one that works (SSH works pretty well).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 6, 2012 at 5:55 pm
Matt Miller (#4) (2/6/2012)
AS400 is a midrange operating system (think mainframe). You're not going to be able to hook up to it directly because it has a very different file structure (and they're usually locked down fairly tightly).Your best bet will be to work with the AS400 admin to set up FTP ( or SFTP depending on what version they're on). They will give you FTP-style connection info (userID, PW, subdirectory if it's not the default): assuming it's NOT SFTP, you can connect in using the built-in Window FTP functionality.
If it IS SFTP, google for secure FTP clients and find one that works (SSH works pretty well).
Your right with all stuff mentioned ...but my question is to download a file from as400 server ( assuming all rights were given, knowing file structure in as400 ) using FTP task in SSIS.
I am trying hard to find a way, if there is any chance that FTP task would work out, downloading a File. I am pretty sure sending file to AS400 server works, as I tested and was very successful. I want to find out, If I can do same with receiving a file. Just want to make things granted, before I really start looking for an alternative.
February 6, 2012 at 11:24 pm
quillis131 (2/6/2012)
Matt Miller (#4) (2/6/2012)
AS400 is a midrange operating system (think mainframe). You're not going to be able to hook up to it directly because it has a very different file structure (and they're usually locked down fairly tightly).Your best bet will be to work with the AS400 admin to set up FTP ( or SFTP depending on what version they're on). They will give you FTP-style connection info (userID, PW, subdirectory if it's not the default): assuming it's NOT SFTP, you can connect in using the built-in Window FTP functionality.
If it IS SFTP, google for secure FTP clients and find one that works (SSH works pretty well).
Your right with all stuff mentioned ...but my question is to download a file from as400 server ( assuming all rights were given, knowing file structure in as400 ) using FTP task in SSIS.
I am trying hard to find a way, if there is any chance that FTP task would work out, downloading a File. I am pretty sure sending file to AS400 server works, as I tested and was very successful. I want to find out, If I can do same with receiving a file. Just want to make things granted, before I really start looking for an alternative.
FTP works in both directions. If you can send a file, you can retrieve a file.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 7, 2012 at 6:07 am
I apologize for what might be dated info - I haven't worked on an AS/400 in a few years. I distinctly remember the default character set giving me heartburn. I think the exported flat file I originally got from the server had some oddball characters in it. I'm thinking UDF8 maybe? Anyway, I had to get the file exported into a format without the "extras".
Good luck and I wish I could add more detail but us old timers only remember useless classic rock trivia.
Cheers,
Ken
February 7, 2012 at 9:34 am
AS400 uses the word File what it is in fact a table.
I just transferred an AS400 File (Table) to SQL Serve using SSIS.
Is it a JD Edwards File(table)?
There is a great open source program called SQuirrel and you can use it to connect to AS400's and others.
You can write ANSI Standard SQL as opposed to an OPEN QUery Statement, etc.
If you have mnemonic table and column names and you don't know what they mean this program displays the extended properties and you can get the Friendly Table and Column Name, Indexes, etc
You can output the results to a SQL Table or a flat file if you like.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 7, 2012 at 9:43 am
Koen Verbeeck (2/6/2012)
quillis131 (2/6/2012)
Matt Miller (#4) (2/6/2012)
AS400 is a midrange operating system (think mainframe). You're not going to be able to hook up to it directly because it has a very different file structure (and they're usually locked down fairly tightly).Your best bet will be to work with the AS400 admin to set up FTP ( or SFTP depending on what version they're on). They will give you FTP-style connection info (userID, PW, subdirectory if it's not the default): assuming it's NOT SFTP, you can connect in using the built-in Window FTP functionality.
If it IS SFTP, google for secure FTP clients and find one that works (SSH works pretty well).
Your right with all stuff mentioned ...but my question is to download a file from as400 server ( assuming all rights were given, knowing file structure in as400 ) using FTP task in SSIS.
I am trying hard to find a way, if there is any chance that FTP task would work out, downloading a File. I am pretty sure sending file to AS400 server works, as I tested and was very successful. I want to find out, If I can do same with receiving a file. Just want to make things granted, before I really start looking for an alternative.
FTP works in both directions. If you can send a file, you can retrieve a file.
Thats what I believe in ...but it seams I am missing something or doing it in a wrong way. I guess, there is exact way of downloding things from as400 server...I am waiting on someone to come up with this steps...
February 7, 2012 at 10:34 am
Why not Create a DSN and link to the tables from Microsoft Access?
You will need to have the iSeries Client Access Components on your machine.
You will need the IP Address and the Database Name.
On the Sever and Packages Tab enter the Library.
Then you the DSN to Link to your AS400 Tables.
Keep in mind that if you have a 64 bit client you have to execute the 32 bit ODBC Manager.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 7, 2012 at 11:15 am
I'm assuming from the .XYZ file extension that the file is on the IFS file system not the native file system as there is no extension on the native.
To FTP a file from the system you can use something the following from a DOS command window The parts in bold are what you enter, italics are parts you need to specify for your environment. The rest is FTP prompts/responses:
C:\>ftp systemname
Connected to systemname.domain.lcl.
220-QTCP at systemname.domain.LCL.
220 Connection will close if idle more than 5 minutes.
User (systemname.domain.LCL:(none)): username
331 Enter password.
Password:password
230 username logged on.
ftp> NAMEFMT 1
ftp> cd /somedir/otherdir
250-NAMEFMT set to 1.
250 "somedir/otherdir" is current directory.
ftp> lcd c:\localfolder
Local directory now C:\localfolder.
ftp> get somfile.xyz
200 PORT subcommand request successful.
150 Retrieving file /somfile.xyz
226 File transfer completed successfully.
Quit
If this is something you do all the time the commands can be entered to an input file to the FTP command.
Depending on the source/destination file format required you may also need to enter the command bin before doing the get command.
The other option you have is to get the source folder in the IFS shared out to allow mapping a drive letter to the shared folder and using Explorer (or a batch file with map & copy commands) to retrieve the file.
Don Urquhart
February 7, 2012 at 11:55 am
quillis131 (2/6/2012)
Koen Verbeeck (2/6/2012)
What exactly do you mean with a file?A database table, or an actual file, such as a flat file (.csv)?
Looking for downloading a file (.XYZ extension) from AS400 server, right now.
I have no clue about table even...if u can explain, I would appreciate that ...Thank u
I misread your question.
How did you get a file on the AS400 with an XYZ extension.
You can scroll down on the AS400 (great) interface, Select a "T" for Text or "P" for pdf and the AS400 will send you an e-mail with the attached file.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 7, 2012 at 12:33 pm
Welsh Corgi (2/7/2012)
quillis131 (2/6/2012)
Koen Verbeeck (2/6/2012)
What exactly do you mean with a file?A database table, or an actual file, such as a flat file (.csv)?
Looking for downloading a file (.XYZ extension) from AS400 server, right now.
I have no clue about table even...if u can explain, I would appreciate that ...Thank u
I misread your question.
How did you get a file on the AS400 with an XYZ extension.
You can scroll down on the AS400 (great) interface, Select a "T" for Text or "P" for pdf and the AS400 will send you an e-mail with the attached file.
I have no idea, how and why this file, generated on AS400 ( as need not know this stuff, I believe ).
Can you plz be bit elaborate on the " You can scroll down on the AS400 (great) interface, Select a "T" for Text or "P" for pdf and the AS400 will send you an e-mail with the attached file. ".
I don't understand, what you want me to do with this ?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply