Catching an error - 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

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Catching an error

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-31 : 11:50:35

I have a SSIS package set up. Here are the first couple of SQL Tasks:

1st SQL Task

Drop Table "LastDiary"

Exec sp_rename "CurrentDiary", "LastDiary"

2nd SQL Task

Drop Table "LastDiary"

Exec sp_rename "CurrentDiary", "LastDiary"

3rd SQL Task
Bringing in a Flat File from a shared drive. The file is called DataInfo.txt.

This DataInfo.txt is downloaded from the mainframe every Monday morning as a txt file and added to the shared drive. My SSIS package grabs that file but how do I tell SSIS to check to see if the Date modified is the same date on the txt files as last week or if it's not today's date then don't run the SSIS package?

I know I will have to do it as the first step in my SSIS package but don't know how I would write this in a new SQL Task.

Would I use Rollback Transaction?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 14:15:48

the first steps looks exactly same. why do you need both?

for your last part what you need is to use expression for precedence constraint. drag and drop a script task after file grabbing step. capture the date modified value of file onto variable created in package and use it to check against current date to see if it is current date. based on result populate a boolean variable as true or false. then in following precedence constraint use constraint and expression option and give expression as @[User::BooleanVariableNameHere] ==true

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 14:17:58

and to know how to grab modified date from file see below

http://jamelcato.com/how-to-check-a-file%E2%80%99s-last-modified-date-using-ssis/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-31 : 15:31:57

Thanks so much for the info and the link. Sorry the second SQL Task is:

Truncate Table "DataInfo"

I added the Script Task and clicked on Design Script and added this from the link you provided

I'm getting an error message:
Name ScriptResults is not declared

What am I suppose to put here:

User::File_Updated_Actual


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

Public Class ScriptMain

	
	Public Sub Main()
	
        Dim fi As System.IO.FileInfo
        Dim ModifiedTime As DateTime

        If Dts.Variables.Contains("User::File_Updated_Actual") = True Then

            fi = New System.IO.FileInfo("\\s5sg53\downloads\Data\DataInfo.txt")
            ModifiedTime = fi.LastWriteTime
            Dts.Variables.Item("User::File_Updated_Actual").Value = fi.LastWriteTime.ToShortDateString
            Dts.TaskResult = ScriptResults.Success
        Else
            Dts.TaskResult = ScriptResults.Failure
        End If

    End Sub

End Class

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:02:26

is script results name of your script task? you should put your actual task name there

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-01 : 08:46:49

I actually had not named it at all it was left as Script Task. I went in under the General tab and changed the name and description to ScriptResults but I'm still Name ScriptResults is not declared.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-02 : 09:35:47

Hello I got this to work:


 Public Sub Main()
        '
        'Dim fi As System.IO.FileInfo
        'Dim ModifiedTime As DateTime

        If Dts.Variables.Contains("User::File_Updated_Actual") = True Then

            fi = New System.IO.FileInfo("\\seb2a53\downloads\offsets\SSISDiaries.txt")
            ModifiedTime = fi.LastWriteTime
            Dts.Variables.Item("User::File_Updated_Actual").Value = fi.LastWriteTime.ToShortDateString
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

How do I add the precedence constraint? Is it a sql task? Also I would like to have an email sent to me regarding the success or failure

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-02 : 10:09:03

Okay I added the Precedence Constraints but got this error message on configuring the Sendmail task:

[Send Mail Task] Error: An error occurred with the following error message: "The connection "{C3A4A505-E15C-4C69-818A-FDADA844B4BA}" is not found. This error is thrown by Connections collection when the specific connection element is not found. ".

How do I find the SMTP Server address and do I use use Window Authentication?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-02 : 11:13:15

Back again

Okay realize I will have to ask my server guys to help me with the SMTP Address

Another question...

I need to add variables:

