?
Solved

best way to rename files in SSIS

Posted on 2009-05-08
4
Medium Priority
?
680 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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 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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 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