Home > MSDN, SSIS > SSIS :- Removing Datestamp From File Name When Moving From One Location to Other

SSIS :- Removing Datestamp From File Name When Moving From One Location to Other

This question is comes from MSDN, link is here.

User Comment:-

Is there any way to move all the file from one location to another and remove the datepart from filename.

for eg:- if file name is abc_20150411.xls change to abc.xls. if file name does not contain date part then ignore it.

Lets create Two Folders Source and Destination in file system, after that come to BIDS or SSDT and create one New SSIS package.

Create 5 Variables in SSIS package like given below:-

Variables

Lets drag one foreach container into the package and set the properties like below:-

ForEachProperty

Inside the ForEach Container create one Script task

Inside Script Task, write below code:-

Imports System.Text.RegularExpressions

Public Sub Main()

Dts.Variables(“SourceFullPath”).Value = Dts.Variables(“SourceFolder”).Value.ToString() + Dts.Variables(“SourceFile”).Value.ToString()

Dts.Variables(“DestinationFullPath”).Value = Dts.Variables(“DestinationFolder”).Value.ToString() + Regex.Replace(Dts.Variables(“SourceFile”).Value.ToString(), “[_0-9]”, “”).ToString()

Dts.TaskResult = ScriptResults.Success

End Sub

Then  below Script task drag File Task

set properties of File Task as shown in below screen:-

FileTaskProperties

as we need to rename the file so , Select Operations as “Rename File”

Lets copy the files in Source folder , it will look like below:-

SourceFolder

Run the package, execution will look like below:-

ExecutionofPackage

lets check the destination folder and source folder to check the result

Result

In result the filenames are modified and no more date is added in the filename.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: MSDN, SSIS Tags: ,
  1. kowie
    January 8, 2016 at 6:56 pm

    I am struggling to get the Script Task to run. would it be possible to provide me the screen dump of the scrip task and properties of the Scrip task or the full ssis

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: