DSXchange

Hello,
I have a DS job in which i use Sequential file stage to produce Excel as Output.Here i use Tab as the text delimiter.
Now i have a requirement where depending upon a value say Instance name(column in source) in the Source file i have to write the data on new sheets.i.e i have sorted data Instance wise and i want the Ouput of all instance in a single Excel file but 1 sheet should contain the data only about 1 instance only.
Is it possible to insert new sheets in excel from DS Jobs while it is executing?I cannot use ODBC Stage because i dont have the rights to create a DSN.
Any help regarding this would be great.

Unless you can use ODBC you're screwed. It's the only way you can access worksheets in an Excel workbook. And even then they're system tables. Get someone to create the DSN for you.

ray.wurlod wrote:you're screwed

I would not want to go against that kind of statement from Ray.

I work in UNIX and darn glad of it, so I ain't no Windows Guroo with fancy pants MS certifications but isn't there a way to have a master Excel spreadsheet with pre-defined worksheets that point to other *.csvs?. In other words, the Master .xls has n number of tabs which are merely references to the actual .csvs files? DS could write the .csvs and the Master would be refreshed upon opening. Just an idle thought unhampered by any actual knowledge or experience in the matter.

trobinson wrote:Just an idle thought unhampered by any actual knowledge or experience in the matter.

Need more of that.

ray.wurlod wrote:Unless you can use ODBC you're screwed. It's the only way you can access worksheets in an Excel workbook. And even then they're system tables. Get someone to create the DSN for you. ...

Thanks for the help Ray,but the problem is even if i get someone to create a DSN it doesnt Appear in the DropDown of the ODBC Stage,Hence the Problem persists..I was thinking about sending some keystrokes to excel (like shift+F11 or alt+I+W) to insert new sheets when required,but could not achieve it...I was hoping somebody has a workaround to this..

Just type it in - you are not forced to use the drop-down. From what I recall, it won't show up on that list until you import metadata using it.

Craig remembers correctly. Importing a table definition establishes the DSN in a file called uvodbc.config in the project directory on the DataStage server machine. The DSN drop down list is populated from this file.

If you don't want to import the table definitions - in which case you are not following DataStage best practice - then you could manually edit the uvodbc.config file.

This query is really a good one
Did any one got some work around ..if so plz let us know


Thanks
Hameed

You don't need a workaround - we have given the solution

chulett wrote:Just type it in - you are not forced to use the drop-down. From what I recall, it won't show up on that list until you import metadata using it.

I tried typing in the DSN Name but it doesnt work because i guess the DS Server engine and the DSN needs to be on the same machine.My Current Environment is as follows ,i connect to a remote server on which i can create a DSN and i use DSDesigner on that machine to connect to a DS Server which is located on some other server,hence the DSN is not working i guess.Factoring in this constraint is there any solution to this ?
I really appreciate the time and thought given to this query by all the members.

No.

The DataStage job - or metadata importer - is the client, the ODBC driver is the protocol, and the database server is the server.

Therefore the ODBC driver (and driver manager) must be on the same machine as the DataStage server.

parag.s.27 wrote:I tried typing in the DSN Name but it doesnt work because i guess the DS Server engine and the DSN needs to be on the same machine.

Of course. Get the DSN created on the Server machine and give it another shot.

chulett wrote:Of course. Get the DSN created on the Server machine and give it another shot.

but craig the issue is that we do not have access at all to the server machine.None of the team members have access to the server.So i guess in that case the requirement will not be achievable with this constraint.
Thanks Everyone for the thought put in to resolve this requirement.

I'm not suggest you have to do it. 'Get' as in 'make arrangements with whomever manages the server' to have the DSN created.