Solved

ssis exporting to Excel

Posted on 2011-09-22
5
484 Views
Last Modified: 2013-11-18
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
Comment
Question by:cheryl9063
  • 3
5 Comments
 
LVL 5

Expert Comment

by:eridanix
ID: 36585646
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 36600724
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
 
LVL 1

Accepted Solution

by:
cheryl9063 earned 0 total points
ID: 36707707
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36707708
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
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 36898836
LIke I said resolved my own problem..
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 43
SQL server client app 3 36
Database Owner 3 21
T-SQL: problem comparing datetime 4 53
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question