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

LVL 1
cheryl9063Asked:
Who is Participating?
 
cheryl9063Connect With a Mentor Author 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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.