asked on
Check If File Exists In DTS
I have an SSIS package; the same logic could be used for a DTS package that I need to see if a CSV file is on the SQL server. Then based on if the file is there on the server (the same server that is running the query):
If the file is there, then I need the SSIS package to go to the next step.
If the file is not there, go to a different step.
I do not want to place the next step in the same step that looks for the file. Thanks!
If the file is there, then I need the SSIS package to go to the next step.
If the file is not there, go to a different step.
I do not want to place the next step in the same step that looks for the file. Thanks!
Microsoft SQL ServerMicrosoft SQL Server 2005
Last Comment
jjrr007
ASKER
Thanks. Using the first example in the link provided. How/where do I specify the path? Will this work with SSIS- SSIS doesn't allow some active X controls.
the filename is obtained in the line from the code below:
sFilename = DTSGlobalVariables.Parent.
it is getting the filename from whatever text source file you have already created in your dts package. in this case the code is looking for a task called "Text File (Source)". replace that with whatever you have called your text source task in your package.
the code resides in a simple active x script task should be available in SSIS.
sFilename = DTSGlobalVariables.Parent.
it is getting the filename from whatever text source file you have already created in your dts package. in this case the code is looking for a task called "Text File (Source)". replace that with whatever you have called your text source task in your package.
the code resides in a simple active x script task should be available in SSIS.
or you can use the extended stored proc xp_fileexists (but you need permissions to use xp_cmdshell)
you can change the eg below to return a value indicating failure etc:
declare @var varchar(500)
set @var = 'c:\myfiles\whatevertextfi
DECLARE @return INT
EXEC master..xp_fileexist @var, @return OUTPUT
IF @return = 1
BEGIN
select 'File does not exist'
END
ELSE
BEGIN
select 'File exists'
END
you can change the eg below to return a value indicating failure etc:
declare @var varchar(500)
set @var = 'c:\myfiles\whatevertextfi
DECLARE @return INT
EXEC master..xp_fileexist @var, @return OUTPUT
IF @return = 1
BEGIN
select 'File does not exist'
END
ELSE
BEGIN
select 'File exists'
END
ASKER
I prefer the last option you suggested. Active X is a bit difficult for me to configure in 2005.
How do say if the files is there: return that the step is sucessful -else show it as a failure?
I really need an answer quickly. I appreciate your time!
How do say if the files is there: return that the step is sucessful -else show it as a failure?
I really need an answer quickly. I appreciate your time!
just run these statements in a sql task in your package. use your workflow properties to control what you want to do next in your package. so if this task executes successfully go to whatever step.
when this task fails, go to the other step
declare @var varchar(500)
set @var = 'c:\myfiles\whatevertextfi
DECLARE @return INT
EXEC master..xp_fileexist @var, @return OUTPUT
IF @return = 1
BEGIN
select 1/0 --file does not exist, deliberate divide by zero failure
END
ELSE
BEGIN
select 'File exists' --task will just execute succesfully
END
when this task fails, go to the other step
declare @var varchar(500)
set @var = 'c:\myfiles\whatevertextfi
DECLARE @return INT
EXEC master..xp_fileexist @var, @return OUTPUT
IF @return = 1
BEGIN
select 1/0 --file does not exist, deliberate divide by zero failure
END
ELSE
BEGIN
select 'File exists' --task will just execute succesfully
END
ASKER
frankytee,
This is a clever idea. I will try it when I get back to work as soon as I can (after Monday). Thanks for your time.
This is a clever idea. I will try it when I get back to work as soon as I can (after Monday). Thanks for your time.
ASKER
I have tried the query. When I ran the SSIS package, the package just stopped when it encountered a divide by zero error. It didn't take the "On failure" flow. What do you suggest? Thanks.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
jjrr, how did it go?
ASKER
I apologize for the delay.
http://www.sqldts.com/211.aspx