January 4, 2011 at 9:52 am
Hi All,
I remember when we were using DTS, We were able to create excel file, though it is deleted using create table statement with excel connection manager.
I don't know the way of asking question and terminology I used is correct or not..But I want to create excel file if it is not available while running ssis package...
We are now using template file to replace if existing..As I don't think we have option to truncate the data..
Please suggest..
In DTS we were using
--- CONNECTION IS EXCEL FILE HERE
CREATE TABLE `NEW` (
`ACCT` BIGINT,
`ACCTNBR` VARCHAR(40),
`CRAT_DATE` VARCHAR(100)
)
🙂
January 5, 2011 at 3:12 am
When working with SSIS, forget everything you know about DTS. They are two different products.
If you want to write to an Excel file with the Excel Destination in the data flow, the Excel file must exist. If it doesn't exist yet when the package starts, you must create one with a script task.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2011 at 7:10 am
Also after creating the file, you can run your sql in a execute sql task when the connection is an excel connection to create the worksheet.
To create a excel connection click new connection... in the connection manager then Excel should be an option.
January 6, 2011 at 9:34 am
Hi,
Once I need to write to a new Excel file and what I did was the following:
-Create a Excel connection manager to a file, if you need you can change the location and name of the file dynamically.
-At the start of the process I delete the file
-Then I create the file using the "Execute SQL Task" with following code using the Excel connection on the "Connection" property of the component:
CREATE TABLE `ExcelDestination` (
`MatchType` NVARCHAR(28),
`Name` NVARCHAR(40),
`BankAccount` NVARCHAR(20),
`Value` NUMERIC (18,4),
`Contract` NVARCHAR(20),
`Date` DATETIME)
January 6, 2011 at 10:54 pm
"We are now using template file to replace if existing..As I don't think we have option to truncate the data.."
With SSIS and Excel as destination file, I found that you cant truncate the data in the Excel file.
What works for me is to create a templete file with the headings, formats and what ever you need.
Together with this create a destination file with same layouts etc as templete, this will be Excel connection as well in SSIS.
Then when running the package, I delete my destination file (as I only every need 1), copy the templete over the destination file and then populate the data into the file as per normal.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply