Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This Blog is About SQL Server Technolgy
Browse by Tags · View All
BRH 12
#WHITEPAPERS 10
whitepapers 10
webcast 10
Training 9
MSBI 9
BI 8
SQL-Server 8
SSAS 7
T-SQL 7

Archive · View All
August 2010 9
July 2010 7
May 2009 6
September 2010 4
June 2009 4
July 2009 3
October 2010 2
April 2010 2
March 2010 1

Ashish's Blog On SQL Server

SSIS File Operations – Copy File, Move File, Rename File and Delete File

Aug 31 2010 2:00AM by Ashish Gilhotra   

In the previous post we saw that how to Load data from Text files to SQL Server Tables and successfully created a package which loads data in to SQL Server table from a flat file. In this post today we will see SSIS File Operations – Copy File, Move File, Rename File and Delete File. Several times we need to do some file operations like copy, move, delete or rename file while performing the integration tasks. In SSIS we can accomplish these by File System Task control. The File System Task control is used for performing operations on files and directories such as move, create, delete, rename file and directories. We will see each and every operation separately.

 

Create SSIS Project

First we need to create an SSIS Project before starting with SSIS File Operations, you can create a SSIS Project by looking in to this previous SSIS post. Let’s say we created an SSIS project as FileOperations.

Copy File:

We can copy file from one place to another with the help of SSIS. We will look into the whole process of copy file from one place to another in this section, for this you need a New Package in your project which you can create easily by looking into my pervious SSIS post. Now we have a SSIS project named FIleOperations, it is the time to create a package in our SSIS project. Add a new package and name it as CopyFile.

Adding File System Task in Your Package

Now we have a package and it is time to add File System Task to your package, to add File System Task control to the package go to control flow times in toolbox and double click on File System Tasks or drag and drop it in your package(See the image below).

copy1 

Configuring File System Task

Now we have File System Task control added to your package and we can configure it. To configure right click on the control and choose edit(See the image below).

copy2

After that you will be navigated to a new window named File System Task Editor in which you have to configure the File System Task(See the image below).

copy3

The options on File system task editor are as below.

  1. IsDestinationPathVariable : Destination path is stored as variable or not.
  2. DestinationConnection : File Connection or vairable.
  3. OverwriteDestination : Overwrite if file exists with same name. True/False
  4. Name : name you want to give.
  5. Description : Little description
  6. Operation : Choose copy file.
  7. IsSourcePathVariable : Source path is stored as variable or not.
  8. SourceConnection : File Connetion or variable.

Here we have to choose operation as Copy File choose OverwriteDestination as True and IsDestinationPathVariable and IsSourcePathVariable as False. Now we have to configure Source and destination connections.

Configuring Source and Destination Connections

Now we have to configure source and destination connections.

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

copy4 

Now you will be navigated to new window named File Connection Manager Editor where you have to configure your source connection. Choose Existing FIle as Usage Type and provide fully qualified path for file which you want to copy to other location(See the image below).

copy5

Destination Connection : Once you done with your SourceConnection you have to configure Destination Connection. Go to DestinationConnection tab and choose New Connection(See the image below).

copy6

Now you will be navigated to new window named File Connection Manager Editor where you have to configure your destination connection(See the image below).

copy7 

Here we also have to provide Usage Type & Folder as in Usage Type we choose Existing Folder so we have to provide the destination folder where we want to have a copy of file.

Now you done with both Source and Destination connection and your final File System Task Editor looks like this(See the image below).

copy8

Click OK  and you are done with your Copy File Task. Save it and execute package as we did in previous post and this will copy specific file from one place to another.

Rename File:

You done with Copy File, we will see the Rename File operation next. This operation will rename any file to the another name you want, for this we need to create a package named Rename. Once you have package added to your project open it and drag and drop File System Task control to your package as we did earlier in this post. Now we will configure that file system task to rename files. For this you have to right click on the package and choose Edit as we did earlier in this post. This will open up File System Task Editor, we have to configure that editor to rename files(See the image below).

We will choose Operation as Rename File(See the image below).

Rename1

Now we have to configure Source and Destination Connection for Rename File System Task.

Configure Source and Destination Connection :

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

copy4

Now you will be navigated to a new window named File Connection Manager Editor and here you have to configure your SourceConnection choose Existing File as Usage Type and provide Fully Qualified Path in File(See the image below).

Rename2

click Ok and your are done with your SourceConnection.

Destination Connection : Once you are done with your SourceConnection you have to configure Destination Connection where you want your renamed file, for this navigate to DestinationConnection Tab and choose New Connection(See the image below).

copy6

Now you will be navigated to new window named File Connection Manager Editor and here you have to configure your DestinationConnection choose Create File as Usage Type and provide Fully Qualified Path in File(See the image below).

Rename3

Click Ok and you are done with your DestinationConnection.

Now you will be navigated back to File System Task Editor here click ok and you are done with your Rename File Task. You can run this package as shown in previous post and you will get new file with name Renamed.txt at your destination path.

