locked
if file exists RRS feed

  • Question

  • Hi All,

     

    I am trying to run an ssis package in a sql job.

    I want to run the job only if a file exists because if the package runs without the file the table is cleared but not reloaded.

    Is there something I can add to the command

     /SQL "\retailer" /SERVER DPSWIN  /CHECKPOINTING OFF /REPORTING E

    that will see if the file exists? And if so run the package if not move on.

     

    Monday, August 16, 2010 3:31 PM

Answers

  • You may also consider using a simple script task.

    Note your file connection's name in Connection Managers section to somewhere. And below code (written for SSIS S2008) will fail the task if the text file doesnt exists in the specified location. Connect the success arrow to your rest of your flow.

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.IO;
    
    //some code exists here
    
    //Change the MyTEXTFILEConnection with the name of your connection. i.e Text File Connection 1 etc.
    
    public void Main()
        {
          bool a = true;
          if (File.Exists(Dts.Connections["MyTEXTFILEConnection"].ConnectionString))
          {        
            Dts.TaskResult = (int)ScriptResults.Success;
          }
          else
          {
            Dts.TaskResult = (int)ScriptResults.Failure;
          }
        }
    

    Or alternatively here is the VB.NET Equivalent of this code:

     

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.IO
    
    'some Code here!
    
    Public Sub Main()
     If File.Exists(Dts.Connections("MyTEXTFILEConnection").ConnectionString) Then
      Dts.TaskResult = ScriptResults.Success
     Else
      Dts.TaskResult = ScriptResults.Failure
     End If
    End Sub
    

    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    • Marked as answer by DotNet Global Monday, August 16, 2010 10:14 PM
    Monday, August 16, 2010 4:09 PM

