Can it Be Done: Querying Mainframe Flat Files Using SQL Server Tools

[DanEvans]

I would like to retrieve data from mainframe (AS/400) flat files using SQL Server tools, either by querying or linking directly to the file (my first choice), or by FTPing datasets using a DTS package or stored procedure to a local drive and importing the data from there.

I tried the FTP Task available for DTS Packages. Problem is, it defaults to my user id as the root directory (xevans...) when I need it to access another directory (xcacs...) and there is no way to change this that I can tell.

Can anyone help me with this?

 

[sguslan]

Microsoft HIS2000(formerly SNA Server) Host Integration Server may help in this regard.

http://www.microsoft.com/sna/productinfo/features_glance.asp

http://www.microsoft.com/sna

Merant offers many products also:

http://www.merant.com/products/microfocus/mainframe_access/factsheet.asp

 

[AshleyRickards]

Would SNA work for connecting to an Alpha Digital Unix box and tap into the flat files? Ashley L Rickards
SQL DBA

 

[AshleyRickards]

Would SNA work for connecting to an Alpha Digital Unix database and tap into the flat files? Ashley L Rickards
SQL DBA

 

[chiph]

I doubt SNA would help you connect to your Alpha Digital Unix box, as SNA is an IBM proprietary network protocol.

You probably need one of the Unix integration packages (there are several companies who sell these, but I can't remember their names right now).

Chip H.

 

[Guest_imported]

Do you have a way of changing the default user id / directory that you are using on the server? If you can then use the one for AS400 (xacs...)

 

[DanEvans]

Unfortunately, I only have one login for the mainframe, and FTP object in the DTS package uses your login ID as the root directory without apparently giving you the option of changing the root. Does anyone have a good sample of vb code or a stored procedure that can FTP various files from a remote FTP address to a local drive?