February 2, 2015 at 9:37 am
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:
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.
February 2, 2015 at 10:05 am
I know that you are new here, so I'll try not to be too hard on you :-), but ...
Saying that a proposed solution 'does not work' is not very helpful in terms of hoping that others will be able to help you resolve the problem. Instead, please post the text of error messages in future.
If I were doing this, I would keep the name of the output file the same in the data flow tasks and instead add a final task – either a File System Task or a Script Task – to rename the file and (optionally) copy it to another place.
Doing this allows you to break your problem into smaller 'chunks' ... get the file export working, then get the rename working.
February 2, 2015 at 10:13 am
Yes, I have try to use the File system task to copy the file to other folder c:\OutputClient\....xls but how can I append the format date like this.
Date must be in this format: DDMMYY append to the output file.
SYSEng_Sox_Cam_SQL_Report_020215.xls
Thank you.
February 2, 2015 at 10:29 am
nguyenl71 39244 (2/2/2015)
Yes, I have try to use the File system task to copy the file to other folder c:\OutputClient\....xls but how can I append the format date like this.Date must be in this format: DDMMYY append to the output file.
SYSEng_Sox_Cam_SQL_Report_020215.xls
Thank you.
There are probably better ways to do it than this, but maybe this gives you something to work with:
"SYSEng_Sox_Cam_SQL_Report" + "_" + right("0" + (DT_STR, 2, 1252) DAY( getdate() ),2) + right("0" + (DT_STR, 2, 1252) MONTH( getdate() ),2) + right( (DT_STR, 4, 1252) YEAR ( getdate() ),2)
February 3, 2015 at 9:35 am
After I add 2 tasks following this linked, ran the package but nothing return and no errors. No data in excel as well. What's is wrong with SSIS?
I even disable 2 Execute Tasks which drop table and create table again but nothing no results. Drop the Excel connection Manger, recreate again still same problem.
Any help is appreciate.
-- this link show how to drop a table and re-create it.
https://dwhanalytics.wordpress.com/2011/04/07/ssis-dynamically-generate-excel-tablesheet/
February 3, 2015 at 10:16 am
nguyenl71 39244 (2/3/2015)
After I add 2 tasks following this linked, ran the package but nothing return and no errors. No data in excel as well. What's is wrong with SSIS?I even disable 2 Execute Tasks which drop table and create table again but nothing no results. Drop the Excel connection Manger, recreate again still same problem.
Any help is appreciate.
-- this link show how to drop a table and re-create it.
https://dwhanalytics.wordpress.com/2011/04/07/ssis-dynamically-generate-excel-tablesheet/
'Still the same problem'? Which one is that, exactly?
I can assure you that SSIS works – lots of people do lots of very powerful things with it.
Sounds like you are trying to make things work by changing everything at the same time. This approach is doomed to failure. I recommend that you attack this problem in stages, testing each stage as you go along and building on top of what you know works.
Otherwise you end up with a big solution containing one or more issues and you have no idea where to look to fix them.
February 3, 2015 at 10:57 am
Ok, stupid question, why are you trying to use an excel connection manager to create a file you want to give the csv extension to?
Do you want a csv or an xls?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply