Solved

SSIS Run package with no error when file does not Exist

Posted on 2015-01-23
5
361 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now