locked
Importing rows from SQL Server to Excel 2007 using SSIS RRS feed

  • Question

  • Is there any white paper or document on

    Importing rows from SQL Server to Excel 2007 using SSIS

    Trying hard to configure this but not sucessful


    Smash126

    • Changed type ArthurZ Wednesday, August 8, 2012 2:14 PM Revised to reflect the intent
    Wednesday, August 8, 2012 7:44 AM

Answers

All replies

  • http://www.excel-sql-server.com/sql-server-export-to-excel-using-ssis.htm

    What are you struggling with?


    MCTS, MCITP - Please mark posts as answered where appropriate.

    Wednesday, August 8, 2012 7:48 AM
  • Hi,

    Have a look at steps provided below

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64585/

    If you have any issues in design mode set package to run in 32 bit mode

    http://dougbert.com/blog/post/debug-your-package-in-32-bit-or-64-bit-mode.aspx


    - Chintak (My Blog)

    Wednesday, August 8, 2012 7:52 AM
  • My System config if needed

    Database: SQL Server 2008

    OS:Windows 2008 Server

    Microsoft Office 2007

    64 bit M/c

    I have followed following steps .

    1. Added OLEDB Source to configure the Table Or View

    2.Then added the Excel destination and Selected Excel 2007

    when i check the package after running the package the excel file is in Microsoft Office Excel 97 - 2003

    i have gone couple of technical documents.

    I need to make changes in OLE DB Source 

    where i need to select provider as 'Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider' and select

    Extended Properties=Excel 12.0

    My Question,doubt and having problem is i am changing these details in source where a table or view is configured

    Confused alot


    Smash126

    Wednesday, August 8, 2012 8:28 AM
  • I followed the steps given by Kingxxx1.

     I get this error message when i try to create new table in excel destination

    'There is no sufficient information about mapping SSIS types to data types of the selected .NET data provider.
    As a result, you may need to modify the default column types of the SQL statement on the next screen. '


    Smash126

    Wednesday, August 8, 2012 12:21 PM
  • It's not a error .. its just information..

    If you are creating a new xlsx file means you have to define the structure of destination..

    Click ok on that message window and modify the Syntax which suits your requirement..The ExcelDestination in that create syntax will

    be your sheetname, change the sheetname and mention your destination column  with their datatypes accordingly..and click ok..


    • Edited by King kalyan Wednesday, August 8, 2012 12:30 PM
    Wednesday, August 8, 2012 12:29 PM
  • Thanks for the reply

    This what i get when i click New button.

    CREATE TABLE `Excel Destination` (
        `DepartmentID` SMALLINT,
        `Name` NVARCHAR(50),
        `GroupName` NVARCHAR(50),
        `ModifiedDate` DATETIME
    )

    and  i created the package using following table structure in excel and executed the package but when i check the excel.is there anything i am missing

    Excel cannot open the file h.xlsx becasue the file format or file extension format is in valid.Verify the file has been corrupted and the file extension matches the  format of the file


    Smash126


    • Edited by Smash126 Wednesday, August 8, 2012 1:28 PM
    Wednesday, August 8, 2012 1:26 PM
  • Any specific data types do i need to mention?

    Smash126

    Wednesday, August 8, 2012 1:41 PM
  • Hi Smash,

    Are you doing it on a existing file.. If so try creating a new xlsx  file.. 

    • Proposed as answer by Eileen Zhao Tuesday, August 14, 2012 9:03 AM
    Thursday, August 9, 2012 9:28 AM
  • hi  ,

    I Think  U can Find  the  Path where the  Excel has saved  ,

    Right click connection  manager of the Excel  and c the Properties  .

    check  it out u may  get ?

    Thursday, August 9, 2012 9:58 AM
  • I followed the steps given by Kingxxx1.

     I get this error message when i try to create new table in excel destination

    'There is no sufficient information about mapping SSIS types to data types of the selected .NET data provider.
    As a result, you may need to modify the default column types of the SQL statement on the next screen. '


    Smash126

    This error is because your sheet is empty and there are no columns defined in it. you have to write the names of the column in the first row the target sheet.

    Please mark the post as answered if it answers your question

    Thursday, August 9, 2012 9:58 AM
  • Hi Smash,

    I would suggest to start fresh, read through this blog and try to follow the steps:

    http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205

    Here are a couple of things i read through this thread which i think your are confused about or you should avoid for a while:

    1 - "xlsx" files are new versions of excel file where are "xls" file are old version of excel files. even though both the files are supported by ssis, i would suggest you to start with "xls" file as you can open in both old version of office and the newer versions.

    2 - as a start do not create a tables using SSIS editor instead, create a xls file yourself, delete all the sheets except the first one, define the column names on the top from left to right, add the records if you must, save and close the file.

    3 - once you are done with the basic functionality you can work a little higher changing the source file to "xlsx", creating the table within the xlsx file from the "SSIS editor" etc.

    4 - there are some limitations while importing data from an excel file such as the maximum length of a field value cannot be more than 255 characters (which can be tweeked), you cannot delete data from an excel file (unless using VBA objects) etc.

    Hope this helps-


    Please mark the post as answered if it answers your question

    Thursday, August 9, 2012 10:13 AM
  • Hey Kingxxx1 thanks for yours valuable inputs.It solved one part of problem.Is not possible to create dynamically??

    Smash126

    Thursday, August 9, 2012 11:06 AM
  • I am not sure , right now i am doing some work arround. if i found a solution definitly poat the solution..

    Hope this helps-


    Please mark the post as answered if it answers your question

    Friday, August 10, 2012 6:06 AM
  • Hi Smash126,

    Do you means to create excel file dynamically? Please correct me if my understanding is wrong.

    In that case, I suggest you assign a package variable to the Excel full path name, and then use the variable in the Excel ConnectionString. Please refer to the similar thread: http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e90215cd-7af5-4b4c-89f3-faf202446914#576af8fb-d0eb-416e-8ecc-931b3f7a55c5

    Please feel free to ask if you have any question.

    Thanks,
    Eileen

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Eileen Zhao Thursday, August 23, 2012 2:38 PM
    Wednesday, August 15, 2012 8:30 AM