Does File Exist Check in SSIS

Who is online?  0 guests and 0 members
Home  »  Blogs  »  DevinKnight  »  Does File Exist Check in SSIS
 
0
/5
Avg: 0/5: (0 votes)

Comments (31)

silverback
silverback said:
Devin - The graphics are not showing up on this page. Any way you can make these available again? Thanks. Larry.
12/29/2009
 · 
 
by
DevinKnight
DevinKnight said:
Sure no problem Larry. I think they didn't get carried over when the blogs were copied from our old site. I'll work on getting that fixed over the weekend.
12/29/2009
 · 
 
by
DavidStein
DavidStein said:

Any suggestions as what to do when you don't know the exact file name? For example if you were looking for files created by a SQL maintenance plan and they looked something like:

M2Mdata01_db_201010181416.BAK

What's the best way check existence for M2Mdata01*.bak ?

 

Also, how would one check to see if the date of the file was within a certain range?

10/18/2010
 · 
 
by
DevinKnight
DevinKnight said:

I know you can do wildcards like you've shown but i'm not sure how to check a date range in VB.  I'll do a little looking.

10/18/2010
 · 
 
by
sammy1188
sammy1188 said:

Hi,

I brought your book and think having a video is the best idea in terms of teaching. One comment thoough, you should really make the screen larger. Been pulling my hair out trying to see what it is youre typing.

But apart from that,,why am I getting different results to you when I done exactly the same thing? Everything is right except even when the file is not found, it goes to the scrip task that says found. Any ideas?

Sam

11/6/2010
 · 
 
by
DevinKnight
DevinKnight said:

That's strange Sam.  It's kind of hard to tell without seeing the package.  Feel free to send me screenshots of it.

11/6/2010
 · 
 
by
sammy1188
sammy1188 said:

Can I email it to you and if so, what's your email? Sorry, I am new to this site and also wasnt able to paste it here.

Just another question if you dont mind..do you know where I can download AdventureWorks2008?

Up until now, I've been using AdventureWorks in place of the 2008 version which worked fine if I changed the connection in your packagees but Chapter 19 I think refers to a column in a table which is not in the earlier edition.

I've been to numerous sites and have downloaded several packages but on each launch, it doesnt give me the option of installing AdventureWorks2008 (it has everything else-so I guess its not related to the setup on my pc).

 

thanks in adance Wink

11/6/2010
 · 
 
by
DevinKnight
DevinKnight said:

Sure go ahead and email it to me.  May take a couple days for me to review right now.  dknight@pramgaticworks.com

 

The AdventureWorks databases are on http://msftdbprodsamples.codeplex.com/.  You need to enable fulltext search on your db instance to get the AdventureWorks2008 database.

11/8/2010
 · 
 
by
sammy1188
sammy1188 said:

Hi Devin,

