Solved

Migrate DTS-ActiveX script to SSIS script with a calculated file name (Name+yyyyMMdd.txt)

Posted on 2009-03-31
5
798 Views
Last Modified: 2013-11-30
The code attached simply calc's the date value (If today = Monday then friday's date (Today - 3) if not then yesterday's date (Today -1) and copies the file to two locations. As the systemfileobject does not exist in SSIS I am unsure how to reproduce this function.
DTS - ActiveX (VB Script)
 

Function Main()

Const TempFile = "\\server\folder\RS-"

Dim fs

Dim fil
 

Set fs = CreateObject("Scripting.FileSystemObject")

Set fil = fs.GetFile(TempFile & Date2YYYYMMDD & "-PSN-0001.PSX")
 

Call fil.Copy("\\server\folder\RS.PSX")

Call fil.Copy("\\server2\folder\RS.PSX")
 
 

Set fil = Nothing

Set fs = Nothing
 

Main = DTSTaskExecResult_Success
 

End Function
 
 

Function Date2YYYYMMDD()

  Dim PreviousBusinessDay

		

	If WeekdayName(Weekday(Now()))  = "Monday" Then

		PreviousBusinessDay = Now() - 3

	Else

		PreviousBusinessDay = Now() - 1

	End If

	

	Date2YYYYMMDD = CStr(Year(PreviousBusinessDay) & Pad2Left(Month(PreviousBusinessDay)) & Pad2Left(Day(PreviousBusinessDay)))

End Function
 

Function Pad2Left(ByVal Value)

	Pad2Left = Right("0" & CStr(Value), 2)

End Function

Open in new window

0
Comment
Question by:pwanveer
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24037470
Use a script task in SSIS control flow, paste the code inside script and add references needed.
You set 250 points to the question, and for that reason I think you are familiar with SSIS and think this question is medium and can go forward and customize it
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24042933
You may want to look at the Microsoft SQL Server 2000 DTS Designer Components. That lets you edit and run DTS packages in SQL 2005.

Microsoft SQL Server 2000 DTS Designer Components
http://msdn.microsoft.com/en-us/sqlserver/aa336314.aspx
0
 

Author Comment

by:pwanveer
ID: 24043996
As SSIS does not Scripting.FileSystemObject some sort of modification is required. I have tried the code below which fails. I believe due to a format issue surrounding the date string. However I cannot see what value is being calculated.
Imports System.IO
 

Public Class ScriptMain

    Public Sub Copy()
 

        Dim FileName As String
 

        MsgBox("Value =" + CStr(FormatDate(ProcessDateParameter())))
 

        FileName = ("\\server\folder\RS-" + CStr(FormatDate(ProcessDateParameter())) + "-PSN-0001.PSX")
 

        File.Copy(FileName, "\\server\folder\RS.PSX")
 

    End Sub

    Public Function FormatDate(ByVal dateToFormat As Date) As String

        Return dateToFormat.ToString("yyyyMMdd")

    End Function
 
 

    Public Function ProcessDateParameter() As Date
 

        If WeekdayName(Weekday(Now())) = "Monday" Then

            Return Now.Date.AddDays(-3)

        Else

            Return Now.Date.AddDays(-1)

        End If
 

        Dim result As Date
 
 

    End Function

End Class

Open in new window

0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24044331
You can use FileSystemObject  in SSIS... why you think not?
0
 

Accepted Solution

by:
pwanveer earned 0 total points
ID: 24052134
Figured out a solution
Imports System.IO
 

Public Class ScriptMain
 

    Public Sub Main()
 

        

        File.Copy("\\server\folder\RS-" + FormatDate(ProcessDate()) + "-PSN-0001.PSX", "\\server2\folder\RS-" + FormatDate(ProcessDate()) + "-PSN-0001.PSX")

        File.Copy("\\server\folder\BS-" + FormatDate(ProcessDate()) + "-PSN-0001.PSX", "\\server2\folder\BS-" + FormatDate(ProcessDate()) + "-PSN-0001.PSX")

        File.Copy("\\server\folder\MR-" + FormatDate(ProcessDate()) + "-PSN-0001.PSX", "\\server2\folder\MR-" + FormatDate(ProcessDate()) + "-PSN-0001.PSX")

        File.Copy("\\server\folder\DU-" + FormatDate(ProcessDate()) + "-PSN-0001.PSX", "\\server2\folder\DU-" + FormatDate(ProcessDate()) + "-PSN-0001.PSX")

        File.Copy("\\server\folder\SF-" + FormatDate(ProcessDate()) + "-PSN-0001.PSX", "\\server2\folder\SF-" + FormatDate(ProcessDate()) + "-PSN-0001.PSX")

         

        File.Copy("\\server\folder\SF-" + FormatDate(ProcessDate()) + "-TRN-0001.TRX", "\\server2\folder\SF-" + FormatDate(ProcessDate()) + "-TRN-0001.TRX")
 
 

    End Sub
 

    Public Function ProcessDate() As Date
 

        If Now.DayOfWeek = System.DayOfWeek.Monday Then
 

            Return Now.Date.AddDays(-3)

        End If
 

        If Now.DayOfWeek <> System.DayOfWeek.Monday Then
 

            Return Now.Date.AddDays(-1)

        End If
 

    End Function
 

    Public Function FormatDate(ByVal dateToFormat As Date) As String

        Return dateToFormat.ToString("yyyyMMdd")

    End Function
 

End Class

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Before we dive into the marketing strategies involved with creating an effective homepage, it’s crucial that EE members know what a homepage is. In essence, a homepage is the introductory, or default page, of a website that typically highlights the …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now