Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

best way to rename files in SSIS

Posted on 2009-05-08
4
Medium Priority
?
681 Views
Last Modified: 2012-05-06
Greetings . . . I'm helping to convert a number of DTS packages to DTSX.   There are a number of ActiveX scripts which no longer function, and I need to find alternative means of doing those tasks.  The most common one is to rename a flat file using today's date, for instance renaming:

file_yyyymmdd.txt

to

file_20090508.txt

I've attached the old script below.  It's very simple, but SSIS doesn't like it.  How would I do this in a way that SSIS likes?
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
 
Function Main()
 
	dim fso
	dim d
	dim m
	dim y
 
	d = day(date)
	m = month(date)
	y =  year(date)
	
	if len(d) = 1 then d = "0" & d
	if len(m) = 1 then m = "0" & m
 
	set fso = createobject("Scripting.FileSystemObject")
 
	fso.copyfile "C:\test\Refresh_yyyymmdd.txt", "C:\test\GW_Refresh_" & m & d & y & ".txt"
 
	fso.DeleteFile "C:\test\Refresh_yyyymmdd.txt"	
	
	Main = DTSTaskExecResult_Success
 
End Function

Open in new window

0
Comment
Question by:rsmuckles
[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
4 Comments
 
LVL 9

Expert Comment

by:Hwkranger
ID: 24337496
You'll have to write it in VB.NET Syntaxt.  The first thing I notice is that you dim variables but not as a type.  

So you have to declare the type of your variables as a starter.
0
 
LVL 9

Accepted Solution

by:
Hwkranger earned 1500 total points
ID: 24337512
Second, I would say if you're using SSIS and you want to transfer files:  Use the FILE SYSTEM TASK task.  It will do all this for you.  use a variable for the destination name including todays date.  and voila.
0
 

Author Comment

by:rsmuckles
ID: 24337613
I understand that ultimately any complex scripts in ActiveX will need to be written again in .NET.  Is there, however, another way for me to do a text replace in a filename with today's date?  I am mostly a hardware & database guy and am just helping with this initiative, so learning how to be a programmer for this one task is not a realistic option.  
0
 

Author Closing Comment

by:rsmuckles
ID: 31579508
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

636 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