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

Create SSIS Project
First we need to create an SSIS Project before starting with SSIS File Operations, you can create a SSIS Project. 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. 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).

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).

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).

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).
 
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).

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).

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).

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).

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).

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).

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).

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).

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).

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 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).

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).

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).

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).

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).


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 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).

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).

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).

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 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.

Multiple Excel File With Multiple Sheets Loading in a Table

Here is one possible way of doing this based on the assumption that there will not be any blank sheets in the Excel files and also all the sheets follow the exact same structure. Also, under the assumption that the file extension is only .xlsx
Following example was created using SSIS 2008 R2 and Excel 2007. The working folder for this example is F:\Temp\
In the folder path F:\Temp\, create an Excel 2007 spreadsheet file named States_1.xlsx with two worksheets.
Sheet 1 of States_1.xlsx contained the following data

Sheet 2 of States_1.xlsx contained the following data

In the folder path F:\Temp\, create another Excel 2007 spreadsheet file named States_2.xlsx with two worksheets.
Sheet 1 of States_2.xlsx contained the following data

Sheet 2 of States_2.xlsx contained the following data

Create a table in SQL Server named dbo.Destination using the below create script. Excel sheet data will be inserted into this table.

CREATE TABLE [dbo].[Destination](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [State] [nvarchar](255) NULL,
    [Country] [nvarchar](255) NULL,
    [FilePath] [nvarchar](255) NULL,
    [SheetName] [nvarchar](255) NULL,
CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

The table is currently empty.

Create a new SSIS package and on the package, create the following 4 variables. FolderPath will contain the folder where the Excel files are stored. FilePattern will contain the extension of the files that will be looped through and this example works only for .xlsx. FilePath will be assigned with a value by the Foreach Loop container but we need a valid path to begin with for design time and it is currently populated with the path F:\Temp\States_1.xlsx of the first Excel file. SheetName will contain the actual sheet name but we need to populate with initial value Sheet1$ to avoid design time error.

In the package's connection manager, create an ADO.NET connection with the following configuration and name it as ExcelSchema.
Select the provider Microsoft Office 12.0 Access Database Engine OLE DB Provider under .Net Providers for OleDb. Provide the file path F:\Temp\States_1.xlsx

Click on the All section on the left side and set the property Extended Properties to Excel 12.0 to denote the version of Excel. Here in this case 12.0 denotes Excel 2007. Click on the Test Connection to make sure that the connection succeeds.

Create an Excel connection manager named Excel as shown below.

Create an OLE DB Connection SQL Server named SQLServer. So, we should have three connections on the package as shown below.

We need to do the following connection string changes so that the Excel file is dynamically changed as the files are looped through.

More Important give DELAY VALIDATION = TRUE for  ForEach Loop Container and Excel Connection Manager and other things

On the connection ExcelSchema, configure the expression ServerName to use the variable FilePath. Click on the ellipsis button to configure the expression.


Similarly on the connection Excel, configure the expression ServerName to use the variable FilePath. Click on the ellipsis button to configure the expression.

On the Control Flow, place two Foreach Loop containers one within the other. The first Foreach Loop container named Loop files will loop through the files. The second Foreach Loop container will through the sheets within the container. Within the inner For each loop container, place a Data Flow Task that will read the Excel files and load data into SQL

Configure the first Foreach loop container named Loop files as shown below:


Configure the first Foreach loop container named Loop sheets as shown below:


Inside the data flow task, place an Excel Source, Derived Column and OLE DB Destination as shown below:

Configure the Excel Source to read the appropriate Excel file and the sheet that is currently being looped through.


Configure the derived column to create new columns for file name and sheet name. This is just to demonstrate this example but has no significance.

Configure the OLE DB destination to insert the data into the SQL table.


Below screenshot shows successful execution of the package.

Below screenshot shows that data from the 4 workbooks in 2 Excel spreadsheets that were creating in the beginning of this answer is correctly loaded into the SQL table dbo.Destination.

Hope that helps.

Script To Delete File (C#)

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Threading;

namespace ST_782e82416722494db4d051a43d3603d6.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
        The execution engine calls this method when the task executes.
        To access the object model, use the Dts property. Connections, variables, events,
        and logging features are available as members of the Dts property as shown in the following examples.

        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
        To post a log entry, call Dts.Log("This is my log text", 999, null);
        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

        To use the connections collection use something like the following:
        ConnectionManager cm = Dts.Connections.Add("OLEDB");
        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
       
        To open Help, press F1.
    */

        public void Main()
        {
            string SourceFileName = string.Empty;
            string SourceFileFolder = string.Empty;
            string SourceFilePath = string.Empty;
            string InvalidFileFolderName = string.Empty;
            string InvalidFilePath = string.Empty;
            string InvalidFileName = string.Empty;
            bool ErrorFlag = false;
            string ErrorMessage = "";
            //--string LoadStatus = "";

            System.DateTime vG_ProcessEndDate = System.DateTime.MinValue;

            SourceFileFolder = Dts.Variables["vG_SourceFileFolder"].Value.ToString();
            SourceFileName = Dts.Variables["vG_SourceFileName"].Value.ToString();
            InvalidFileFolderName = Dts.Variables["vG_InvalidFileFolder"].Value.ToString();
            SourceFilePath = SourceFileFolder + SourceFileName;
            InvalidFileName = InvalidFileFolderName + SourceFileName;
            File.Delete(SourceFilePath);
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
   }
}

Script To Copy File From One Folder To Other

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Threading;

namespace ST_782e82416722494db4d051a43d3603d6.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
        The execution engine calls this method when the task executes.
        To access the object model, use the Dts property. Connections, variables, events,
        and logging features are available as members of the Dts property as shown in the following examples.

        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
        To post a log entry, call Dts.Log("This is my log text", 999, null);
        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

        To use the connections collection use something like the following:
        ConnectionManager cm = Dts.Connections.Add("OLEDB");
        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
       
        To open Help, press F1.
    */

        public void Main()
        {
            string SourceFileName = string.Empty;
            string SourceFileFolder = string.Empty;
            string SourceFilePath = string.Empty;
            string InvalidFileFolderName = string.Empty;
            string InvalidFilePath = string.Empty;
            string InvalidFileName = string.Empty;
            bool ErrorFlag = false;
            string ErrorMessage = "";
            //--string LoadStatus = "";

            System.DateTime vG_ProcessEndDate = System.DateTime.MinValue;

            SourceFileFolder = Dts.Variables["vG_SourceFileFolder"].Value.ToString();
            SourceFileName = Dts.Variables["vG_SourceFileName"].Value.ToString();
            InvalidFileFolderName = Dts.Variables["vG_InvalidFileFolder"].Value.ToString();
            SourceFilePath = SourceFileFolder + SourceFileName;
            InvalidFileName = InvalidFileFolderName + SourceFileName;
            try
            {
                if (File_copy(SourceFilePath,InvalidFileName) == true)
                {
                    ErrorFlag = false;
                    ErrorMessage = "File is Invalid";

                }
                else
                {
                    ErrorFlag = true;
                }

                Dts.Variables["vG_ProcessedFileFolder"].Value = InvalidFileFolderName;
                Dts.Variables["vG_ProcessedFilePath"].Value = InvalidFileName;
                Dts.Variables["vG_ProcessedFileName"].Value = SourceFileName;
                Dts.Variables["vG_LoadStatus"].Value = "Failed";
                Dts.Variables["vG_ProcessEndDate"].Value = DateTime.Now;
                Dts.Variables["vG_ErrorMessage"].Value = "File is Invalid";
            }
            catch (Exception ex)
            {
                Dts.Variables["vG_ProcessedFileFolder"].Value = InvalidFileFolderName;
                Dts.Variables["vG_ProcessedFilePath"].Value = InvalidFileName;
                Dts.Variables["vG_ProcessedFileName"].Value = SourceFileName;
                Dts.Variables["vG_LoadStatus"].Value = "Error in moving to Invalid Status";
                Dts.Variables["vG_ProcessEndDate"].Value = DateTime.Now;
                Dts.Variables["vG_ErrorMessage"].Value = ex.Message;
            }
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        public bool File_copy(string OldFileName, string NewFileName)
        {
            try
            {
                string sPath = System.IO.Path.GetDirectoryName(NewFileName);
                if (!Directory.Exists(sPath)) Directory.CreateDirectory(sPath);

                if (File.Exists(NewFileName) == false)
                {
                   
                    File.Copy(OldFileName, NewFileName);

                    return true;
                }
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                bool bVal = false;
                Dts.Events.FireInformation(0, "Write to Log", "Error during file move: " + ex.Message, "", 0, ref bVal);
                return false;
            }
        }
        /*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
   Date         Author      Description
   07-May-10    ASHOK M     Created Initial Version of the Program
   */
    }
}

