Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

BI Extract to Flat File

Posted on 2007-11-20
9
Medium Priority
?
445 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

Author Comment

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

Accepted Solution

by:
nmcdermaid earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 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