Solved

ssis exporting to Excel

Posted on 2011-09-22
5
479 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
get most recent and second most recent date in SQL Server 24 77
sql query help 4 44
Strange msg in the SSMS pane 13 47
Upgrading SQL Server Management Tools 7 36
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now