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

  • Yakov Shlafman

    SSCommitted

    Points: 1613

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

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    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

    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

    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

    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

    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: 232

    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: 15941

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


    Sujeet Singh

  • Yakov Shlafman

    SSCommitted

    Points: 1613

    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: 15941

    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: 996863

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Yakov Shlafman

    SSCommitted

    Points: 1613

    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

    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

    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: 996863

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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