Move File :

In this Move File Task we will see how to move files from one location to another location. For this we have to add a package name Move in our project and add File System Task Control to your package by Drag & Drop from ToolBox. Once you have File System Task added to your project you have to configure this to move file by right click and choose edit on File System Task as we did earlier in this post(See the image below).

move1

Now after this we need to configure Source and Destination Connection as we did earlier in this post.

Configure Source and Destination Connection :

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

copy4

Now you will be navigated to a new window named File Connection Manager Editor and here you have to configure your SourceConnection choose Existing File as Usage Type and provide Fully Qualified Path in File(See the image below).

move2

click Ok and your are done with your SourceConnection.

Destination Connection : Once you are done with your SourceConnection you have to configure Destination Connection where you want to move file, for this navigate to DestinationConnection Tab and choose New Connection(See the image below).

copy6

Now you will be navigated to new window named File Connection Manager Editor and here you have to configure your DestinationConnection choose Existing Folder as Usage Type and provide Folder Path in File(See the image below).

move3

Click Ok and you are done with your DestinationConnection.

Now you will be navigated back to File System Task Editor here click ok and you are done with your Move File Task. You can run this package as shown in previous post and you will get a new file at DestinationConnection and deleted from SourceConnection.

Delete File:

Delete File Task  is used to delete specific file from the specific location. For this we need to create a new package by name delete  in our sample project and add File System Task control to your package by Drag & Drop it from ToolBox. Once you have File System Task added to your package Right Click on File System Task and choose Edit to configure it to delete files from a specific location. Now we get File System Task Editor window on our screen choose Delete File as operation in File System Task Editor window(See the image below).

Delete1

Now we have to configure Source Connection for this from where we like to delete files.

Configure Source Connection :

Go to SourceConnection tab in File System Task Editor and choose New Connection(See the image below).

copy4

Now you will be navigated to a new window named File Connection Manager Editor and here you have to configure your SourceConnection choose Existing File as Usage Type and provide Fully Qualified Path in File(See the image below).

Delete2

Click Ok and you are done with SourceConnection and navigated back to File System Task Editor, Click Ok here and you are done with your Delete File Task run this package as shown in previous post and the targeted file will be deleted from the path specified in SourceConnection.

In this post we looked in to different File Operations(Copy, Rename, Move and Delete File). If you are facing any problems or you have any question regarding these file operations let me know by posting a comment, I will try to resolve it at the soonest.

Tags: SQL-Server, SSIS, BRH, BI, #BI, #SQLServer,


Ashish Gilhotra
31 · 6% · 1776
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • HI,

    Please can you help. I am a complete beginner and just had somebody help me set up packages in bids to loop through a folder and extract the information from 29 xlsb files with identically constructed columns in them.

    At month end I have been copying manually the 29 files form the current location into a location where the package picks them up. I am finding though that i appears that in order for the package to work I need to open the files and save them. ( I can either do this in the current location before I move them or in the new location) but once somebody else has accessed the file The package will not work until I have opened it and saved it.

    Being a complete novice I have no idea how to overcome this and wondered if copying the files via a package might make a difference?

    This is the error I am getting in the package.

    An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "This file was created in a previous beta version of Excel 2007. Open the file with Excel 2007 to save it to the most recent version of the Excel 2007 file format before opening the file in Access 2007.".

    Do any of the following affect it.

    1.Some of the excel files have links to other other files. 2.If I copy it over to the location and somebody was in the file when I took a copy. 3. I think we are all using the same verison of Excel and Windows XP though could this affect it? 4. If people amend it in the location where the package picks it up form should that resolve the issue? 5. IN the past the Excel files were xls before they have been made xlsb. Do I need to copy the the sheets into a new workbook and and rename directly as xlsb.? 6. Would the problem go away if the files were xlsx instead of xlsb.

    The connection string in the intial excel conenction is as follows

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 12.0;HDR=YES\";"

    IN the execution part of the loop everything is set to false.

    If I do create the copy package above do I need to ensure nobody is in the files.

    I would really appreciate your help as there is nobody at work who can support me on this and i desperatelty need to resolve the issue without having to open 29 files each month. I have no idea where it may be going wrong hence why tries to give you different parts of the package but so not understand them in enought details to trouble shoot them.

    Many thanks in advance - even if you cannot solve just some pointer would be really helpful

    Michelle

    commented on Apr 13 2012 4:07AM
    SQLHELP1
    3049 · 0% · 2
  • Try and download " Long Path Tool " useful in error messages like: Cannot read from source file or disk, there has been a sharing violation, cannot delete file or folder, the file name you specified is not valid or too long, the source or destination file may be in use and many other file managing errors.

    commented on Aug 8 2013 9:20AM
    adamdee
    3049 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"SSIS File Operations – Copy File, Move File, Rename File and Delete File" rated 5 out of 5 by 3 readers
SSIS File Operations – Copy File, Move File, Rename File and Delete File , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]