none
SSIS issue with dynamic excel sheet name

    Question

  • Hi Team,

    I am creating a Excel file dynamically using a script, excute sql and then the dataflow.

    I am not able to cretae a excel sheet name without an underscore

    exisitng file: "team data" even though am giving the table name in excute sql task(which is excel sheet name) as "team data" it is creating the name as "team_data" because of which the package is failing?

    I am able to achieve this with an underscore, as it is a client requirement to generate wihtout an underscore am posting this question.

    Is this an issue with Sql server 2005?

    Thanks,

    Eshwar

     

    Thursday, September 16, 2010 8:39 AM

All replies

  • What is the code you are using the create the excel file ? Is it a .NET assembly ? Can you bit elobrate more on how you create the excel file ?
    Thursday, September 16, 2010 10:46 AM
  • you can use OpenRowset property to set the excel sheet name.
    Sudeep's Domain   Tweet me..
    Thursday, September 16, 2010 10:53 AM
  • its already set still not working...

     

    Thanks,

    Eshwar

    Thursday, September 16, 2010 11:05 AM
  • is it giving any error?

    while setting the sheet name try using square baces or quotes as the sheet name are 2 words.


    Sudeep's Domain   Tweet me..
    Thursday, September 16, 2010 11:07 AM
  • This is what the create script that i have used in the excute sql task

    CREATE TABLE `Termination Date` (
    `Company Code` NVARCHAR(10),
    `SSN` NVARCHAR(20),
    `Subscriber Name` NVARCHAR(255),
    `Group Number` NVARCHAR(255) )

    if you see here i have given a space between the two words because of this its getting failed.

    Regards,

    Ram.

    Thursday, September 16, 2010 11:16 AM
  • CREATE TABLE [Termination Date] (
    [Company Code] NVARCHAR(10),
    [SSN] NVARCHAR(20),
    [Subscriber Name] NVARCHAR(255),
    [Group Number] NVARCHAR(255) )

    Try using the resolution operator "[" around the names with spaces. Let us know if this fixes the issue.

     

    Thursday, September 16, 2010 11:47 AM
  • Thanks Sorna but no luck

    its still creating the excel sheet as termination_date not as "termination date"

    any other workaround?

    Regards,

    Eshwar.

    Thursday, September 16, 2010 12:12 PM
  •  how you are generating the excel file and assigning a name to it ?

    Thursday, September 16, 2010 12:23 PM
  • CREATE TABLE [Termination Date] (
    [Company Code] NVARCHAR(10),
    [SSN] NVARCHAR(20),
    [Subscriber Name] NVARCHAR(255),
    [Group Number] NVARCHAR(255) )

    Try using the resolution operator "[" around the names with spaces. Let us know if this fixes the issue.

     


    Try this:

    CREATE TABLE [Termination#Date] (
    [Company Code] NVARCHAR(10),
    [SSN] NVARCHAR(20),
    [Subscriber Name] NVARCHAR(255),
    [Group Number] NVARCHAR(255) )


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Thursday, September 16, 2010 1:11 PM
  • Thanks CozyRoc..

    No it didn't worked..

    Regards,

    Eshwar

    Friday, September 17, 2010 4:50 AM
  • Hi All

    Any update on this?

    I too need to have WorkSheet name with Space. I am trying to create a dynamic excel. I tried all over internet and i havnt found any answer to this yet.

     

    I am using BIDS 2008. I found that this can be done using third party tools. I cannot use any third party tool. On the server that we have, Microsoft Office is not installed. Is there any way i can get this done?

    Thanks,

    Piyush


    Friday, March 18, 2011 12:49 PM