[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

ssis exporting to Excel

I have a ssis package that exports data to an excel spreadhseet from a query. I'm using Visual Studio 2008. The first component is a execute SQL task editor that runs the code below(step1) This is supposed to delete all the data on that excel sheet called data.. Step 2) is another sql task editor that recreates the header names back on the Excel sheet called data.. Step3) populates the table from a data flow task.. Basically from an oracle query to the excel destination Editor..

THE PROBLEM... Step 1 does not delete the rows.. This works in other packages that were created at this co by someone who no longer works here.. Step 1 is only deleting the first row.. I dont understand why it's not deleting all the rows.. PLease tell me how to do this..not just "use a script task etc.." I need a real example..
step1)drop table `data` GO

step2)CREATE TABLE `Data` (
`ORG` VarChar(3),
`TO_SUBINV` VarChar(10),
`PO_NO` VarChar(10),
`PO_LN` VarChar(10),
`ITEM` VarChar(40),
`SUPPLY_TYPE` VarChar(40),
`CREATION_DATE` DateTime,
`OTW_DATE` DateTime,
`EXP_DATE` DateTime,
`CONTAINER` VarChar(30),
`RECEIPT` VarChar(30),
`STATUS` VarChar(30),
`UOM` VarChar(25),
`OTW` Single,
`UNIT_PRICE` Single,
`TOT_AMT` Single,
`QUOTA_CATEGORY` VarChar(150),
`CHARGE_ACCOUNT` VarChar(150))
GO

Open in new window

0
cheryl9063
Asked:
cheryl9063
  • 3
1 Solution
 
eridanixCommented:
Hi,

maybe this should help:

1) you can use TRUNCATE TABLE 'data' in Step1
2) you don't need Step2, becouse table 'data'is clear, but not deleted
0
 
cheryl9063Author Commented:
Found my own solution.. In order for my process to work as is you have to allow SSIS to create the worksheet first(you can not create it yourself)..Once SSIS creates the worksheet initially(basically running step 2 above) then you can go back and save formatting on the sheet and rerun the package from beginning to end.. Not sure why this works but now does every time.
0
 
cheryl9063Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for cheryl9063's comment http:/Q_27323016.html#36600724

for the following reason:

I just did
0
 
Alpesh PatelAssistant ConsultantCommented:
Better to create new file each time using the dataflow task and use excel connection to do that.

All are the standard component so increase permoramnce.
0
 
cheryl9063Author Commented:
LIke I said resolved my own problem..
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now