0

I am a novice in C#. but I am working on a SSIS C# script which has to deal with list of files and rename sheets of each file. I have a list of excel files it can be .xls or .xlsx format. I am writing a c# script in SSIS script task to loop through the file names and sheet names and want to rename the sheet names with some hard coded values for example: a folder has file1.xls : sheet 1,sheet2, sheet3 ,file2.xls : sheet 1, sheet2 ,file3.xls : sheet1, sheet2, sheet3,sheet4

So, every time if a sheet name called sheet1 is available it should be renamed as ABC, sheet2 is available it should be DEF , if the one of the file has sheet3 missing it should be ignored. I have written a piece of code where I got stuck at fetching the sheet names. Please help me in fixing the code or suggest a better idea, if you have any.

Added these NameSpaces

using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

Added this in Main method

public void Main()
        {
            // TODO: Add your code here
            String FolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();
            String fileFullPath = "";
            var directory = new DirectoryInfo(FolderPath);
            FileInfo[] files = directory.GetFiles();
            foreach (FileInfo file in files)
            {
                fileFullPath = FolderPath + "\\" + file.Name;

                //Create Excel Connection
                string ConStr;
                string HDR;
                HDR = "YES";
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                string sheetname;
                sheetname = "";
                    
    



        #region ScriptResults declaration
                /// <summary>
                /// This enum provides a convenient shorthand within the scope of this class for setting the
                /// result of the script.
                /// 
                /// This code was generated automatically.
                /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }

Thank you very much in advance. Best Regards Raju

VRaju
  • 21
  • 1
  • 8
  • Are you able to install things on the SQL server? Because interacting with Excel files in your proposed manner will require the Primary Interop Assemblies for Excel Interop, which can be installed from a redistributable package. OLEDB is not capable of managing sheets like that. – romeozor Aug 31 '20 at 07:20
  • Hi @romeozor, I have added the reference microsoft.office.interop.excel in script task now to deal with excel. Also, would like to inform that there are around 10 different excel work books, each having 4-5 sheets. – VRaju Aug 31 '20 at 08:22
  • that's fine, but the SQL Server will need that reference to be installed through that redistributable package mentioned above, otherwise the task will fail to run. Can you confirm that you are able and permitted to install it, otherwise you'll need to find a different solution. – romeozor Aug 31 '20 at 11:32
  • Hi @romeozor ,No, I am not allowed to install. Is there any different approach you can suggest? – VRaju Aug 31 '20 at 12:44

0 Answers0