Post reply

Data import when a file is dropped to a folder

  • May 30, 2014 at 8:23 am

    #307782

    I have a file (csv) that will be dropped to a drop folder that I would like to import my data into an existing database. No biggie - question is -now that file will be dropped to this folder at any given time instead of a timed interval as before, and they would like me to import it as they are dropped (immediately). All I can think of is scheduling (Sql job) this every 1 or 2 minutes to run if a file exists, what would be the best way to do this?

    Thanks in advance.

  • May 30, 2014 at 9:07 am

    #1717826

    Please have a look on below articles from Orlando.

    Using the Konesans File Watcher Task in SSIS to Process Data Files[/url]

    Using the WMI Event Watcher Task in SSIS to Process Data Files[/url]

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
     🙂

  • May 30, 2014 at 9:43 am

    #1717851

    Using a script task inside a loop you could check if the file exists and end the loop when the file is found to continue with the package execution.

    I used this to check if the file exists:

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic 2008.

    ' The ScriptMain is the entry point class of the script.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports System.IO ' This is important

    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

    <System.CLSCompliantAttribute(False)> _

    Partial Public Class ScriptMain

    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults

    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    End Enum

    Public Sub Main()

    Dts.Variables("bolFileExists").Value = File.Exists(Dts.Variables("FileName").Value)

    Dts.TaskResult = ScriptResults.Success

    End Sub

    End Class

    If you add a conditional sleep (System.Threading.Thread.Sleep(60000)) you can make it run every minute or more. The value is in milliseconds.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply