What To Do When the Import and Export Wizard Fails - Part II Exports

Question

What To Do When the Import and Export Wizard Fails - Part II Exports

Yakov Shlafman

SSCommitted

Points: 1613
More actions
December 16, 2014 at 12:11 am

#299565

Comments posted to this topic are about the item What To Do When the Import and Export Wizard Fails - Part II Exports

Viewing 15 posts - 1 through 15 (of 16 total)

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

Robert.Sterbal SSCrazy Points: 2855 More actions · Answer 1

Could your data have fit into a Varchar(4000) column? Could you have then created a view and then just exported the view?

Yakov Shlafman SSCommitted Points: 1613 More actions · Answer 2

By definition an Excel column can have 250 characters. The trick is to put in an Excel column a string of any length. If you have a column with a length greater than 250 characters, you would have troubles to export it to an Excel file. Thanks

snelmica SSC Rookie Points: 44 More actions · Answer 3

That was an interesting article but there is another way to go I use - which doesn't involve SSIS (which I don't have).

Basically, you can make a refreshable query table in the excel workbook. That will allow you to import your data and refresh as often as needed for free.

* First, open and excel workbook and go to Data>"From other Sources"

* In the "Data Connection Wizard" enter your server name and log on credentials as needed and click Next.

*Select the database you need in the top drop down,

*Select the table or view you would like to connect to below that,

*Press finish.

You will then be asked where you would like to dump the data (select the cell you would like to have as the top-left corner of the returned record-set).

You can press enter at this point, and you now have a refreshable excel table that is populated from the table you have selected.

**Note: At this point, instead of pressing enter, you can click on Properties>Definition and you can change the "command type" from Table to SQL - that will let you paste a custom query right into the "command text". I've found that very helpful when I want to access a specific view with sorting.

You can also ask the workbook to Save the password (check the box) - this is handy when you want to distribute your creation around the office (if you are using SQL credentials)

Try it out - there is no problem with a maximum character count that I've run into -- I've never had a problem returning long strings from a varchar(max) or text type using this method. But the best part of this method is it's free and doesn't require you to have SSIS. 😀

Yakov Shlafman SSCommitted Points: 1613 More actions · Answer 4

Thanks for sharing. Is there a way to automate this process? The bottom line is the automation. When you have many reports to deliver you want to schedule jobs to do a report generation and delivery.

snelmica SSC Rookie Points: 44 More actions · Answer 5

If you were asking about my excel process, yes. Once you create the workbook you can set it to refresh upon opening. So, whenever the user opens the file it refreshes. There is no need for you to reproduce anything from the server side.

You can disable that option if needed (like if they plan to open it off-network and don't want to wait for the connection to time out). They can refresh any query on demand by right-clicking on the data table and selecting refresh - or if they need to refresh multiple queries in a workbook, the user can "refresh all" from the Data tab in the ribbon.

This method also plays well with pivot table reports based on the Sql tables. The pivot tables don't need to have any ranges updated, the will automatically look at the refreshed data(because the tables are created as named table objects). You can also set the pivot tables to refresh whenever the source data refreshes.

So, overall the excel workbook becomes a fairly user friendly report that is always current.

trimjib SSC Veteran Points: 234 More actions · Answer 6

I'm stunned by the amount of effort required to use SSIS to push data out. Underneath it is simply a connection string, a sql statement, a destination, and some amount of access security. If to a centralized destination, perhaps web-centric, a pull solution like snelmica recommends has a lot of appeal. Presumably the sql is a proc opening up all sorts of tailored-to-user role features, including whether report is ever even used. The game can be abstracted, as your mentor would do, to a system for creating destination files where most of the added value is the sql statement, not the SSIS. Manufacture an excel file or cut and paste into a killer excel template, then copy to your favorite central location - even Share Point, if you must.

Divine Flame SSCoach Points: 15943 More actions · Answer 7

Thanks for writing. Article was a bit long though. Actual information seems lost somewhere.

Sujeet Singh

Yakov Shlafman SSCommitted Points: 1613 More actions · Answer 8

Thanks for your input. What do you mean by saying Actual Information was lost? How do you like this saying: Make everything as simple as possible, but not simpler. Albert Einstein

Divine Flame SSCoach Points: 15943 More actions · Answer 9

Yakov Shlafman (12/18/2014)
Thanks for your input. What do you mean by saying Actual Information was lost? How do you like this saying: Make everything as simple as possible, but not simpler. Albert Einstein

1. I meant that article was very long.

2. You did highlight the "Main Issue" however we couldn't find " The Solution" heading where you explain the soution you used i.e. the solution to the problem has not been highlighted.

Don't get me wrong but it made us to read a lot more things like I clicked on this i clicked on that & searching for that solution to the problem was not easy (if we try to skip the common steps we are already familier with).

Sujeet Singh

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 10

Divine Flame (12/18/2014)
Yakov Shlafman (12/18/2014)
Thanks for your input. What do you mean by saying Actual Information was lost? How do you like this saying: Make everything as simple as possible, but not simpler. Albert Einstein
1. I meant that article was very long.
2. You did highlight the "Main Issue" however we couldn't find " The Solution" heading where you explain the soution you used i.e. the solution to the problem has not been highlighted.
Don't get me wrong but it made us to read a lot more things like I clicked on this i clicked on that & searching for that solution to the problem was not easy (if we try to skip the common steps we are already familier with).

I agree with that. It's not totally obvious to the reader that the solution was to put the three extended rows into the spreadsheet, in this case.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Yakov Shlafman SSCommitted Points: 1613 More actions · Answer 11

Thanks. I agree I communicated multiple things with an assumption that my reader is a beginner. How I could do it better?

latha1980in SSC Rookie Points: 36 More actions · Answer 12

I like the article as a beginner.But

I am confused ,why do we need to hide the first 3 columns in the destination excel file.

I was successful in exporting data from EmployeeHobby table to the destination excel file even with out hiding the columns.

I did not create the destination excel file , SSIS did create it on the fly,where I have given the path in Excel connection.

Let me know the reason to hide the columns in the destination excel.

Thanks in advance.

Yakov Shlafman SSCommitted Points: 1613 More actions · Answer 13

Thank you very much for your comments. I would like to answer your question ASAP but I would need you to provide a step by step how to do what you did. I need to be able to reproduce your case. I think that my way of doing a download of a big data type columns is the only way to do it. Please reply. Thanks

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 14

Yakov Shlafman (12/19/2014)
Thanks. I agree I communicated multiple things with an assumption that my reader is a beginner. How I could do it better?

Highlight the key to the process by saying something like "This is the key to this process and here's why". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)