SQL Server Performance Forum – Threads Archive
Check file exists before load into Database
Hi Folks I am creating SSIS package to load data into specific table. Before load the file I have to check if current date file is exists in the destination folder. how can do it using SSIS? Thanks…sonny
Use system.io.file.exist(PathOfFile)
___________________________________
Need an IT job? –http://www.ITjobfeed.com
Thanks…. I have written this piece of code to check file exists..and added into Execute SQL Task. what need to do is
"if @fileexts =1 then execute next step (Execute SQL Task) to stsrt loading the excel file into table or if @fileexts = 0 then send email for non exists of file". how can execute next step in the basis of variable value? I need help on this stage. here it is the code……
DECLARE @filename VARCHAR(100)
[email protected] INT
DECLARE @processname VARCHAR(50)
DECLARE @filedest VARCHAR(100)
DECLARE @month char(2)
DECLARE @date char(2)
DECLARE @year char(4) SET @processname = ‘sales’
set @month = DATEPART(m, getdate())
if LEN(RTRIM(DATEPART(m, getdate()))) = 1
set @month = ‘0’+RTRIM(DATEPART(m, getdate()))
set @date = DATEPART(d, getdate())
if LEN(RTRIM(DATEPART(d, getdate()))) = 1
set @date = ‘0’+RTRIM(DATEPART(d, getdate()))
set @year = DATEPART(yyyy, getdate()) set @filename = @[email protected][email protected][email protected]
set @filedest = ‘D:sales’[email protected]+’.xls’ EXEC master..xp_fileexist @filedest,@fileexists OUT
IF @fileexists = 1 Another solution I have found was : File Watcher Task from this site
http://www.sqlis.com/23.aspx
I did looked into but somehow couldn’t figured out that how this could solve my problem? Thanks in advance…
The simple way is when @fileexts = 0 raise the error which will cause the task to fail then on failure send the notification…
@fileexts = 1 do nothing… on success load the excel file into table… You can also use OPENROWSET or OPENQUERY to load the excel into a table…. How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686 MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Thanks MohammedU But that’s what I do not know that how I added "@fileexts = 0 raise the error " in SSIS. I can think of that in this case go to step so on so…(step that send the notification mail).. could you give some example??? sonny
Do you have a notification services installed or database mail configuration on this server instance? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I have databse mail services installed.. as i am new user of SQL 2005…do not know how configure and use notification services yet.. Thanks,
You can make use of SendMail task within SSIS http://www.databasejournal.com/features/mssql/article.php/3646986] fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I mentioned earlier that I have alreday done this (send mail using database mail) part.. what i am asking that execute next task on the basis of step in which checking file exists or not. And if not exist then the execute step send mail to notify that file not exists otherwise the execute step to load the file into table. All this depend on return value of step on which check exists of file which return ‘0’ (file not found) or ‘1’ (file found).
quote:
I have written this piece of code to check file exists..and added into Execute SQL Task. what need to do is
"if @fileexts =1 then execute next step (Execute SQL Task) to stsrt loading the excel file into table or if @fileexts = 0 then send email for non exists of file". how can execute next step in the basis of variable value? I need help on this stage. here it is the code……
DECLARE @filename VARCHAR(100)
[email protected] INT
DECLARE @processname VARCHAR(50)
DECLARE @filedest VARCHAR(100)
DECLARE @month char(2)
DECLARE @date char(2)
DECLARE @year char(4) SET @processname = ‘sales’
set @month = DATEPART(m, getdate())
if LEN(RTRIM(DATEPART(m, getdate()))) = 1
set @month = ‘0’+RTRIM(DATEPART(m, getdate()))
set @date = DATEPART(d, getdate())
if LEN(RTRIM(DATEPART(d, getdate()))) = 1
set @date = ‘0’+RTRIM(DATEPART(d, getdate()))
set @year = DATEPART(yyyy, getdate()) set @filename = @[email protected][email protected][email protected]
set @filedest = ‘D:sales’[email protected]+’.xls’ EXEC master..xp_fileexist @filedest,@fileexists OUT
IF @fileexists = 1 Another solution I have found was : File Watcher Task from this site
http://www.sqlis.com/23.aspx
I did looked into but somehow couldn’t figured out that how this could solve my problem?
I have written this piece of code to check file exists..and added into Execute SQL Task. what need to do is
"if @fileexts =1 then execute next step (Execute SQL Task) to stsrt loading the excel file into table or if @fileexts = 0 then send email for non exists of file". how can execute next step in the basis of variable value? I need help on this stage. here it is the code……
DECLARE @filename VARCHAR(100)
[email protected] INT
DECLARE @processname VARCHAR(50)
DECLARE @filedest VARCHAR(100)
DECLARE @month char(2)
DECLARE @date char(2)
DECLARE @year char(4) SET @processname = ‘sales’
set @month = DATEPART(m, getdate())
if LEN(RTRIM(DATEPART(m, getdate()))) = 1
set @month = ‘0’+RTRIM(DATEPART(m, getdate()))
set @date = DATEPART(d, getdate())
if LEN(RTRIM(DATEPART(d, getdate()))) = 1
set @date = ‘0’+RTRIM(DATEPART(d, getdate()))
set @year = DATEPART(yyyy, getdate()) set @filename = @[email protected][email protected][email protected]
set @filedest = ‘D:sales’[email protected]+’.xls’ EXEC master..xp_fileexist @filedest,@fileexists OUT
IF @fileexists = 1 Another solution I have found was : File Watcher Task from this site
http://www.sqlis.com/23.aspx
I did looked into but somehow couldn’t figured out that how this could solve my problem?
Thanks
sonny
Folks let me define the steps and hope some one help me on this.. step1:
Truncate the staging table
step2:
check the existence of requred file on the destination folder(as I used above pasted script).
if file found then next is step 3 otherwise send file not found mail on step 4 step3:
load the file into staging table Step 4:
Send warning using database mail to concern people Step5:
move the data from staging table to production table Step6:
send database mail for successful data load. I am stucked on step2 part check file and passing the parameter value 0(file not found) or 1 (file found) to the next step… it is urgent guys.. Thanks in advance
See thsihttp://www.sqldts.com/211.aspx is any help. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I think tht this works with SQL Server 2000??? what about SQL Server 2005? sonny
DTS still have support for such legacy stuff, I can work it out at my end. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi Folks add further to the topic.. There are more 1 sheet in the excel file.. can I load in one go all of the sheets from one excel file? if not then how? Thanks in Advance..
See if this helps
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8440 Madhivanan Failing to plan is Planning to fail
Thanks Madhivanan But I am using SSIS and want to use SSIS..
Wait I’m confused. If you are using SSIS then why not create a data flow task for the import. If it fails then use sendmail task, tell you that the required file doesn’t exist. If it succeeds then upload into DB.
]]>