[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ssis exporting to Excel

Posted on 2011-09-22
5
Medium Priority
?
489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

656 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