Using this do I add the DataInfo.txt as a variable or just the FileUpdateAcutal as a variable?


 Public Sub Main()
        '
        Dim fi As System.IO.FileInfo
        Dim ModifiedTime As DateTime


        If Dts.Variables.Contains("User::FileUpdatedActual") = True Then

            fi = New System.IO.FileInfo("\\sb2a5\downloads\DataInfo\DataInfo.txt")
            ModifiedTime = fi.LastWriteTime
            Dts.Variables.Item("User::FileUpdatedActual").Value = fi.LastWriteTime.ToShortDateString
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 12:43:55

you're just checking presence of variable and assigning value in above code. You need to create the variable before this in variables tab of ssis.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-02 : 13:28:16

Okay I thanks I added this variable:

Name = FileUpdateActual

Scope = Script Task

Data Type = DateTime

Value came up as 4/2/2010

I ran it and the Script Task turned yellow and then this Runtime Error appeared:

DTS Script TAsk has encountered an exception in user code:

Project name: ScriptTask_e308c28ded014505954830d7cc35132c

The network path was not found

This was in the box underneath:

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileSystemInfo.get_LastWriteTimeUtc()
at System.IO.FileSystemInfo.get_LastWriteTime()
at ScriptTask_e308c28ded014505954830d7cc35a32c.ScriptMain.Main()

What am I doing wrong?

In the Precedence Constraint Editor I have:
Evaluation operation: Constraint

Value: Failure

Multiple constraints = Logical AND, All constraints must evaluate to True

In the Script Task I added User::FileUpdatedActual to the ReadWriteVariables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 13:30:36

does account running package has access to path \\sb2a5\downloads\DataInfo\DataInfo.txt?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-02 : 13:45:08

I was missing $ which should have gone after the word downloads$. Thanks that worked but now I have a new error:


Error: The type of the value being assigned to variable "User::FileUpdatedActual" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

This is what's set up in Explorer (where the file is located)

Name: DataInfo.txt

Size: 240,000

Type: Text Document

Date Modified: 3/29/2010 4:02AM

So I know this should come up and fail then I want an email to go to the DBA.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 13:46:21

whats the datatype you used for User::FileUpdatedActual?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-02 : 13:48:47

DateTime

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 13:51:48

seems like its not able to interpret date value correctly from fi.LastWriteTime.ToShortDateString. can you try printing it out and see if it does have correct date value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-02 : 13:57:17

How do I print out:

fi.LastWriteTime

I have Dim ModifiedTime as DateTime then call it here:

Dim fi As System.IO.FileInfo
Dim ModifiedTime As DateTime

If Dts.Variables.Contains("User::FileUpdatedActual") = True Then

       fi = New System.IO.FileInfo("\\sb2a5\downloads$\DataInfo\DataInfo.txt")")
            ModifiedTime = fi.LastWriteTime
            Dts.Variables.Item("User::FileUpdatedActual").Value = fi.LastWriteTime.ToShortDateString
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

try using msgbox

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-02 : 14:06:19

Got it to work!!!!

I got rid of LastWriteTime.ToShortDateString and made it:

LastWriteTime.Date

It turned Green! Thanks.

Now I'm trying to send it to a Send Mail Task. In the Precedence Constraint Editor I added:

Evaluation Operation = Expression and Contraint

Value = False

Expression: User::FileUpdateActual == True

When I tested it I got:

The expression is required to evaluate to true or false:
"@User::FileUpdateActual==True"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-02 : 14:13:40

I added MsgBox(Dts.Results.Success) and it came back as Script Task 0

When I added MsgBox(Dts.Results.Failure) it came back as Script Task 1

I want it to come back as failure if the file that's in there is not today's date. Will what I have make it do that?

Dim fi As System.IO.FileInfo
Dim ModifiedTime As DateTime

If Dts.Variables.Contains("User::FileUpdatedActual") = True Then

fi = New System.IO.FileInfo("\\sb2a5\downloads$\DataInfo\DataInfo.txt")")
ModifiedTime = fi.LastWriteTime
Dts.Variables.Item("User::FileUpdatedActual").Value = fi.LastWriteTime.ToShortDateString
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 14:15:40

how can date values variable be evaluated to true?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources