Solved

SSIS Run package with no error when file does not Exist

Posted on 2015-01-23
5
505 Views
Last Modified: 2016-02-15
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
Comment
Question by:SQLSearcher
  • 2
  • 2
5 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 40565986
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
 

Author Comment

by:SQLSearcher
ID: 40566080
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
 
LVL 14

Expert Comment

by:nishant joshi
ID: 40566095
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
 

Author Comment

by:SQLSearcher
ID: 40566110
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
 
LVL 14

Accepted Solution

by:
nishant joshi earned 500 total points
ID: 40566120
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question