Solved

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

Posted on 2009-03-31
5
819 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Lag Function DateDiff 2 26
HIghlights of SSIS? 3 45
t-sql left join 2 34
MS SQL TO JSON 14 18
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

710 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