How to load three Different Excel files into different Tables

Question

I Have three Excel files which will update weekly with name including updated Date.xlsx. I need to load this three Excel files into my three tables using SSIS Package. I also need to make this automated as I have to schedule a job for that.

How can I automate the selection of Particular Excel and load to particular Table. e.g.

workanalysis_21032015.xlsx
analytics_21032015.xlsx
googleprobes_21032015.xlsx

I need this Excels data to load into three different Tables as these Excel updates with new name as (1.workanalysis_28032015.xlsx) in particular Folder for every week. I need to select workanalysis and dump the data into Table, same for remaining two.

score 1 · Answer 1 · answered Mar 27 '15 at 07:59

use a for each loop. the file name that is getting processed for that instance will/can be stored in a variable, take only the string part of the filename(u can use script task). Put the validations in the precedence constraints (eg @var = "workanalysis") then pass it to particular destination required.

score 0 · Accepted Answer · answered Mar 25 '15 at 10:09

If you are wondering how to do automated import (SSIS package) of Excel files to SQL server, here is a good step-by-step guide with screenshots: https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server---10-steps-to-follow/

You have to do it three times. Copy-paste is your firend :)

Regarding logic that takes account for date and folderstructure i suggest writing a C# script task. Here is a bad commented example (sorry, commets where in norwegian and I didn't care to translate so I removed them) of a script that finds the name of the newest xml-file that uses a very specific naming convention including datatime, much like yours on a ftpserver and then writes it to a SSIS variable named User:newFile. The xml-files have names: Something_YYYYMMDD.xml, hence the logic in teh fileIsNew function.

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

namespace ST_59d80f6857bc4a6197af798be478f308.csproj
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    #region VSTA generated code
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion
    public string[] GetDirectory()
    {
        StringBuilder result = new StringBuilder();

        System.Net.FtpWebRequest requestDir = (FtpWebRequest)WebRequest.Create("ftp://someftpserver.com");
        requestDir.Method = WebRequestMethods.Ftp.ListDirectory;

        requestDir.Credentials = new System.Net.NetworkCredential("User", "password");
        FtpWebResponse responseDir = (FtpWebResponse)requestDir.GetResponse();
        StreamReader readerDir = new StreamReader(responseDir.GetResponseStream());

        string line = readerDir.ReadLine();
        while (line != null)
        {
            result.Append(line);
            result.Append("\n");
            line = readerDir.ReadLine();
        }

        result.Remove(result.ToString().LastIndexOf('\n'), 1);
        responseDir.Close();
        return result.ToString().Split('\n');
    }

    public bool fileIsNew(string file, string newestFile)
    {
        if (file.EndsWith(".xml", System.StringComparison.CurrentCultureIgnoreCase) && file.Length >= 11)
        {
            decimal test;
            if(decimal.TryParse(file.Substring(file.Length - 12, 8), out test))
            {
                if (Convert.ToInt32(file.Substring(file.Length - 12, 8)) > Convert.ToInt32(newestFile.Substring(newestFile.Length - 12, 8)))
                {
                    return true;
                }
                return false;
            }
            return false;
        }
        return false;
    }

    public void Main()
    {
        string newestFile = "19900101.xml"; 

        foreach (string file in GetDirectory())
        {
            if (fileIsNew(file, newestFile))
            {
                newestFile = file;
                // TEST!!!!
                // MessageBox.Show(newestFile);
            }

        }
        Dts.Variables["User::newFile"].Value = newestFile;

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

I imagine you might have to do something similar :)

How to load three Different Excel files into different Tables

2 Answers2