Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2005: SSIS QUERY

Posted on 2011-09-06
9
Medium Priority
?
328 Views
Last Modified: 2013-11-10
HI ALL

i am currently developing my first package in ssis, one element of the package is a data flow task that uses an ole db source - which points towards an excel file - thinking being that it will pick up this file when its saved in the correct location process it accordingly and move the file when fisnished

this is all working perfectly in debug, until when the excel document isnt there - it then gives back an error!

how would i best achieve this process? eg i will want to check the folder daily and if its not there simply do nothing and return no errors..

cheers

mal
0
Comment
Question by:malraff
[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
  • 5
  • 3
9 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 36489087
Here is what  I use as the first step in my SSIS jobs that import files.  It checks to see if the file is there and hasn't already been processed.  As the final step, I update the table ImportLog with the finished time.


DECLARE @ImportName varchar(50)
SET NOCOUNT ON
SET @ImportName = 'DailyTotals'
DECLARE @FileInfo varchar(500)
DECLARE @CreateDate datetime
CREATE TABLE [dbo].[#tbl_temp_Timestamp](vchFileData varchar(2000))
INSERT INTO #tbl_temp_Timestamp
  EXEC [master].[dbo].xp_cmdshell 'dir \\server\share\filename.txt /T:W | FIND "filename"'

SELECT @FileInfo = vchFileData FROM #tbl_temp_Timestamp WHERE #tbl_temp_Timestamp.vchFileData IS NOT NULL
DROP TABLE [dbo].[#tbl_temp_Timestamp]
IF @FileInfo = 'File Not Found'
    BEGIN
      raiserror ('File not present',16,1)
      return
    END
SELECT @CreateDate = CAST(LEFT(@FileInfo,20) AS SMALLDATETIME)

IF (SELECT COUNT(*) FROM ImportLog WHERE ImportName = @ImportName AND FileDate = @CreateDate AND ImportComplete IS NOT NULL) > 0
    BEGIN
      raiserror ('File already processed',16,1)
      return
    END

INSERT INTO ImportLog (ImportName, FileDate, ImportStart) VALUES (@ImportName, @CreateDate, getdate())

0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 2000 total points
ID: 36489143
In the past, I've used a Foreach Loop Container to prevent the "File Not Found" type of errors.

If you haven't used a Foreach Loop Container before, simply drag the Task from the toolbar onto your SSIS Canvas.  You can then copy and paste the contents of your package into the Foreach Loop Container (make sure you have the task highlighted when you paste).

Last, you'll need to set some properties of the Foreach loop task.

1. Right Click on Foreach Loop task.
2. Select "Collection" on Left Hand Menu
3. Change Enumerator to "Foreach File Enumerator".
4. Set the "Folder:" property the target folder.
5. Set the "Files:" property to the name of the file the package will be looking for (you can use wildcards).

0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36489181
Some more additional explanation might be in order,

With the above logic, the ssis package will first execute the Foreach loop task.  If it finds a file that matches the criteria specified, it will execute the code inside the container.  If it doesn't, it will close without throwing an error.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:malraff
ID: 36489225
hi gohord

i tried the foreach loop container - but it still returns the exact same error when it does not exist? it works when the file is there
0
 

Author Comment

by:malraff
ID: 36489244
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36489263
hi malraff

Sorry never had that issue before.

Do you have all of your code inside the foreach loop container?

Are there any other files in the target folder?  If so, is the configuration information within the task specific enough to the files you're looking to process?

0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 2000 total points
ID: 36489270
This probably isn't the issue, but might be a good idea to set "DelayValidation" property of your Data Flow task to "True".  (Since the file isn't always going to be there)
0
 

Author Comment

by:malraff
ID: 36489646
brilliant gohord! i changed delay validation to true and viola - no errors!

this isnt sitting in a loop if the files are not there?


mal
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36489733
You won't have loop issues if there are no files in the specified folder in the foreach file task.  In fact, the foreach file loop won't allow the code to "kick off" if there are no files that match the criteria specified.

Glad I could help!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

715 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