Script file - 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 2005 Forums

SSIS and Import/Export (2005)

Script file

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-10 : 14:45:32

I have this script file in my ssis package:


Public Class ScriptMain

	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts object. Connections, variables, events,
	' and logging features are available as static members of the Dts class.
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Code and Text Editor Help, press F1.
	' To open Object Browser, press Ctrl+Alt+J.

	Public Sub Main()
        Dim fi As System.IO.FileInfo
        fi = New System.IO.FileInfo("\\PhXfer\downloads$\Offsets\SSIDiaries.txt")
        If fi.LastWriteTime.Date = DateTime.Today Then
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

	End Sub


A file is downloaded every Monday.  How can I make the file go into an archieved folder two days later.

Also I don't want it to run on a Monday when there's a holiday.  So Monday, Feb 20th is a holiday. How can I write a script or add to this script not to run that day?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 16:16:53

declare a new variable on script like
Dim arcflag AS Boolean

then use it inside if like

If DateDiff(DateInterval.Day,fi.LastWriteTime.Date,DateTime.Today) >=2 Then
arcflag = True
Else
arcflag = False
End If

then add a precedence constraint from script task to archiving task with expression like

arcFlag==True

and if you have another branch of execution you can link by expression
arcFlag==False under precendence constraint

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-10 : 19:34:20

I made a folder called Archieve how do I get the file that just ran to go into there?

("\\PhXfer\downloads$\Offsets\Archieve")

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-10 : 19:36:06

I forgot to thank you...Thanks!

Another question...How can if get it to test for 02/28/2012 and if it's that date don't run the ssis package.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 20:21:40

quote:
Originally posted by JJ297

I made a folder called Archieve how do I get the file that just ran to go into there?

("\\PhXfer\downloads$\Offsets\Archieve")

use file system task or script task

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 20:22:52

quote:
Originally posted by JJ297

I forgot to thank you...Thanks!

Another question...How can if get it to test for 02/28/2012 and if it's that date don't run the ssis package.

again another if then else check in script task to set another boolean variable and using it in expression there after.

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-10 : 23:01:55

Okay great thanks I will work on this Monday and let you know!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-11 : 08:02:24

no problem...let us know how you got on!

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-13 : 13:21:13

Did get a chance to test it today but I will do it tomorrow and try to move the file into the folder. I will let you know how it goes.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 13:40:40

quote:
Originally posted by JJ297

Did get a chance to test it today but I will do it tomorrow and try to move the file into the folder. I will let you know how it goes.

yep...take your time

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-14 : 11:45:32

Okay I'm stuck.

I added this to my existing script:


Public Class ScriptMain

	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts object. Connections, variables, events,
	' and logging features are available as static members of the Dts class.
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Code and Text Editor Help, press F1.
	' To open Object Browser, press Ctrl+Alt+J.

	Public Sub Main()
        Dim fi As System.IO.FileInfo
        Dim arcflag As Boolean

        fi = New System.IO.FileInfo("\\PhXfer\downloads$\Offsets\SSIDiaries.txt")
        If fi.LastWriteTime.Date = DateTime.Today Then
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

        If DateDiff(DateInterval.Day, fi.LastWriteTime.Date, DateTime.Today) >= 1 Then
            arcflag = True
        Else
            arcflag = False
        End If


	End Sub

Do I add a new script and add this to it?


Try
File.Move(Dts.Variables("\\PhXfer\downloads$\Offsets\SSIDiaries.txt").Value.ToString, Dts.Variables("\\PhXfer\downloads$\Offsets\Archieve").Value.ToString)
Catch ex As Exception
Dts.Events.FireError(1, "", "Source file or destination location does not exist", "",0)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 16:16:05

you need to add a new script as its used for manipulating different control flow branch. it needs to be first script task inside loop over archive folder i guess as per your earlier expression

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-02-15 : 08:48:02

I'm sorry I don't understand when you say it needs to be the first script task inside of loop over.

I have this at top of the Control Flow:


Public Class ScriptMain

	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts object. Connections, variables, events,
	' and logging features are available as static members of the Dts class.
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Code and Text Editor Help, press F1.
	' To open Object Browser, press Ctrl+Alt+J.

	Public Sub Main()
        Dim fi As System.IO.FileInfo
        Dim arcflag As Boolean

        fi = New System.IO.FileInfo("\\PhXfer\downloads$\Offsets\SSIDiaries.txt")
        If fi.LastWriteTime.Date = DateTime.Today Then
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

        If DateDiff(DateInterval.Day, fi.LastWriteTime.Date, DateTime.Today) >= 1 Then
            arcflag = True
        Else
            arcflag = False
        End If


	End Sub

Then I have a sequence container where my things run inside.

Where do I place this last script as I don't want it to run until the very end?


Try
File.Move(Dts.Variables("\\PhXfer\downloads$\Offsets\SSIDiaries.txt").Value.ToString, Dts.Variables("\\PhXfer\downloads$\Offsets\Archieve").Value.ToString)
Catch ex As Exception
Dts.Events.FireError(1, "", "Source file or destination location does not exist", "",0)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources