May 22, 2007 at 11:51 pm
Hi All
I want to oad data into specific table using SSIS package and running every day. Before load the file I have to check if current date file is exists in the destination folder.
I am stucked on step2 check file (using xp_filexist) and passing the return value 0(if file not found) or 1 (if file found) to the next step and execute the further steps.
These are steps....
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.
Thanks in advance
SqlIndia
May 23, 2007 at 1:13 pm
Use a script task to populate a boolean variable to determine if the file exists (in this example, using varFileName, varFileExists). Then use an expression (i.e. @[User::varFileExists]==True) on the precedence constraint prior to executing your ETL tasks.
Tommy
Imports
System
Imports
System.Data
Imports
System.Math
Imports
System.IO
Imports
Microsoft.SqlServer.Dts.Runtime
Public
Class ScriptMain
'Author: Tommy Bollhofer (freebsdboy@gmail.com)
'Last Modified: 11/22/2006
'Purpose: Determines if the file exists and sets the varFileExists boolean value.
Public Sub Main()
Dim fileLoc, fileName As String
If Dts.Variables.Contains("User::varFileName") = True Then
fileName =
CStr(Dts.Variables.Item("User::varFileName").Value)
'System.Windows.Forms.MessageBox.Show("FileDir:"fileName)
If File.Exists(fileName) Then
Dts.Variables.Item(
"User::varFileExists").Value = True
'System.Windows.Forms.MessageBox.Show("File Exists!")
Else
Dts.Variables.Item(
"User::varFileExists").Value = False
'System.Windows.Forms.MessageBox.Show("File Does Not Exist!")
End If
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End
Class
Tommy
Follow @sqlscribeMay 24, 2007 at 1:06 am
Thanks Tommy...
As I am totally new for using SSIS (or DTS)..plus using script in SSIS..so, could you plesae guide me how I am going to implement in SSIS.
Thanks again
SqlIndia
May 24, 2007 at 6:48 am
NP - e-mail me at freebsdboy@gmail.com and I can you send you an example.
Thanks.
Tommy
Tommy
Follow @sqlscribeMay 25, 2007 at 1:22 am
I have send the mail... Tommy
SqlIndia
May 25, 2007 at 10:40 am
Just FYI, replied to your e-mail with an example package attached. Hope it helps.
Tommy
Follow @sqlscribeViewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply