Solved

best way to rename files in SSIS

Posted on 2009-05-08
4
679 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 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

719 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