10

I have SSIS Package which Exports Data from table to Excel file

Control Flow :-

Control Flow

Data Flow :-

enter image description here

This is My Step :-

  1. Drop Excel Table
  2. Create Excel Table with format as of my Select Query which i used to retrieve data from database
  3. Insert Data from Database to Excel file

I Used Query Like Select * From Table Where --Some Condition

I retrieve 3000 rows out of 10000 rows and put that 3000 rows in my excel sheet. But when open my excel sheet i saw scrollbar which goes till 10000th row and ends hence my excel sheet size also increses . how can i reduce my excel sheet size ? my excel sheet contains only 3000 rows then why blank cells which goes till 10000th row ?

SQL Server 2008 & Visual Studio 2008 with BIDS

Improve this question
11
  • 2
    What is your reasoning for only retrieving 3000 rows? Would it make sense to have the SQL query only return 3000 rows instead of limiting it in SSIS? Also, is this for Excel 97-2003 (xls) or 2007+ (xlsx)? Commented Jan 4, 2013 at 7:37
  • Its Excel 97-2003 File Format. My OLE DB Source is retrieving 3000 Rows & Put it in Excel File.....then why my Excel File Scrollbar goes till 10000th row ? Thus increases Excel file size. Commented Jan 4, 2013 at 8:20
  • So, your Excel file must already exist because you have to connect to it. That being the case, how many (blank) records does it have already? In other words - if you open the file prior to running your SSIS package, does it scroll down to the 10,000 row? Commented Jan 4, 2013 at 15:09
  • have look at diagram..i m writing query on Excel Sheet 1. Drop Table And Then Create Table on Excel Sheet which creates first row of header only.. Commented Jan 4, 2013 at 15:12
  • Can you post the query inside of the 'Create Excel Table' task? Commented Jan 4, 2013 at 15:14

2 Answers 2

Reset to default
24
+25

I believe your issue is around the method in which you are using to create the file. You have two alternatives and both should fix your issue:

Solution #1:

You can create an Excel file with those predefined columns, essentially your empty output file - this would act as your 'Template File'. Your flow would then be this:

  1. File System Task - Copy template file to output or working directory (rename if necessary)
  2. OLEDB Source Task - Query your source for the data (3000)
  3. Data Conversion Task
  4. Excel Destination Task - Put data into new Excel file

Note: You already have steps 2 thru 3 complete, you just need to make sure you are connecting to the new Excel file. Also, to clarify, step 1 is outside the Control Flow Task.

This way is helpful because you always have a blank and consistently formatted Excel file to copy and work with.

Solution #2:

The other option is to use a Script Task and create the Excel file - you could also load the data into the file in this task. This requires some basic understanding of VB.NET or C#. Basically you would need to get a XLS library (like NPOI). This is more complicated, but gives you the best functionality.

I recommend you try solution #1 and see how that works for you.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I'm also using option 1, but am running into an error. After the File-Copy task (step 1 in option 1 above) my package is failing at Step 4, with an error that the table (excel sheet) does not exist. Even though it does. Anyone come across this issue? I confirmed that the template file is properly copied and exists. Also the excel file connection in #4 is a variable containing the newly copied excel file's file name.
2

Drop table SheetName doesn't delete the sheet instead it just deletes the row . If for the 1st time you have loaded 10K rows and then again executed the package by restricting the number of rows to 3K ,the excel file will still contain those 10K empty rows as it retains the sheet along with the empty spaces .

You can use script task to delete the sheet using COM obects .But for that you need to place the Excel PIA(Primary Interop Assemply) to make it visible for VSA or else create a new excel file every time the package runs

Else as suggested by Nicarus use File System Task to delete the existing file and create a new Excel file on every execution .

Diagram :

enter image description here

File System Task :

enter image description here

Use the same components and the query for Create Table using Execute SQL task and your DFT

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.