Copy rows from SQL to Excel

Question

Copy rows from SQL to Excel

Libby

Ten Centuries

Points: 1175
More actions
August 26, 2010 at 8:02 am

#141768

I'm very new to SSIS and trying to copy rows from a sql table to an Excel spreadsheet, but not having much luck getting any of the rows to copy. I have an OLE DB Source task setup with a SQL script and an Excel Destination task. I added a data flow task to connect them.
When I do a "Preview" on the OLE DB Source Editor Connection Manager, I'm getting 2 rows, so I know there is data from my SQL selection. I added a Data Viewer on the data flow path between my 2 tasks to see what I'm getting after the OLE DB Source task completes and see that there are no rows. Can someone PLEASE tell me what I'm doing wrong? I'm wondering if there's some setting on the OLE DB task because obviously that's where my problem is. I have searched the forum, but can't find anything on this.
I appreciate any help you can give me because I'm feeling totally frustrated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

Libby, you might find it easier to set up an Export-to-Excel package by using "Export Data" from SSMS, and then in that wizard tell it to save a package. When you look at the data flow task, you'll most likely see a data transformation task to convert columns to nvarchar (unicode), which is what the Excel drivers use. Anyway, using the wizard the first few times will get you familiar with the process a bit easier.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Libby Ten Centuries Points: 1175 More actions · Answer 2

Thanks for your reply Wayne. I've created DTS packages quite a bit in sql 2000, but SSIS and SSMS is new to me, so it took me a little while to figure out how to do this, but finally did. I created the SSIS package by rt-clicking on the database and selecting Tasks/Export data, which took me into the wizard. After running through each of the steps, I checked the boxes to run immediately and saved the package. I didn't know where to go to find the SSIS package in SSMS, but finaly found where I need to connect to Integration Services. I did that and selected the server to which the database is on. I'm still not able to find my package. I see a list of the following folders, but there's either nothing in them or when I try to expand the folder, I get a "Login Failed" error.

Running Packages

Stored Packages

Libby Ten Centuries Points: 1175 More actions · Answer 3

ooops hit post too soon...

Here's the remaining folder under Stored Packages. All of the MSDB folders give me the error.

File System (empty)

MSDB115

MSDB215

MSDB315

MSDB325

Do I need to have our DBA give me security access to view this information?

Thanks for your help.

Libby Ten Centuries Points: 1175 More actions · Answer 4

...Oh and by the way, when the package ran, it still gave me the same results... 0 rows exported.

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 5

Libby,

Why do you need a Script component for data transfer...

All you require is a OLEDB source and Excel destination as per the problem statement you described

Raunak J