Script To Move File From One Folder To Other (C#)

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_782e82416722494db4d051a43d3603d6.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
        The execution engine calls this method when the task executes.
        To access the object model, use the Dts property. Connections, variables, events,
        and logging features are available as members of the Dts property as shown in the following examples.

        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
        To post a log entry, call Dts.Log("This is my log text", 999, null);
        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

        To use the connections collection use something like the following:
        ConnectionManager cm = Dts.Connections.Add("OLEDB");
        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
       
        To open Help, press F1.
    */

        public void Main()
        {
            string SourceFileName = string.Empty;
            string SourceFileFolder = string.Empty;
            string SourceFilePath = string.Empty;
            string ArchiveFileFolderName = string.Empty;
            string RejectFilePath = string.Empty;
            string ArchiveFileName = string.Empty;
            bool   ErrorFlag = false;
            string ErrorMessage = "";
            //--string LoadStatus = "";

            System.DateTime vG_ProcessEndDate = System.DateTime.MinValue;

            SourceFileFolder = Dts.Variables["vG_SourceFileFolder"].Value.ToString();
            SourceFileName = Dts.Variables["vG_SourceFileName"].Value.ToString();
            ArchiveFileFolderName = Dts.Variables["vG_ArchiveFileFolder"].Value.ToString();
            SourceFilePath = SourceFileFolder + SourceFileName;
            ArchiveFileName = ArchiveFileFolderName + SourceFileName;
            try
            {
                if (File_Move(SourceFilePath, ArchiveFileName) == true)
                {
                    ErrorFlag = false;
                    ErrorMessage = "No Error";

                }
                else
                {
                    ErrorFlag = true;
                }

                Dts.Variables["vG_ProcessedFileFolder"].Value = ArchiveFileFolderName;
                Dts.Variables["vG_ProcessedFilePath"].Value = ArchiveFileName;
                Dts.Variables["vG_ProcessedFileName"].Value = SourceFileName;
                Dts.Variables["vG_LoadStatus"].Value = "Sucess";
                Dts.Variables["vG_ProcessEndDate"].Value = DateTime.Now;
                Dts.Variables["vG_ErrorMessage"].Value = ErrorMessage;
            }
            catch (Exception ex)
            {
                Dts.Variables["vG_ProcessedFileFolder"].Value = ArchiveFileFolderName;
                Dts.Variables["vG_ProcessedFilePath"].Value = ArchiveFileName;
                Dts.Variables["vG_ProcessedFileName"].Value = SourceFileName;
                Dts.Variables["vG_LoadStatus"].Value = "Error in File Archive";
                Dts.Variables["vG_ProcessEndDate"].Value = DateTime.Now;
                Dts.Variables["vG_ErrorMessage"].Value = ex.Message;
            }
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        public bool File_Move(string OldFileName, string NewFileName)
        {
            try
            {
                string sPath = System.IO.Path.GetDirectoryName(NewFileName);
                if (!Directory.Exists(sPath)) Directory.CreateDirectory(sPath);

                if (File.Exists(NewFileName) == false)
                {
                    File.Move(OldFileName, NewFileName);
                    return true;
                }
                {
                    return false;
                }               
            }
            catch (Exception ex)
            {
                bool bVal = false;
                Dts.Events.FireInformation(0, "Write to Log", "Error during file move: " + ex.Message, "", 0, ref bVal);
                return false;
            }
        }

        /*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
   Date         Author      Description
   07-May-10    Sam K       Created Initial Version of the Program
  
*/

    }
}

