Post reply

Check file exists before load into Database

  • May 22, 2007 at 11:51 pm

    #119087

    Hi All

    I want to oad data into specific table using SSIS package and running every day. Before load the file I have to check if current date file is exists in the destination folder.

    I am stucked on step2 check file (using xp_filexist) and passing the return value 0(if file not found) or 1 (if file found) to the next step and execute the further steps.

    These are steps....

    step1:

    Truncate the staging table

    step2:

    check the existence of requred file on the destination folder(as I used above pasted script).

    if file found then next is step 3 otherwise send file not found mail on step 4

    step3:

    load the file into staging table

    Step 4:

    Send warning using database mail to concern people

    Step5:

    move the data from staging table to production table

    Step6:

    send database mail for successful data load.

    Thanks in advance

    SqlIndia

  • May 23, 2007 at 1:13 pm

    #708420

    Use a script task to populate a boolean variable to determine if the file exists (in this example, using varFileName, varFileExists). Then use an expression (i.e. @[User::varFileExists]==True) on the precedence constraint prior to executing your ETL tasks.

    Tommy

    Imports

    System

    Imports

    System.Data

    Imports

    System.Math

    Imports

    System.IO

    Imports

    Microsoft.SqlServer.Dts.Runtime

    Public

    Class ScriptMain

    'Author: Tommy Bollhofer (freebsdboy@gmail.com)

    'Last Modified: 11/22/2006

    'Purpose: Determines if the file exists and sets the varFileExists boolean value.

    Public Sub Main()

    Dim fileLoc, fileName As String

    If Dts.Variables.Contains("User::varFileName") = True Then

    fileName =

    CStr(Dts.Variables.Item("User::varFileName").Value)

    'System.Windows.Forms.MessageBox.Show("FileDir:"fileName)

    If File.Exists(fileName) Then

    Dts.Variables.Item(

    "User::varFileExists").Value = True

    'System.Windows.Forms.MessageBox.Show("File Exists!")

    Else

    Dts.Variables.Item(

    "User::varFileExists").Value = False

    'System.Windows.Forms.MessageBox.Show("File Does Not Exist!")

    End If

    Dts.TaskResult = Dts.Results.Success

    Else

    Dts.TaskResult = Dts.Results.Failure

    End If

    End Sub

    End

    Class

    Tommy

    Follow @sqlscribe

  • May 24, 2007 at 1:06 am

    #708546

    Thanks Tommy...

    As I am totally new for using SSIS (or DTS)..plus using script in SSIS..so, could you plesae guide me how I am going to implement in SSIS.

    Thanks again

    SqlIndia

  • May 24, 2007 at 6:48 am

    #708605

    NP - e-mail me at freebsdboy@gmail.com and I can you send you an example.

    Thanks.

    Tommy

    Tommy

    Follow @sqlscribe

  • May 25, 2007 at 1:22 am

    #708874

    I have send the mail... Tommy

    SqlIndia

  • May 25, 2007 at 10:40 am

    #709033

    Just FYI, replied to your e-mail with an example package attached. Hope it helps.

    Tommy

    Follow @sqlscribe

Viewing 6 posts - 1 through 5 (of 5 total)

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