DSXchange

I am trying to read multiple files from a network location (not on the same server where the Datastage is installed). I am using sequential file stage:

File Pattern: //pd4p-ccbv-w01/d$/Cognos/Historical Volumes*.csv

Read Method: File Pattern

But it is returning 0 records.

I read in the forum that files should be on the same location as Datastage to read them using File Pattern method. But I couldn't find any solution to read them from the network location.

Is there any way to acheive this?

Does it allow space in the folder name?

Did you tried to view the data or ...?

chandu123 wrote:But it is returning 0 records.
Is there any way to acheive this?

When you execute the job in the director you can find the Datastage user name who triggered the job. The shared folder should have the access for this Datastage user.

That file path must be as it is used on the DataStage engine machine. The path must be mounted/shared for the DataStage process and of course the process needs read access as well.

Try logging into your engine machine with the DataStage user (probably "dsadm") and see if you can list the files in that path.

Dusting off some basics I learned about Windows networks, from a practical point of view.

Typically, a user's logon protocol will include a series of "net use" commands to establish a network link to remote servers (the one "local" server being your workstation's hard drive). This is (very) roughly equivalent to logging into a remote Unix server, most often lately using ssh. The "net use" commands will be accepted or rejected based on the user's authorized access to each server.

Our network security is perhaps more robust than most places, so grain of salt advice: if your process id (the DataStage appid or dsadm) has not been formally granted access to the remote server and the path to the files, you will not be able to access anything. Get a verbose set of log messages for your sequential file stage, and I will guess that the underlying failure is user authorization.

EDIT: Sorry, forgot to add that logging onto the remote server, or just trying to list files won't tell you anything unless you try the "net use" command first. It will tell you whether you have access to that server.

I am able to access single file using sequential stage but not multiple files using File Pattern method. This makes me feel like there is no issue with the access to the directory on the network.

chandu123 wrote://pd4p-ccbv-w01/d$/Cognos/Historical Volumes*.csv

May be the space issue in the file.

try like below

//pd4p-ccbv-w01/d$/Cognos/'Historical Volumes'*.csv

Or create few test files without space and try to read it.

A useful trick when pathnames have special characters, such as space, is to use the old "8+3" variant of the name.

Only if you're aware that they are still there under the fancy Windows 'long filenames' and how they work... tilde and all.

I tried using below formats and still didn't work.

//pd4p-ccbv-w01/d$/Cognos/Data Manager/Historical Volumes*.csv
'//pd4p-ccbv-w01/d$/Cognos/Data Manager/Historical Volumes'*.csv
'//pd4p-ccbv-w01/d$/Cognos/Data Manager/Historical Volumes*.csv'
"//pd4p-ccbv-w01/d$/Cognos/Data Manager/Historical Volumes*.csv"

And above combinations with backslash(\) in place of front slash.

Ray,

Could you please elaborate the meaning of "8+3" format of file path?

I also tried putting the file path in the parameter and using it which made no difference.

#FilePath#\Historical Volumes*.csv

chandu123 wrote:Could you please elaborate the meaning of "8+3" format of file path?

As suspected, a bit of arcane lore.

https://en.wikipedia.org/wiki/8.3_filename

The part you would be most interested in is the 4th section: How to convert a long filename to a short filename. This so you could see the short names hiding under the long names, this to avoid a pathname with spaces in it.

I'd also be curious if you tried the quotes SURA mentioned, that is a typical 'trick' for handling folder/file names with spaces in them in places that don't tolerate spaces well.

try like below

//pd4p-ccbv-w01/d$/Cognos/'Historical Volumes'*.csv

I forgot to mention that I tried Sura's suggested format of adding quotes and it didn't work.

Thanks for sharing me about 8.3 filename. I found though command line query that the short names for the files are something like this.

\\pd4p-ccbv-w01\d$\Cognos\DATAMA~1\SOURCE~1\FCUNIT~1\HISTOR~1.CSV
\\pd4p-ccbv-w01\d$\Cognos\DATAMA~1\SOURCE~1\FCUNIT~1\HISTOR~2.CSV
\\pd4p-ccbv-w01\d$\Cognos\DATAMA~1\SOURCE~1\FCUNIT~1\HI4B34~1.CSV
\\pd4p-ccbv-w01\d$\Cognos\DATAMA~1\SOURCE~1\FCUNIT~1\HI5B02~1.CSV
\\pd4p-ccbv-w01\d$\Cognos\DATAMA~1\SOURCE~1\FCUNIT~1\HI06DD~1.CSV

I tried giving below for file name:

HI*~?.CSV
HI~*.CSV
HISTOR~*.CSV

But resulted in 'Sequential_File_0: When checking operator: No files found for pattern.'

Any suggestions?

chandu123 wrote:\\pd4p-ccbv-w01\d$\Cognos\DATAMA~1\SOURCE~1\FCUNIT~1\*.CSV

Can't you try like above?

I tried the above one and got the same message. I wonder if there some issue with the Datastage itself when reading FilePattern from network location? Since I am able to read single file successfully but not FilePattern.