none
Rename Excel Sheet using SSIS RRS feed

  • Question

  • Hello All,

    I have an excel sheet which is stored in .xls format i.e 97-2003 document. Every month i get this excel workbook with some different sheet name which is difficult for me to run if i want to automate my package.There is only one worksheet inside that and i want to rename that sheet name to "Sheet1" and so i can automate my package.

    I have seen many blogs in the way how to work rename it. Many of them have given codes in C# and VB.Net but it didnt worked for me. Might be i didnt understood clearly. I have downloaded InteropAssembly.exe but it didnt work.

    

    So i want anyone of you guys to help me in giving the code from start to end.

    I am using SQL Server 2012.. Microsoft Office 2010 version. But the Excel workbook for this requirement is being saved as 97-2003 document.

    Thanks a lot..


    Thanks, Anji

    Monday, September 9, 2013 8:55 PM

Answers

All replies

  • Hello All,

    I have an excel sheet which is stored in .xls format i.e 97-2003 document. Every month i get this excel workbook with some different sheet name which is difficult for me to run if i want to automate my package.There is only one worksheet inside that and i want to rename that sheet name to "Sheet1" and so i can automate my package.

    I have seen many blogs in the way how to work rename it. Many of them have given codes in C# and VB.Net but it didnt worked for me. Might be i didnt understood clearly. I have downloaded InteropAssembly.exe but it didnt work.

    

    So i want anyone of you guys to help me in giving the code from start to end.

    I am using SQL Server 2012.. Microsoft Office 2010 version. But the Excel workbook for this requirement is being saved as 97-2003 document.

    Thanks a lot..


    Thanks, Anji

    you could write an Expression in the Excel Connection manager it self for renaming the File.

    For this you need two variables, File name and  Filepath, create these at Package Scope level and map the in the Excel Connection manager in the Expression box, you should be good to go.

    @[User::path] +
    (DT_STR,4,1252) DatePart("yyyy",getdate()) +
    Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
    Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".xls"


    Thanks, Please Help People When they need..!!! Mark as answered if your problem is solved.

    Monday, September 9, 2013 9:05 PM
  • Hello BKomm... Thanks for your reply..

    Actually i want to rename the sheet inside the excel workbook but not the Excel File name..


    Thanks, Anji

    Tuesday, September 10, 2013 1:37 PM
  • Use script task with the code sample in the below link,

    http://www.xtremedotnettalk.com/showthread.php?t=74551


    Regards, RSingh

    Tuesday, September 10, 2013 2:15 PM