Solved

BI Extract to Flat File

Posted on 2007-11-20
9
438 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 97
sql, case when & top 1 14 30
Requesting help with creating an SQL query with 2 tables 6 27
Are triggers slow? 7 14
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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…

820 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