All replies

  • Why not put all your SSIS stuff in a For Each loop with VERY tight file masking, like right down to the actual file name?

    That way, the loop executes for every instance of that file (and there can be only a max of one) in the folder? If the file is not found (the mask is not satisfied) the steps INSIDE the loop don't execute. Such steps would include your TRUNCATE statement and any others.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, August 16, 2010 3:35 PM
  • You can include a Script Task to detect the file, or use a custom task like the File Properties Task to detect it in your package.
    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by Sudeep Raj Monday, August 16, 2010 3:56 PM
    Monday, August 16, 2010 3:45 PM
  • You need to modify your package. By just the command caling the package will not do.
    My Blog
    Monday, August 16, 2010 3:57 PM
  • I created the package using the import wizard and saving it.

     

    I am new to SSIS and can not seem to find a way to modify the packages in Management Studio.

    Monday, August 16, 2010 4:01 PM
  • add a script task and check the file. for checking the file see this post
    Let us TRY this |

    http://quest4gen.blogspot.com/
    Monday, August 16, 2010 4:04 PM
  • I created the package using the import wizard and saving it.

     

    I am new to SSIS and can not seem to find a way to modify the packages in Management Studio.

    you need to open your package in BIDS. then add the script task and check above link
    Let us TRY this |

    http://quest4gen.blogspot.com/
    Monday, August 16, 2010 4:06 PM
  • I suggest you use the Import/Export wizard as a way to jump-start your learning curve with SSIS. If you saved the package before running it in the wizard, then open Visual Studio (BIDS) and create a new SSIS Project. Then add the .dtsx package from where ever on your hard drive you saved it. From there you can open it and edit/change it.
    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, August 16, 2010 4:07 PM
  • You may also consider using a simple script task.

    Note your file connection's name in Connection Managers section to somewhere. And below code (written for SSIS S2008) will fail the task if the text file doesnt exists in the specified location. Connect the success arrow to your rest of your flow.

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.IO;
    
    //some code exists here
    
    //Change the MyTEXTFILEConnection with the name of your connection. i.e Text File Connection 1 etc.
    
    public void Main()
        {
          bool a = true;
          if (File.Exists(Dts.Connections["MyTEXTFILEConnection"].ConnectionString))
          {        
            Dts.TaskResult = (int)ScriptResults.Success;
          }
          else
          {
            Dts.TaskResult = (int)ScriptResults.Failure;
          }
        }
    

    Or alternatively here is the VB.NET Equivalent of this code:

     

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.IO
    
    'some Code here!
    
    Public Sub Main()
     If File.Exists(Dts.Connections("MyTEXTFILEConnection").ConnectionString) Then
      Dts.TaskResult = ScriptResults.Success
     Else
      Dts.TaskResult = ScriptResults.Failure
     End If
    End Sub
    

    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    • Marked as answer by DotNet Global Monday, August 16, 2010 10:14 PM
    Monday, August 16, 2010 4:09 PM
  • Ok,

    I can add a step in the job and move to the correct step on fail, bypassing the ssis package if the file is not there.

     

    I can use an active X script,  to see if it is there. Do you see a problem with this script?

     

    Option Explicit

    Function Main()
            Dim oFSO, sFileName

            ' Get the name of the file from the Connection "Text File (Source)"
            sFilename = DTSGlobalVariables.Parent.Connections("Text File (Source)").DataSource

            Set oFSO = CreateObject("Scripting.FileSystemObject")

            ' Check for file and return appropriate result
            If oFSO.FileExists(sFilename) Then
                    Main = DTSTaskExecResult_Success
            Else
                    Main = DTSTaskExecResult_Failure
            End If

            Set oFSO = Nothing
    End Function

    Monday, August 16, 2010 4:34 PM
  • I tried this:

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.IO

    'some Code here!

    Public Sub Main()
     If File.Exists(Dts.Connections("C:\inetpub\ftproot\INFILES\WEB-RETAILER.csv").ConnectionString) Then
      Dts.TaskResult = ScriptResults.Success
     Else
      Dts.TaskResult = ScriptResults.Failure
     End If
    End Sub

     

    But even thought the file is there it moves to the onFailure result.

     

     

    Monday, August 16, 2010 5:01 PM
  • I am assuming the Dts.Connections is the issue...I can't set it by pointing directly to the file eg. ("C:\inetpub\ftproot\INFILES\WEB-RETAILER.csv").

    ?

    Monday, August 16, 2010 5:19 PM
  • exactly. Dts.Connections(<index or name>) works with index or name of the connection. But if you want it to be hardcoded then do it like:

     If File.Exists("C:\inetpub\ftproot\INFILES\WEB-RETAILER.csv") Then
      Dts.TaskResult = ScriptResults.Success
     Else
      Dts.TaskResult = ScriptResults.Failure
     End If


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    Monday, August 16, 2010 5:37 PM
  • That is what I tried but it errored out

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.IO

    'some Code here!

    Public Sub Main()
    If File.Exists("C:\inetpub\ftproot\INFILES\WEB-RETAILER.csv") Then
      Dts.TaskResult = ScriptResults.Success
     Else
      Dts.TaskResult = ScriptResults.Failure
     End If
    End Sub

    Object required "File" Error on line 9

    This is a new step in the job running as a vbscript

    Monday, August 16, 2010 5:49 PM
  • Hi

    Code that i've written is a Script Task code not a vbscript code. Please ope nyour package, drag and drop a script task and paste the code partially inside the script task within Sub Main().

    1- Add only "Imports System.IO" at the top.
    2- Put the foolowing code block within Sub Main().... End sub

    If File.Exists("C:\inetpub\ftproot\INFILES\WEB-RETAILER.csv") Then
      Dts.TaskResult = ScriptResults.Success
     Else
      Dts.TaskResult = ScriptResults.Failure
     End If

    Regards

    Onur


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    Monday, August 16, 2010 6:30 PM
  • Guys:

    I've been watching this discussion all day and really think you are over complicating things. I submitted the original suggestion of using a For Each task and tightening down the file mask down to the individual file level. Everyone seemed to ignore that.

    There is no code to write, no need to worry about referencing Connection Managers, getting the right IMPORTS statement, no worries about VB.Net vs. C# vs. VBScript.

    And best of all, it does NOT fail the task/package if it does not find the file, something DotNet Global, a self-proclaimed novice may have trouble with.

    Why not?


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    • Proposed as answer by Todd McDermid Monday, August 16, 2010 6:55 PM
    Monday, August 16, 2010 6:44 PM
  • Hi Todd,

    Yes you are right too, but personally even i am mostly against scripting, i dont agree using a For each loop for this kind of solution as well. I usually dont prefer using the tasks in inappropriate manner. He could be a novice on SSIS, but again a novice can easily handle extending the SSIS by non-existent functionality. Besides the solution i've provided is only 5 lines of code, not a complex code so far. And i must confess that his nick has the impression on me that he has the knowledge of .NET but novice on SSIS.

    Anyway it is my personal decision, so i replied as a personal suggestion :)

    Regards

    Onur

     


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    Monday, August 16, 2010 7:17 PM
  • I agree with Todd - his code is evident that he may be somewhat comfortable with code - but you see he's chosen an ActiveX script task - not a VSTA script, and also doesn't understand variable access in a script.

    IMO, even if you have to push the normal boundaries a little, you should try to avoid scripting in SSIS.  It's more understandable to the next guy who has to support your packages.  And when the "next version" ships, you won't be able to update your architecture as easily, or benefit from feature improvements.

    Yes, as of today, you really can't do anything serious with SSIS without knowing a little .Net - because the Script ends up being used to fill gaps.  But that shouldn't be the case, IMO.


    Todd McDermid's Blog Talk to me now on
    Monday, August 16, 2010 7:24 PM
  • Oner:

    I agree that your five lines of code is quite simple. But there is still the fact that it might FAIL the task, and if the OP is not careful, he'll get a surprise error the first time he runs the package from SQL Agent and the file is not there. A failed Agent job step may keep other steps from going forward. (Personally, I don't like being told that something FAILED when the situation (the file is not found) is perfectly acceptable.)

    Maybe an addendum to the script would be to populate a Package Variable (maybe named User::FileExists) with "Yes" or "No" based on the results of the IF statement. 

    DotNet Global: Have you solved your original issue? Did you get the Script Task in SSIS to work? 


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, August 16, 2010 7:37 PM
  • Oner:

    I agree that your five lines of code is quite simple. But there is still the fact that it might FAIL the task, and if the OP is not careful, he'll get a surprise error the first time he runs the package from SQL Agent and the file is not there. A failed Agent job step may keep other steps from going forward. (Personally, I don't like being told that something FAILED when the situation (the file is not found) is perfectly acceptable.)

    Maybe an addendum to the script would be to populate a Package Variable (maybe named User::FileExists) with "Yes" or "No" based on the results of the IF statement. 

    DotNet Global: Have you solved your original issue? Did you get the Script Task in SSIS to work? 


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.


    Todd, i think we should evaluate everything under the code of business logic. That file could be waiting orders of retailers from a B2B portal which is going to be imported into ERP system or the warehouse system which is waiting for it to be committed to operate or ship the goods. (see the file name: C:\inetpub\ftproot\INFILES\WEB-RETAILER.csv)

    And if that file doesnt exists it could be a real problem. And within the for each loop you will not be able to determine if the file doesnt exist, cos nothing will happen in the loop if file doesnt exists. And if that doesnt exists i.e ETL process must be blocked and Admin must be warned maybe by an email immediately and a high importance. We can not skip the part of "file doesnt exists". There could also be another actions taken based on the non-existance of the file.

    But if it is a simple load and truncate issue, then you are right.

    So it is better to ask Dot Net Global for the business logic.?

     


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    Monday, August 16, 2010 8:09 PM
  • Thanks all for helping out, I eventually broke down and used VS to edit the ssis package directly and am working the bugs out of that now.

     

    Thanks again for the help and direction....

    Monday, August 16, 2010 10:16 PM