Solved

best way to rename files in SSIS

Posted on 2009-05-08
4
677 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
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL SERVER - Index skipped a number 2 27
TSQL Challenge... 7 37
SQL Server 2008 R2, need a pivot/cross tab query... 4 29
Run Stored Procedure uisng ADO 5 21
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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

829 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