Solved

SSIS Run package with no error when file does not Exist

Posted on 2015-01-23
5
575 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 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