Hello All,

I have a script task which will basically pick up the latest file from the folder and then pass it to the "Data Flow Task" where it will be loaded into the SQL Server, this process runs fine on visual studio however it fails on SQL agent because of the missing drivers. The DBA team is working on it however i was wondering if i can convert my picked up latest excel file into a CSV ? Because the csv automation on Agent runs fine.

Below is the code i have currently in Script task, Can someone please help me with this?

region Namespaces

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

endregion

namespace ST_9742f4376d5c4b3da8923e028d63a7a8
{

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// TODO: Add your code here
var directory = new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());

        FileInfo[] files = directory.GetFiles("*LC_-_State*");
        DateTime lastModified = DateTime.MinValue;

        foreach (FileInfo file in files)
        {
            if (file.LastWriteTime > lastModified)
            {
                lastModified = file.LastWriteTime;
                Dts.Variables["User::VarFileName"].Value = file.ToString();
            }
        }

        //MessageBox.Show(Dts.Variables["User::VarFileName"].Value.ToString());

        Dts.TaskResult = (int)ScriptResults.Success;
    }

    #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

}

}

Hi @sujith kumar matharasi ,

Have you installed the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing?

You may install the provider for a try.

https://datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Also here are two articles about converting-excel-files-into-csv-and-uploading-files-using-ssis & ssis-how-to-convert-excel-file-to-csv.html.

Just for your reference.

Regards,

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Hi @sujith kumar matharasi ,

It is better to stay with the existing DFT to process MS Excel file.

Though here is how to retrieve a latest fully qualified file name in the directory based on its datetime attribute. There is no need in any loop, like your current implementation is doing.

c#

void Main()  
{  
 var directory = new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());  
 FileInfo file = directory.GetFiles("*LC_-_State*")  
 .OrderByDescending(d => d.LastWriteTime)  
 .FirstOrDefault();  
  
 Dts.Variables["User::VarFileName"].Value = file.FullName;  
}  

Hi @Yitzhak Khabinsky ,

My only concern is i cannot automate the excel file through SQL agent so i need to convert it to csv. My current code already picks up the latest excel file its just i need to add something in here which will convert that picked up file to csv.

Thanks

You have to be able to open and read the Excel file - and that requires the ACE drivers or ExcelDataReader or some other component to be installed on the server. It would be easier to just wait for the DBA's to get the ACE 64 and 32 bit drivers installed on the server.

You can install either one first with no problems - but installing the other version requires you to run that install from the command line using /quiet or /passive (depends on version being installed). So - if you install x64 first (which on a server is the one you really need), then you install the x86 version from the command line.