Need help with Dynamic Excel File Name please.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

Need help with Dynamic Excel File Name please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-02-02 : 11:21:54

I am try to output an excel file with dynamic date. Here what I done.
1. Create Execute SQL Task Connect Type: Excel
2. Create Data Flow Task set to DelayValidation: True
3. Create OLE DB Sourc
4. Create Data Converstion
5. Excel Destination
6. Excel Connection, Expression, select ExcelFilePath
7. @[User::sXLFilePath] + @[User::sFileName] + RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", GETDATE()), 2)+ RIGHT("0" + (DT_WSTR, 2) DATEPART("MM", GETDATE()), 2) + RIGHT((DT_WSTR, 4) DATEPART("YYYY", GETDATE()), 2) +".csv"
8. C:\ExcelOutPut\SOX_CAM_SQL_Report_010215.xls

What I try to accomplish is output the file with each day append to it, date must be DDMMYY.
I google it and found many samples, tested it, and none of them is work for me. Any suggestions or some examples to share is greatly appreciate. I am new to SSIS. I found one poster have similar issue and inside the posted below, there was one suggestion to create variable and connection string but how do I bind that variable to Excel Connection manger.

Please help.

Thank you so much in advance.

Ex: SOX_CAM_SQL_Report _020215.csv
SOX_CAM_SQL_Report _030215.csv

--Similar issue:
https://social.msdn.microsoft.com/Forums/en-US/bda433aa-c8f8-47c9-9e56-efd20b8354ac/creating-a-dynamic-excel-file?forum=sqlintegrationservices

Suggestion in the above posted but where can bind this to Excel Connection Manger. Please help provide step by step. Thanks.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\temp\\" + "ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + ".xls" + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

And yes, as you were intimating, the delay validation on the dataflow should be set.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-02 : 13:27:06

At first glance it seems that you are on the right track.

Questions:

1. Do you want filetype to be csv or xls? If csv, just use flat file destination
2. Is your solution giving error messages (if so, post them)
3. If you're not getting errors, please post the filenames being generated by your expression.

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-02-02 : 17:17:47

I just want to output csv. The package did not give an error but when
I look at the excel file, nothing is output. Here is my steps:

Change my system date to 02/03/15, 02/04/15, ect...run the package, it ran fine but when I change my system date back to today and delete
all the package except the template than nothing ran.
btw, when I look at the Excel Connection manager, Edit and is point
to the file below. How can I force always to use the file name and append just date to it?. Thanks.

C:\ExcelOutPut\SOX_CAM_SQL_Report_020215.xls

What am I missing here?
How can I upload images to this my post?

quote:
Originally posted by gbritton

At first glance it seems that you are on the right track.

Questions:

1. Do you want filetype to be csv or xls? If csv, just use flat file destination
2. Is your solution giving error messages (if so, post them)
3. If you're not getting errors, please post the filenames being generated by your expression.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-02 : 21:19:08

if you want CSV use a flat file connector not excel

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-02-03 : 11:32:12

After I add drop table and create table, ran the package but nothing is ran and the execution result have nothing. What is wrong with stupid SSIS???
Please help.

Thanks.

I following this linked:

https://dwhanalytics.wordpress.com/2011/04/07/ssis-dynamically-generate-excel-tablesheet/

quote:
Originally posted by gbritton

if you want CSV use a flat file connector not excel

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 12:17:15

I suggest you work through the Stairway to SSIS: http://www.sqlservercentral.com/stairway/72494/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources