BI Extract to Flat File

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
rdrayAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nmcdermaidConnect With a Mentor Commented:
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
 
YveauCommented:
how do you create the flat file ? By SQL or by BI ?
0
 
James MurrellProduct SpecialistCommented:
could try something like

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

then call @filename


i think that is right
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rdrayAuthor Commented:
BI,  I am using the Studio to extract the data and push it to the network.
0
 
YveauCommented:
... 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
 
rdrayAuthor Commented:
Thanks for the Input Yveau
0
 
rdrayAuthor Commented:
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
 
nmcdermaidCommented:
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
 
rdrayAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.