Solved

BI Extract to Flat File

Posted on 2007-11-20
9
434 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 37
Concatenating multiple comments into one row 16 46
SQL Server memory Issue 7 76
SQL Inner Join Vs SubQueries 9 26
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

746 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

11 Experts available now in Live!

Get 1:1 Help Now