我应该如何执行此文件/文件夹分发任务; 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.file复制到这些文件夹的目标文件夹下。
- 但是，只有复制/更换，如果已经有一个target.file现有的。
- 如果在target.file'存在，复制和文件替换到备份文件夹如果备份文件夹存在。
- 如果没有，先创建备份文件夹。
推荐答案
下面是写在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:
有是你想复制源文件。
Source |- Sample_File.txt下面是目标文件夹结构。
Here is the target folder structure.
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:\SSIS\Files\Source\ Target_Folder String D:\SSIS\Files\Target\选择变量的
Source_FilePath的并点击F4以查看属性。更改属性的EvaluateAsEx pression的设置为true。点击旁边的省略号按钮的防爆pression的属性来打开的防爆pression生成器的。将前pression到@ [用户:: SOURCE_FOLDER] +\\\\+ @ [用户:: 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].您可能只是一个变量来存储源文件路径。我通常preFER保留源文件夹和文件名是分开的。
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并选择以下变量，因为我们将在脚本任务code使用这些变量。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点击编辑脚本... 的按钮，并输入code，如下图所示。
Click the Edit Script... button and enter the code as shown below.
脚本任务code执行以下操作：
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或商业智能开发套件（BIDS）于2005年SSIS SQL Server数据工具（SSDT）的进展情况/执行结果选项卡中的地位 - 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
{
公共无效的主要（）
{
尝试
{
布尔fireAgain = FALSE;
字符串backupFolder =的String.Empty;
字符串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的，该文件被复制到目标位置，然后用源文件覆盖。
- there was a backup folder under
Target_1, so no folder was created but file was copied to backup folder. - There was no matching file in
Target_2, so no action was taken. - Backup folder was created in
Target_3, the file was copied to target location and then overwritten with source file.
的目标位置将类似于作为包执行后，如下图所示。
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屋！
