Parse MS Excel files dynamically with SSIS
<p>I have a business requirement that is looking for the ability to have Excel files placed in a network location and the data from these files uploaded to a SQL Server database. The files will contain 1 worksheet of data. The files correspond to a table found within a known database. The files can and will correlate to multiple tables and will be known only when opening up the file, i.e., the name of the worksheet. I'm currently creating multiple SSIS packages for each of these files as they are uploaded to the shared drive but sometimes, I'm not creating the package fast enough.</p>

<p>I guess my question is, is this type of dynamic parsing something that SSIS can accomplish from a Script Task within a Foreach container? or should I look into another option?</p>

<p>So far, I have the following...but as I've researched, I've come across post similar to this: <a href="https://stackoverflow.com/questions/29311309/extracting-excel-files-with-ssis">Extracting excel files with SSIS</a> and that is making me slightly concerned regarding the feasiability...</p>

<pre><code>public void Main()
{
        // TODO: Add your code here

        Dts.TaskResult = (int)ScriptResults.Success;

        string NetworkLocation;

        //Create database connection
        SqlConnection myADONETConnection = new SqlConnection();
        myADONETConnection = (SqlConnection)(Dts.Connections["db"].AcquireConnection(Dts.Transaction) as SqlConnection);

        //Obtain the location of the file(s)
        NetworkLocation = (string)Dts.Variables["User::NetworkLocation"].Value;

        string[] dirs = Directory.GetFiles(NetworkLocation, "*.csv");


    }
</code></pre>

<p>So, any thoughts or ideas or what direction I should look into?</p>