How To Reduce Log File Size

Shrink the transaction log (.ldf) file

By using this method, you can shrink the log to the desired size, ex: 1MB
+ Right-click on the database you want to shrink => Tasks => Shrink => Files

Microsoft SQL Server 2008 Shrink

+ On Shrink File box, select Log on File type drop down list and select Reorganize pages before releasing unused space then set size for the log file, ex: 1 MB as picture below:

Microsoft SQL Server 2008 Shrink Set Size Log File

+ Click OK and you’re done.
The good point of this method is you can shrink at the right time but sometime, it doesn’t work especially with Microsoft SQL Server 2005. Let’s try the second method below in case it’s does not work.

2. Completely delete the log file

The main idea of this method is it will delete the big log file and create a new log file with the minimum size.
Follow steps below:
1. Detach the database
Right-click on the database => Tasks => Detach…

Microsoft SQL Server 2008 Detach

2. Delete the big log file
This step will completely erase it from your hard drive. However, from my suggestion you should back up the log file somewhere, ex: rename it from the file location (my case is: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA)

Microsoft SQL Server 2008 Data Folder

3. Attach the database again
As we deleted the log file, so we do attach the database without the log file. Don’t worry, SQL Server will create a new log file for you which will be of the minimum size. That’s perfect!
+ Right-click Databases => Attach…

Microsoft SQL Server 2008 Attach

+ On Attach Database box, click Add…

Microsoft SQL Server 2008 Attach Add

+ Browser to the database (.mdf) file then click OK

Microsoft SQL Server 2008 Browser MDF

+ Select the log (.ldf) file then click Remove

Microsoft SQL Server 2008 Attach Remove Log

Finally, click OK and you’re done.

To Find Larger Size Database

http://sql-bi-dev.blogspot.com/2010/07/find-largest-size-tables-in-database.html

The above link is the source of postings. and can find many more in that link.