Solved

BI Extract to Flat File

Posted on 2007-11-20
9
436 Views
Last Modified: 2013-11-30
I am using the BI studio to export a data set to the network.  The resulting file is a txt file and I need the date contained in the file name.  What is the best approch to this.  Examples will be great.

When I push it to a hardcoded file like c:\temp\Myfile.txt it works fine.

Thanks in advance
0
Comment
Question by:rdray
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20322728
how do you create the flat file ? By SQL or by BI ?
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20322739
could try something like

DECLARE @filename AS varchar
SET @filename = '[dbo].[totals' + DATENAME(month, GETDATE())

then call @filename


i think that is right
0
 

Author Comment

by:rdray
ID: 20322771
BI,  I am using the Studio to extract the data and push it to the network.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20322803
... sorry, I'm just a database guy. In that case the BI tool needs to set the name for you I suppose. Can't see how SQL would get involved in naming the output from the BI tool.

But in general, I can advise to use the naming convention including YYYYMMDD part. That way, the files will get ordered chronological, might turn out to be handy !

Hope this helps ... a bit ...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:rdray
ID: 20322819
Thanks for the Input Yveau
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 20325096
I take you are using Integration Services?

To rename the output file you

1. Create a package variable
2. Update it to the name you want in code
3. Apply it to the output file name using 'property expressions'

To use property expressions, select your data flow task, then in the properyies window you'll see 'expresssions' . You can expand that an assign your varialble to your output filename.


here is some local help on property expressions, though its kinda complicated:

(type this into the help URL at the top of the help window)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a4bfc925-3ef6-431e-b1dd-7e0023d3a92d.htm
0
 

Author Comment

by:rdray
ID: 20365069
I finally got the package to work!  I know have trouble getting it to the server.. Question,  Do you have to have the management tools loaded to the server to install the package?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20372608
No. But usually you would have thise tools installed by anyway.

If you are saving the package to integration services, then the "integration services service" must be running.

If you are saving it to the file system, then you have to have access to the file system.


What problem are you having?
0
 

Author Comment

by:rdray
ID: 20372950
Thanks nmcdermaid,

Recap,

Create the package,
Build the Package,
navigate to  LeadsLabels.SSISDeploymentManifest and double click it.
point the wizard to the right server.
finish
create a new job in sql agent to run my package.

Worked.

Thanks
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now