Help writing stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help writing stored procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-07 : 09:48:34

I need to write a stored procedure in a Script Task:

I want to check if DataInfo.txt is = to today's date (I don't want to use the time with it) then it's successful else it fails. How would I write this below?


Public Sub Main()
		
        Dim fi As System.IO.FileInfo
        
fi = New System.IO.FileInfo("\\sb25\downloads$\Offsets\SSISDiaries.txt") Then

        
        Dts.TaskResult = Dts.Results.Success

        Else
      
        Dts.TaskResult = Dts.Results.Failure

        End If

Sachin.Nand

2937 Posts

Posted - 2010-04-07 : 09:55:08

U mean the name of the .txt file = today's date?

PBUH

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-07 : 09:56:09

Yes but omitting the time at the end of it. I only want it to see the date (today's date)

Sachin.Nand

2937 Posts

Posted - 2010-04-07 : 10:00:50

U are going to do that in the Script task of SSIS. right?

PBUH

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-07 : 10:04:06

Yes

I tried this but getting the blue line under SSISDiaries.txt

Here it is in the Script Task


Public Sub Main()
	
        Dim fi As System.IO.FileInfo
        
        If SSISDiaries.txt = Today Then

            Dts.TaskResult = Dts.Results.Success

            fi = New System.IO.FileInfo("\\sb25\downloads$\Offsets\SSISDiaries.txt")

            
        Else
            

            Dts.TaskResult = Dts.Results.Failure

        End If

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 10:17:03

Try this:


Public Sub Main()
	
        Dim fi As System.IO.FileInfo
        fi = New System.IO.FileInfo("\\sb25\downloads$\Offsets\SSISDiaries.txt")

        If fi.LastWriteTime.Date = DateTime.Today Then
            Dts.TaskResult = Dts.Results.Success            
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

There are 10 types of people in the world, those that understand binary, and those that don't.

Sachin.Nand

2937 Posts

Posted - 2010-04-07 : 10:22:58

U will have to use the split function of VB using '\'.You will get the o/p in an array this way.
\\sb25
downloads$
Offsets
date time.txt.
Then again use split to spilt the txt file name using empty space. you will get two values date and time.
Then compare the first value which is date to today.
For split function have a look at this

PBUH

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-07 : 10:30:53

Thanks for your reply Idera but DBA sent this and it's working...Thanks!


Public Sub Main()
	
        Dim fi As System.IO.FileInfo
        fi = New System.IO.FileInfo("\\sb25\downloads$\Offsets\SSISDiaries.txt")

        If fi.LastWriteTime.Date = DateTime.Today Then
            Dts.TaskResult = Dts.Results.Success            
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

Now I added a Precedence Constraint to my SSIS package and I have the

Evaluation operation = Constraint
Value = Success

If the date is today's date then I want the next Execute SQL Task to run. If not I want it to fail. To get an email sent to me I know I have to go into my Job in SQL Server and select Notifications. Add my email and select when the job fails so I can get an email.

Is this all correct?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-07 : 14:42:02

Okay finally got it all to work.

Thanks for everyone's help. I used DBA's Script Task:

Public Sub Main()

Dim fi As System.IO.FileInfo
fi = New System.IO.FileInfo("\\sb25\downloads$\Offsets\SSISDiaries.txt")

If fi.LastWriteTime.Date = DateTime.Today Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If

Then I added a Precedence Constraint to my SSIS package and configured it as:

Evaluation operation = Constraint
Value = Success

I built the SSIS Package, went into D:/location/bin/deployment and executed that deployment package. I next imported my SSIS package in SQL Server, created a job and scheduled it to run. I next went to notifications and added my email info (Oh I added myself as an Operator so I can receive emails).

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-09 : 14:45:16

I'm back Ugh...

DateTime.Today brings in the time along with the date. How can I just bring in the date and add it to the Script Task in SSIS?


Public Sub Main()
	
        Dim fi As System.IO.FileInfo
        fi = New System.IO.FileInfo("\\sb25\downloads$\Offsets\SSISDiaries.txt")

        If fi.LastWriteTime.Date = DateTime.Today Then
            Dts.TaskResult = Dts.Results.Success            
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-09 : 15:33:15

Never mind I misspelled the txt file it should have been SSIDiaries.txt.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources