How to create dtsx package in sql server?

• Open SQL Server Data Tools (SSDT):

  • Open SSDT and create a new SSIS project.

• Create a New SSIS Package:

  • Right-click on the "SSIS Packages" folder in Solution Explorer.
  • Select "New SSIS Package".

• Add a Foreach Loop Container:

  • Drag a Foreach Loop Container from the SSIS Toolbox to the Control Flow design surface.
  • Double-click the Foreach Loop Container to configure it.
  • In the "Collection" tab, set the "Enumerator" to "Foreach File Enumerator".
  • Set the "Folder" to D:\SQL BackUps and the "Files" to *.* to loop through all files.
  • In the "Variable Mappings" tab, create a variable to hold the file name (e.g., User::FileName).

• Add a Script Task:

  • Drag a Script Task into the Foreach Loop Container.
  • Double-click the Script Task to configure it.
  • In the "Script" tab, add the variable User::FileName as a ReadOnlyVariable.
  • Click "Edit Script" to open the VSTA editor and add the following C# code to delete files older than 4 weeks:

using System;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;

public void Main()
{
    string fileName = Dts.Variables["User::FileName"].Value.ToString();
    FileInfo fileInfo = new FileInfo(fileName);

    if (fileInfo.LastWriteTime < DateTime.Now.AddDays(-28))
    {
        fileInfo.Delete();
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    else
    {
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}