• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

SSIS Run package with no error when file does not Exist

Hello Experts Exchange
I have a SSIS Package that copies a Access database, copies data from a Access database and then archives the Access database.  When I try to test the package in BIDs I am unable to because it errors saying the file does not Exist.

How do I get the package to run when the file is not at the  source location?

Regards

SQLSearcher
0
SQLSearcher
Asked:
SQLSearcher
  • 2
  • 2
1 Solution
 
Barry CunneyCommented:
Hi SQLSearcher,
If in your final solution you want the SSIS flow to check if the file is at the source location or not and then have relevant logic for each case, you can use a Script Task and enter code which does the check for the source file.
so something like
Dts.Variables("fileExists") = File.Exists(CStr(Dts.Variables("filePath").Value))

pass in the file as a variable and have a variable which stores the result

In your package flow then have precedence constraints based on the fileExists variable

If you just want it to ignore looking for the source file while testing an option is to temporarily disable the task which copies the source file
0
 
SQLSearcherAuthor Commented:
Hello Barry
I don't want to check that the file exists, I want to disable the file check that is already part of the SourceConnectionOLEDB, so when the package starts and there is no file there, it will continue to the copy file task.

Regards

SQLSearcher
0
 
nishant joshiTechnology Development ConsultantCommented:
SQLSercher,You are facing validation issue while connection is validation file.You need to enable delay validation (Data flow property for source you are referring).

As you have already copy task,it will not validate if file exist as delay validation marked as true.

Hope,This will solve your problem.
0
 
SQLSearcherAuthor Commented:
Hello nishant
When I set DelayValidation to True I get the following message when I start the package in BIDs.

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [Source - Data_Bank [161]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error at Data Flow Task 1 [SSIS.Pipeline]: Source - Data_Bank failed validation and returned error code 0xC020801C.

Error at Data Flow Task 1 [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

Error at Jig Tool and Die Database [Connection manager "SourceConnectionOLEDB"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft Access Database Engine"  Hresult: 0x80004005  Description: "Could not find file 'F:\Jig Tool and Die\Database\Jtd_web.mdb'.".

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

Open in new window


How do I resolve?

Regards

SQLSearcher
0
 
nishant joshiTechnology Development ConsultantCommented:
Try to set property delay validation for access connection only as you are using other task in data flow.It will not work for you.


Conclusion is :
Make data flow delay validation property as "false".
Access connection manager delay validation property as "true".

Regards,
Nishant
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now