COGNOiSe.com - The IBM Cognos Community

ETL, App Development and other tools => COGNOS DecisionStream/Data Manager => Topic started by: SynexusStuart on 30 Nov 2010 11:43:19 AM

Title: Connect DM to static text file
Post by: SynexusStuart on 30 Nov 2010 11:43:19 AM
All,

I have a txt file which is updated every day and lives on our local Data Manager server.

Up to now, all our Connections are with databases and work fine. How do you connect Data Manager to a flat file source such as a text file?

I can do it using SSIS and then use DM to pick it up from there but ideally I'd like to reduce the process steps.

Any help would be appreciated.
Cheers
Stuart
Title: Re: Connect DM to static text file
Post by: MFGF on 01 Dec 2010 07:04:43 AM
Hi,

Data Manager has inbuilt capabilities to do this, so you don't need to use SSIS.

First, launch SQLText Designer, add a new definition file, then right-click this and import your text file.  This will launch a wizard to guide you through defining the name and structure of the file.  Save the definition when you are finished.

Next, add a new data source using the SQLText connection method, and point this at your definition file.

You can now use this as a data source in a build to bring in your text data - just code a SQL query to read it as normal.

Regards,

MF.
Title: Re: Connect DM to static text file
Post by: SynexusStuart on 13 Mar 2012 09:29:09 AM
hi MF, we've been using this solution for a long time now, thanks for your help with that.

We want to extend this to picking up a file on a shared data drive (not on local Data Manager machine). This works fine when running it manually but when scheduled, we get an error:

DM-STX-0007 No files were found for table 'Account' using 'T:\xxxx\Account.csv'

DM-DBM-0401 SQLTXT driver error on connection 'Account'

DM-DBM-0391 SQL statement in error:

Is there anything we can do other than saving the files locally?

Stuart
Title: Re: Connect DM to static text file
Post by: wyconian on 13 Mar 2012 10:07:41 AM
Hi

We have many many many (probably too mnay) external files sitting on a shared drive that  bring into DM so it is possible.  Check that the user stated in your connection (and/or the user stated in the scheduled job) has access to the shared drive/directory.  If the files are being updated do they always have exactly the same name?
Title: Re: Connect DM to static text file
Post by: SynexusStuart on 13 Mar 2012 11:58:11 AM
Found the problem. We were using a mapped drive location rather than the UNC Path.

Duh!