我应该如何执行此文件/文件夹分发任务；SSIS 或命令脚本? [英] How should I perform this file/ folder distribution task; SSIS or command script?
问题描述
我需要执行以下概述的任务，但不确定如何继续.使用 Windows Server 2003，我可以在命令脚本或 SSIS 中的脚本任务中执行此操作吗?我一直在使用 SSIS 2005 并且知道有一个文件系统任务，但我以前从未使用过它.
I need to perform the following outlined task and am unsure how to proceed. Using Windows Server 2003, can I do this in a command script, or maybe a script task in SSIS? I have been using SSIS 2005 and know there is a file system task, but I've never used it before.
- 我在本地有一个文件target.file".
- 网络服务器上有一个目标"文件夹.
- 'target' 下有数百个文件夹.
- 其中一些文件夹下有备份"文件夹.
- 我需要将target.file"复制到target"文件夹下的那些文件夹中.
- 但只有在已经存在target.file"的情况下才复制/替换.
- 如果'target.file'存在，如果备份文件夹存在，则将文件复制并替换到备份文件夹.
- 如果没有，请先创建备份文件夹.
推荐答案
使用 C# 和 VB.NET 在 SSIS 2012 中编写的示例包
这是一个用 SSIS 2012 编写的示例包，它使用脚本任务执行您正在寻找的操作.您不需要使用 SSIS.您甚至可以使用简单的 C# 或 VB.NET 控制台应用程序来完成此操作，但 SSIS 提供了记录信息和安排作业的灵活性.
Sample package written in SSIS 2012 using C# and VB.NET
Here is a sample package written in SSIS 2012 that does what you are looking for using a script task. You don't need to use SSIS. You can even do this with a simple C# or VB.NET console application, but SSIS gives the flexibility to log information and schedule the jobs.
假设文件夹的结构如下所示:
Let's assume that the folders are structured as shown below:
有一个您想要复制的源文件.
There is a source file that you would like to copy.
Source |- Sample_File.txt这是目标文件夹结构.
Target |- Target_1 | |- Archive | |- Sample_File.txt |- Target_2 |- Target_3 |- Sample_File.txt创建一个 SSIS 包并创建文件夹变量:
Create an SSIS package and create the folder variables:
Variable name Data type Value ------------------ ---------- ---------------------- Backup_FolderName String Archive Source_FileName String Sample_File.txt Source_FilePath String Source_Folder String D:SSISFilesSource Target_Folder String D:SSISFilesTarget选择变量
Source_FilePath并单击F4以查看属性.将属性EvaluateAsExpression更改为 true.单击Expression属性旁边的省略号按钮以打开 Expression Builder.将表达式设置为@[User::Source_Folder] + "\" + @[User::Source_FileName].Select the variable
Source_FilePathand clickF4to view the properties. Change the propertyEvaluateAsExpressionto true. Click the ellipsis button next to theExpressionproperty to open the Expression Builder. Set the expression to@[User::Source_Folder] + "\" + @[User::Source_FileName].您可以只有一个变量来存储源文件路径.我通常更喜欢将源文件夹和文件名分开.
You could have just one variable to store the source file path. I usually prefer to keep the source folder and the file name separate.
将脚本任务拖放到控制流选项卡上.双击脚本任务打开脚本任务编辑器.在脚本标签页，点击
ReadOnlyVariables旁边的省略号按钮，选择以下变量，因为我们将在脚本任务代码中使用这些变量.Drag and drop a script task onto the control flow tab. Double-click the script task to open the script task editor. On the script tab page, click the ellipsis button next to
ReadOnlyVariablesand select the following variables, because we will use these variables in the script task code.User::Source_FilePath User::Target_Folder User::Backup_FolderName点击Edit Script...按钮并输入如下所示的代码.
Click the Edit Script... button and enter the code as shown below.
脚本任务代码执行以下操作:
The script task code does the following:
它将检查源文件路径是否有效.如果无效，它会抛出一条消息并退出进程.
It will check if the source file path is valid or not. If invalid, it will throw a message and quit the process.
它将检查目标文件夹是否有效.如果无效，它会抛出一条消息并退出进程.
It will check if the target folder is valid or not. If invalid, it will throw a message and quit the process.
如果源文件路径和目标文件夹有效，则逻辑将遍历目标文件夹内子文件夹中源文件名的所有匹配位置.如果有匹配的文件，它会将目标文件复制到备份文件夹，然后用源文件覆盖目标文件.
If source file path and target folder are valid, the logic will loop through all the matching locations of the source file name in the sub-folders within target folder. If there are matching files, it will copy the target file to backup folder and then will overwrite the target file with source file.
脚本任务将发出适当的信息，以便您可以在 SSIS 2012 中的 SQL Server Data Tools (SSDT) 或 SSIS 2005 中的 Business Intelligence Development Studio (BIDS) 上的进度/执行结果选项卡中跟踪状态 -SSIS 2008 R2.
The script task will emit the appropriate information so you can track the status within the progress/execution results tab on SQL Server Data Tools (SSDT) in SSIS 2012 or Business Intelligence Development Studio (BIDS) in SSIS 2005 - SSIS 2008 R2.
使用系统；使用 System.Data;使用 Microsoft.SqlServer.Dts.Runtime；使用 System.Windows.Forms;使用 System.IO;
using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.IO;
命名空间 ST_523853dfbc0d4123be43383671f8a6c6{[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]公共部分类 ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase{公共无效主(){尝试{bool fireAgain = false;字符串备份文件夹=字符串.空；string backupFilePath = string.Empty;
namespace ST_523853dfbc0d4123be43383671f8a6c6 { [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { public void Main() { try { bool fireAgain = false; string backupFolder = string.Empty; string backupFilePath = string.Empty;
string sourcFilePath = Dts.Variables["User::Source_FilePath"].Value.ToString(); string targetFolder = Dts.Variables["User::Target_Folder"].Value.ToString(); string backupFolderName = Dts.Variables["User::Backup_FolderName"].Value.ToString(); if (String.IsNullOrEmpty(sourcFilePath) || !File.Exists(sourcFilePath)) { // Check if a valid source file path was specified on the package variable Dts.Events.FireError(101, "Source path error", String.Format("You need to set a valid source file path in the package variable 'Source_FilePath'. Invalid path: '{0}'", sourcFilePath), string.Empty, 0); Dts.TaskResult = (int)ScriptResults.Failure; } else if (String.IsNullOrEmpty(targetFolder) || !Directory.Exists(targetFolder)) { // Check if a valid target folder was specified on the package variable Dts.Events.FireError(102, "Target folder error", String.Format("You need to set a valid target folder location in the package variable 'Target_Folder'. Invalid folder: '{0}'", targetFolder), string.Empty, 0); Dts.TaskResult = (int)ScriptResults.Failure; } else { FileInfo sourceInfo = new FileInfo(sourcFilePath); // Loop through each file that matches the name of the source file foreach (string targetFilePath in Directory.EnumerateFiles(targetFolder, sourceInfo.Name, SearchOption.AllDirectories)) { FileInfo targetInfo = new FileInfo(targetFilePath); backupFolder = Path.Combine(targetInfo.Directory.FullName, backupFolderName); backupFilePath = Path.Combine(backupFolder, backupFolderName); // If the backup folder does not exist in the folder within root target folder, create the backup folder. if (!Directory.Exists(backupFolder)) { Directory.CreateDirectory(backupFolder); Dts.Events.FireInformation(401, "Backup folder created", String.Format("Backup folder '{0}' was created.", backupFolder), string.Empty, 0, ref fireAgain); } // Archive the target file to the backup folder. File.Copy(targetFilePath, backupFilePath, true); Dts.Events.FireInformation(402, "Target file archived", String.Format("Target file '{0}' was archived to the backup folder '{1}'.", targetFilePath, backupFolder), string.Empty, 0, ref fireAgain); // Overwrite the target file with the source file. File.Copy(sourcFilePath, targetFilePath, true); Dts.Events.FireInformation(403, "Target file overwritten", String.Format("Target file '{0}' was overwritten with the source file '{1}'.", sourcFilePath, targetFilePath), string.Empty, 0, ref fireAgain); } Dts.TaskResult = (int)ScriptResults.Success; } } catch (Exception ex) { Dts.Events.FireError(100, "Unhandled exception", ex.ToString(), string.Empty, 0); } } #region ScriptResults declaration enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion }}
#Region "Imports" Imports System Imports System.Data Imports System.Math Imports System.IO Imports Microsoft.SqlServer.Dts.Runtime #End Region <Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _ <System.CLSCompliantAttribute(False)> _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Public Sub Main() Try Dim fireAgain As Boolean = False Dim backupFolder As String = String.Empty Dim backupFilePath As String = String.Empty Dim sourcFilePath As String = Dts.Variables("User::Source_FilePath").Value.ToString() Dim targetFolder As String = Dts.Variables("User::Target_Folder").Value.ToString() Dim backupFolderName As String = Dts.Variables("User::Backup_FolderName").Value.ToString() If String.IsNullOrEmpty(sourcFilePath) OrElse Not File.Exists(sourcFilePath) Then ' Check if a valid source file path was specified on the package variable Dts.Events.FireError(101, "Source path error", String.Format("You need to set a valid source file path in the package variable 'Source_FilePath'. Invalid path: '{0}'", sourcFilePath), String.Empty, 0) Dts.TaskResult = ScriptResults.Failure ElseIf String.IsNullOrEmpty(targetFolder) OrElse Not Directory.Exists(targetFolder) Then ' Check if a valid target folder was specified on the package variable Dts.Events.FireError(102, "Target folder error", String.Format("You need to set a valid target folder location in the package variable 'Target_Folder'. Invalid folder: '{0}'", targetFolder), String.Empty, 0) Dts.TaskResult = ScriptResults.Failure Else Dim sourceInfo As FileInfo = New FileInfo(sourcFilePath) ' Loop through each file that matches the name of the source file For Each targetFilePath As String In Directory.EnumerateFiles(targetFolder, sourceInfo.Name, SearchOption.AllDirectories) Dim targetInfo As FileInfo = New FileInfo(targetFilePath) backupFolder = Path.Combine(targetInfo.Directory.FullName, backupFolderName) backupFilePath = Path.Combine(backupFolder, backupFolderName) ' If the backup folder does not exist in the folder within root target folder, create the backup folder. If Not Directory.Exists(backupFolder) Then Directory.CreateDirectory(backupFolder) Dts.Events.FireInformation(401, "Backup folder created", String.Format("Backup folder '{0}' was created.", backupFolder), String.Empty, 0, fireAgain) End If ' Archive the target file to the backup folder. File.Copy(targetFilePath, backupFilePath, True) Dts.Events.FireInformation(402, "Target file archived", String.Format("Target file '{0}' was archived to the backup folder '{1}'.", targetFilePath, backupFolder), String.Empty, 0, fireAgain) ' Overwrite the target file with the source file. File.Copy(sourcFilePath, targetFilePath, True) Dts.Events.FireInformation(403, "Target file overwritten", String.Format("Target file '{0}' was overwritten with the source file '{1}'.", sourcFilePath, targetFilePath), String.Empty, 0, fireAgain) Next Dts.TaskResult = ScriptResults.Success End If Catch ex As Exception Dts.Events.FireError(100, "Unhandled exception", ex.ToString(), String.Empty, 0) Dts.TaskResult = ScriptResults.Failure End Try End Sub #Region "ScriptResults declaration" Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum #End Region End Class当提供无效的源文件路径时，包将抛出以下错误消息:
When an invalid source file path is provided, the package will throw the below error message:
当提供无效的目标文件夹时，包将抛出以下错误消息:
When an invalid target folder is provided, the package will throw the below error message:
当源和目标位置有效时，包将成功执行.在这个例子中，
When source and target locations are valid, the package will execute successfully. In this example,
Target_1下有一个备份文件夹，所以没有创建文件夹，但文件被复制到备份文件夹.Target_2中没有匹配的文件，因此未采取任何措施.- 在
Target_3中创建了备份文件夹，该文件被复制到目标位置，然后被源文件覆盖.
包执行后目标位置如下图所示.
The target location will look like as shown below after the package execution.
Target |- Target_1 | |- Archive | |- Sample_File.txt | |- Sample_File.txt |- Target_2 |- Target_3 |- Archive |- Sample_File.txt |- Sample_File.txt这篇关于我应该如何执行此文件/文件夹分发任务；SSIS 或命令脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