I just noticed you had another 24 hour trainer book ((MS Business Intelligence) and wanted to buy it straight away from Amazon (soft copy for iPad). Only question is where do I go afterwards for the video? (which is the main reason for me buying it)

Please let us know otherwise I'll need to buy the hard copy which I dont want to do because of cost and because I need to wait a couple of weeks (am in Australia)

 

thanks

Sam

12/11/2010
 · 
 
by
DevinKnight
DevinKnight said:

Good question Sam.  I'm not sure how the videos are provided when purchasing the soft copy.  I'll see if I can find out from the publisher.

12/13/2010
 · 
 
by
BI_SWFL
BI_SWFL said:

Devin, if you need to use Script Task in SSIS (irrespective of language - VB or C#), do we've to have Visual Studio installed on the server where you host the SSIS package (production box), for the script portion to work?

12/15/2010
 · 
 
by
DevinKnight
DevinKnight said:

You do not need Visual Studio installed on the server.

12/16/2010
 · 
 
by
BI_SWFL
BI_SWFL said:

Thank you Devin. One more thing, your steps above hold good if we're looking for a particular file name, right? I have a scenario where we need to scan a folder for multiple CSV files (different file names - but all are CSV files) coming in from a vendor. Sometimes a single file is dropped & sometimes four! Irrespective of the number of files present, the load needs to kick off. I tried your method, by adding a wildcard (*.csv) and seems SSIS won't accept it, and it treats it as if no file exists, and I get email saying "No Files to Process" from the load. Please advise.

12/16/2010
 · 
 
by
DevinKnight
DevinKnight said:

I found this example someone had written.  I think the variables names are a little different but uses the same idea.

 

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim di As DirectoryInfo = New DirectoryInfo("c:\")

Dim fi As FileInfo() = di.GetFiles("*.txt")

If fi.Length > 0 Then
Dts.Variables("User::FileExists").Value = True
Else
Dts.Variables("User::FileExists").Value = False
End If

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

12/16/2010
 · 
 
by
BI_SWFL
BI_SWFL said:

Thank you Devin. But, that requires installation of Imports System.IO anmespoace and all, right? Basically I did not want to use the "Script Task". One of the blogs recommended using the "For Each Loop" container instead. I was surprised - was easier than I thought, for my situation just used the For Each Loop container, an Execute SQL Task and two variables - mission accomplished! Ha...so it seems there're several ways/options to do a particular task in SSIS! 

12/17/2010
 · 
 
by
DevinKnight
DevinKnight said:

Great glad you figured it out!

12/17/2010
 · 
 
by
nbison
nbison said:

Hi Devin,

Great article, thank you very much :)

I have one question (and I am very new to SSIS), using your example, how can I check for multiple specific file names in the same directory?  

For example, we have a flat file directory that contains many multiple files with different names.  This particular package only needs to process/import File1.txt, File2.txt and File3.txt.

I'm guessing  I will need to create multiple variables, one for each respective file name.  But not sure how the code is written withen the script editor?

Many thanks in advance.

Nicklas 

1/27/2011
 · 
 
by
nbison
nbison said:

Hi Devin,

Great article, thank you very much :)

I have one question (and I am very new to SSIS), using your example, how can I check for multiple specific file names in the same directory?  

For example, we have a flat file directory that contains many multiple flat files with different names.  This particular package only needs to process/import File1.txt, File2.txt and File3.txt.

I'm guessing  I will need to create multiple variables, one for each respective file name.  But not sure how the code is written withen the script editor?

Many thanks in advance.

Nicklas 

1/27/2011
 · 
 
by
DevinKnight
DevinKnight said:

You can either place the task in a foreach loop to capture each file name or use ("*.txt") for the file name 

1/27/2011
 · 
 
by
Ashokkumar
Ashokkumar said:
Hi Devin, I have a directory from there we are accessing source files like CSV,EXCEL for ssis package.my question is if we dont have source files i want move to the else part in the package
9/8/2011
 · 
 
by
DevinKnight
DevinKnight said:
Are you asking if you don't have to file you want to move to another part of the package? That's exactly what this post walks you through doing.
9/8/2011
 · 
 
by
Ashokkumar
Ashokkumar said:
but in the above post it will work for a single file,in my case i have different sub folders containg EXCEL,CSV files.if the file is not there for current date then else part should run. Many thanks in Advance
9/12/2011
 · 
 
by
marydj
marydj said:
Thanks very much.
11/22/2011
 · 
 
by
stevedev
stevedev said:
Devin, in my situation my source file path is coming from the SourceConnectionFlatFile local connector, which is then redefined dynamically by the SSISConfigurations table in the db. How would I still employ an IF EXISTS task around the SSISConfigurations filter ?
7/16/2012
 · 
 
by
DevinKnight
DevinKnight said:
Sounds like you could just use T-SQL with an Execute SQL Task for that.
7/16/2012
 · 
 
by
stevedev
stevedev said:
doh! yes, solved. I used file exists in t-sql, and then executed package via “proc executing temp job” to avoid xp_cmdshell security concerns/block. CREATE PROCEDURE [dbo].[process_DataSync_IFFILEEXISTS_RunPKG] @Agency_id int AS BEGIN DECLARE @job NVARCHAR(100) ; DECLARE @pkgpath NVARCHAR(250); DECLARE @PKGCmnd NVARCHAR(MAX); DECLARE @Agency_id_Convert VARCHAR(10); SET @Agency_id_Convert = CONVERT(VARCHAR(10),@Agency_id) SET @pkgpath = ‘c:\databases\fbimport\’ + @Agency_id_Convert + ‘Import.dtsx’ –check if exists — if so run pkg as temp job DECLARE @csvfile NVARCHAR(150) DECLARE @file_exists_result int SELECT @csvfile = ConfiguredValue FROM dbo.SSISConfigurations WHERE ConfigurationFilter = ’2299′ exec master..xp_fileexist @csvfile, @file_exists_result OUT IF (@file_exists_result = 1) BEGIN SET @job = ‘xp_cmdshell replacement – ‘ + CONVERT(NVARCHAR, GETDATE(), 121) ; SET @PKGCmnd = ‘dtexec /f “‘ + @pkgpath + ‘”‘ EXEC msdb..sp_add_job @job_name = @job, @description = ‘Automated job to execute command shell script’, @owner_login_name = ‘sqlmx7user’, @delete_level = 1 ; EXEC msdb..sp_add_jobstep @job_name = @job, @step_id = 1, @step_name = ‘Command Shell Execution’, @subsystem = ‘CMDEXEC’, @command = @PKGCmnd, @on_success_action = 1 ; EXEC msdb..sp_add_jobserver @job_name = @job ; EXEC msdb..sp_start_job @job_name = @job ; END END
7/16/2012
 · 
 
by
mestophales
mestophales said:
Great Job - Thx for sharing
8/14/2012
 · 
 
by
mstephen
mstephen said:
what would be the equivalent in C#
3/4/2013
 · 
 
by
mstephen
mstephen said:
what would be the equivalent in C# for the original code by Devin above
3/4/2013
 · 
 
by
DevinKnight
DevinKnight said:
Here you go. Dts.Variables["boolFileFound"].Value = File.Exists(Dts.Variables["strFilePath"].Value.ToString());
3/5/2013
 · 
 
by
user209133
user209133 said:
Devin, thank you very much!
5/9/2013
 · 
 
by
user776405
user776405 said:
hi, I am not able edit script,i am getting error "Cannot show Visual Studio 2008 Tools for Applications Editor".Please help me...Thanks in Advance
6/5/2013
 · 
 
by
WilliamWilson
WilliamWilson said:
Devin, Thanks this was very helpful and easy to understand
10 days ago
 · 
 
by
user264208
user264208 said:
This topic really helpfull....
yesterday
 · 
 
by
Blogs RSS Feed

DevinKnight's latest blog posts

Blogs RSS Feed

Latest community blog posts