November 17, 2008 at 8:11 am
I have a SSIS package which I want to export data on a daily basis; it writes to excel file once but then will error if I run it again. Please see attachment.
Have built it using these components:
Data Flow Task:
Data Reader Source: - Sql query is here.
Excel Destination:
Data Source
Other points to note is that I’m using an expression like this for the file name:
"C:\\ example1\\example1" + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"
+ (DT_WSTR,4)YEAR(GETDATE())
+".xls"
any Ideas
November 17, 2008 at 8:27 am
Lee Hemmings (11/17/2008)
I have a SSIS package which I want to export data on a daily basis; it writes to excel file once but then will error if I run it again. Please see attachment.Have built it using these components:
Data Flow Task:
Data Reader Source: - Sql query is here.
Excel Destination:
Data Source
Other points to note is that I’m using an expression like this for the file name:
"C:\\ example1\\example1" + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"
+ (DT_WSTR,4)YEAR(GETDATE())
+".xls"
any Ideas
I don't know much about SSIS package, but it seems that there is a problem with replacing files. Have you thought about adding also the time to the filename? That way it won't happen that easily that you need to replace a file as a result from this package. If it's the case that the error is that the next day it gives a problem, I think we need more information about the system you use, etc.
Greetings,
Ronald
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
November 17, 2008 at 8:38 am
http://www.mydatabasesupport.com/forums/ms-sqlserver/357322-excel-template-required-export-ssis.html
This guy has more or less the same issue i'm having. I will look at what his done.
November 17, 2008 at 9:10 am
You must create a template excel file with the columns that you are planning to use, this will help you when you try to map the columns on the destination file.
1) Add an SQL Task before the Data Flow Task
2) Change the Connection Type to Excel
3) Add the SQLStatement to generate the table
example:
CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER,
`RevisionNumber` SMALLINT
)
This statement will create a Worksheet with the name 'Excel Destination' on your Excel destination file.
If you want to create this statement form a wizard you can do it using the excel destination.
1)Make a right click over the excel destination
2)Select edit
3)When you see the label "Name of excel sheet" click over the New Button
4)Now you could see the statement in a new window.
November 17, 2008 at 9:38 am
Whoop Whoop !!!!!
Worked Perfectly !!!!
Thank you very much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply