Home » SQL ServerRSS

if file exists

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.

 

 

20 Answers Found

 

Answer 1

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.

 

Answer 2

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.
 

Answer 3

You need to modify your package. By just the command  caling the package  will not do.
 

Answer 4

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.

 

Answer 5

add a script task and check the file. for checking the file   see this post
 

Answer 6

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
 

Answer 7

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.
 

Answer 8

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.publicvoid 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!PublicSub Main()
 If File.Exists(Dts.Connections("MyTEXTFILEConnection").ConnectionString) Then
  Dts.TaskResult = ScriptResults.Success
 Else
  Dts.TaskResult = ScriptResults.Failure
 EndIfEndSub
 

Answer 9

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

 

Answer 10

Can I replace

sFilename = DTSGlobalVariables.Parent.Connections("Text file  (Source)").DataSource

with

sFilename = "C:\inetpub\ftproot\INFILES\WEB-BOOK.165.csv"

And that will check for the existance of the above file?

 

Answer 11

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").

?

 

Answer 12

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

 

Answer 13

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

 

Answer 14

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

 

Answer 15

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?

 

Answer 16

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

 

 

Answer 17

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 BlogTalk to me now on
 

Answer 18

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? 

 

Answer 19

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.?

 

 

Answer 20

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....

 
 
Search for  if file exists
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter