Post reply

Export to Excel

  • October 23, 2008 at 2:09 am

    #128080

    Hello.

    I've just started using SQL Server 2005 and SSIS, in particular. As an ad-hoc task, I needed to export the contents of a SQL table into an Excel workbook. Simple I thought but apparently not!

    Using Enterprise Manager, I used to be able to right-click the table and select Export Data. A DTS session was opened and I simple chose the source and a Excel destination pretty much, that was it. It took 30 secs.

    However, in SSIS, I'm struggling. I've created my source data adapter and the Excel destination adapter but the issue seems to be around data type conversion. I have some varchar fields in my SQL table and the adapter seems to try and do an implicit conversion to NVARCHAR and fails. Now, I could use a data conversion task but I've got 140 fields to convert.

    This was such a trivial task in DTS, why does it seem so convoluted in SSIS? Or am I missing something?!

    All help gratefully received!

    J

  • October 23, 2008 at 8:15 am

    #888623

    Hi

    I'm relatively new to SSIS as well and I have to agree that DTS was a lot quicker and easier.

    I would however suggest that you use the SSIS Import and Export Wizard to do this. It creates all the tasks for you and the steps are basically the same as the Import and Export Wizard in SQL 2000.

  • October 27, 2008 at 3:41 am

    #889879

    Brilliant. Thanks for that. The Import/Export wizard is exactly what I was looking for.

    J

  • October 27, 2008 at 3:45 pm

    #890303

    I am having trouble with Excel exports as well. I want to be able to run the same export more than once without intervention and I want each run to clear the worksheet from the previous run(s). I used the Import/Export Wizard and it set everything up for me but when I test the package, I get the following error:

    Error: 0xC002F210 at Clear Worksheet Task, Execute SQL Task: Executing the query "DELETE FROM `RepList`

    " failed with the following error: "Deleting data in a linked table is not supported by this ISAM.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Task failed: Clear Worksheet Task

    I've tried adjusting the ResultSet property settings but no luck. Anyone know how to fix this to make it work for me?

    Thanks!

    mj

    ~mj

Viewing 4 posts - 1 through 3 (of 3 total)

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