SSIS DTSX 包在 SQL Server 代理作业中丢失数据

发布于 2024-11-25 03:37:22 字数 421 浏览 1 评论 0原文

我有一个 SSIS 作业，它从输入 FTP 目录获取文件并将内容解压缩到临时目录中以进行进一步处理。我在输入 FTP 目录上使用 For-Each 目录循环，其中调用了 Winzip。命令行调用的参数使用 SSIS 表达式生成器配置输入文件的文件名和临时目录的名称。它在表达式生成器中看起来像这样：

 -e  " +  @[User::InputFolder] + "\\" + @[User::CurrentInputFileName] + "  " + @[User::TempFolder]

现在，当我从 VS2005 本地运行它并通过网络访问相关文件时，这一切都工作得很好。但是当我部署到应用程序服务器时，我从另一端得不到任何信息；它就挂在那里。一路上，变量似乎正在消失。

有人有什么想法吗？有人在 SSIS 包中看到过类似的行为吗？

原文

I have an SSIS job which takes files from an input FTP directory and unzips the contents into a temp directory for further processing. I'm using a For-Each directory loop over the input FTP directory, and within that there is a call to Winzip. The argument to the command-line call is configured with the filename of the input file and the name of the temp directory using the SSIS Expression builder. It looks like this in Expression Builder:

 -e  " +  @[User::InputFolder] + "\\" + @[User::CurrentInputFileName] + "  " + @[User::TempFolder]

Now, this all works fine and dandy when I run it locally from VS2005 and access the relevant files over the network. But when I deploy to the application server, I get nothing out of the other side; it just hangs there. The variables seem to be being lost along the way.

Any ideas anyone? Has anyone seen similar behaviour from an SSIS package?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

同尘 2024-12-02 03:37:22

当您在 Business Intelligence Development Studio (BIDS) 中运行 SSIS 包时，它会在您的凭据下执行，并且您可能对文件提取到的文件夹拥有完全权限。当您安排包在 SQL Server 代理中运行时，该包将在 SQL 代理服务帐户 下运行，该帐户可能无权访问该文件夹。

如果您有权登录托管数据库的服务器，请登录该服务器并双击该包。当您双击该包时，它将带来 dtexec 实用程序。在实用程序中运行程序包，如果它从服务器内成功运行，则问题很可能与权限有关。
要检查的另一件事是变量 @[User::InputFolder] 是否有可能在路径中包含空格。在这种情况下，当传递给 Winzip 命令行参数时，该参数应该用双引号括起来。
是否有可能无法从托管 SQL 作业的服务器访问 FTP 服务器？由于防火墙阻止，我遇到了这样的问题。
在包上启用Logging选项将有助于捕获错误消息。这是链接< /a> 解释了如何启用日志记录。
我认为问题中提供的表达式不是完整的表达式，因为这是行不通的。它必须以双引号开头才能正确评估。 表达式： "-e " + @[User::InputFolder] + "\\" + @[User::CurrentInputFileName] + " " + @[User::TempFolder]

以下步骤描述了如何设置 SQL 作业来运行 SSIS 包。如果您可以通过 SQL Server Management Studio 访问 SQL Server Agent，则以下是使用图形用户创建作业的步骤界面。这些步骤展示了如何创建 SQL 作业以使用 SQL 代理服务帐户运行 SSIS，以及如何创建代理以使用不同的凭据在不同的环境下运行。如果问题是由于权限引起的，在不同的帐户下运行可能会帮助您解决问题。

转到 SQL Server Management Studio。展开 SQL Server Agent 并右键单击“作业”，然后选择新建作业...，如屏幕截图 #1 所示。
提供名称和所有者，默认情况下将是创建作业的帐户，但您可以根据您的要求进行更改。如果您愿意，请分配一个类别并提供描述。请参阅屏幕截图#2。
在“步骤”部分，单击新建...，如屏幕截图 #3 所示。
在“新建作业步骤”对话框中，提供步骤名称。从类型中选择SQL Server 集成服务包。默认情况下，此步骤将在 SQL Agent Service Account 下运行。选择包源作为文件系统，然后通过单击省略号浏览到包路径。这将填充包路径。请参阅屏幕截图#4。如果您不希望在 SQL 代理服务帐户下执行该步骤，请参阅步骤 #8 - 9< /strong> 了解如何使用不同的帐户。
如果您有该包的 SSIS 配置文件 (.dtsConfig)，请单击配置选项卡并添加配置文件，如屏幕截图 #5 所示。< /p>
单击“确定”，即可出现步骤 1 中的包，如屏幕截图 #6 所示。同样，您可以创建不同的步骤。
创建作业后，您可以右键单击该作业并选择将作业脚本编写为 -->创建到-->新的查询编辑器窗口用于生成脚本，如屏幕截图#7所示。
要在不同帐户下运行 SSIS 步骤，请在 Management Studio 上导航到 Security -->右键单击 Cedentials -->选择 New Credential...，如屏幕截图 #8 所示。
在新建凭据对话框中，提供您要在 SQL 作业中执行 SSIS 步骤的凭据名称、Windows 帐户和密码。请参阅屏幕截图#9。将创建凭据，如屏幕截图 #10 所示。
接下来，我们需要创建一个代理。在 Management Studio 上，导航到 SQL Server Agent -->代理 -->右键单击 SSIS 包执行 -->选择“新建代理...”，如屏幕截图 #11 所示。
在“新建代理帐户”窗口中，提供代理名称，选择新创建的凭据，提供说明并选择 SQL Server Integration Services Package，如屏幕截图 #12 所示。应创建代理帐户，如屏幕截图 #13 所示。
现在，如果您返回到 SQL 作业中的步骤，您应该会在运行方式下拉列表中看到新创建的代理帐户。请参阅屏幕截图 #14。

希望有帮助。

屏幕截图 #1：

屏幕截图 #2：

屏幕截图#3：

屏幕截图 #4：

屏幕截图 #5：

屏幕截图 #6：

屏幕截图 #7：

屏幕截图 #8：< /strong>

屏幕截图#9：

屏幕截图 #10:

屏幕截图 #11:< /strong>

屏幕截图 #12：

屏幕截图 #13：

屏幕截图 #14：

When you run SSIS package in Business Intelligence Development Studio (BIDS), it executes under your credentials and you might have full permissions to the folder where the files are extracted to. When you schedule the package to run in SQL Server Agent, the package will run under SQL Agent Service Account, the account may not have access to the folder.

If you have permissions to log into the server hosting the database, then log into the server and double-click on the package. When you double-click on the package, it will bring the dtexec utility. Run the package in the utility, if it runs successfully from within the server then the issue is more likely related to the permissions.
Another thing to check is whether there is a possibility that the variable @[User::InputFolder] might contain a space in the path. In that case, that parameter should be enclosed within a double quotes when passed to the Winzip command line arguments.
Is there a possibility that the FTP server is not accessible from the server hosting the SQL job? I have encountered such an issue due to firewall block.
Enabling Logging option on the package would help to capture the error messages. Here is a link that explains how to enable logging.
I assume that the expression provided in the question is not the complete expression because that won't work. It has to begin with a double quote something like this to evaluate correctly. Expression: "-e " + @[User::InputFolder] + "\\" + @[User::CurrentInputFileName] + " " + @[User::TempFolder]

Following steps describe how to set up an SQL job to run SSIS package.If you have access to SQL Server Agent through SQL Server Management Studio, here are the steps to create a job using the Graphical User Interface. The steps show how to create an SQL job to run SSIS using SQL Agent Service Account and also how to create a proxy to run under a different using different credentials. If the problem is due to permissions ,running under different account might help you to fix the problem.

Go to SQL Server Management Studio. Expand SQL Server Agent and right-click on Jobs, then select New Job... as shown in screenshot #1.
Provide a name and Owner by default will be the account that creates the job but you can change it according to your requirements. Assign a Category if you would like to and also provide a description. Refer screenshot #2.
On the Steps section, click New... as shown in screenshot #3.
On the New Job Step dialog, provide a Step name. Select SQL Server Inegration Services Package from Type. This step will run under SQL Agent Service Account by default. Select the package source as File system and browse to the package path by clicking on ellipsis. This will populate the Package path. Refer screenshot #4. If you don't want the step to execute under the SQL Agent Service Account, then refer the steps #8 - 9 to know how you can use a different account.
If you have a SSIS configuration file (.dtsConfig) for the package, click on the Configurations tab and add the Configuration file as shown in screenshot #5.
Click OK and there is the package in step 1 as shown in screenshot #6. Similarly, you can create different steps.
Once the job has been created, you can right-click on the job and select Script Job as --> CREATE To --> New Query Editor Window to generate the script as shown in screenshot #7.
To run the SSIS step under different account, on the Management Studio, navigate to Security --> right-click on Cedentials --> select New Credential... as shown in screenshot #8.
On the New Credential dialog, provide a Credential name, Windows account and Password under which you would like to execute SSIS steps in SQL jobs. Refer screenshot #9. Credential will be created as shown in screenshot #10.
Next, we need to create a proxy. On the Management Studio, navigate to SQL Server Agent --> Proxies --> right-click on SSIS Package Execution --> select New Proxy... as shown in screenshot #11.
On the New Proxy Account window, provide a Proxy name, select the newly created Credential, provide a description and select SQL Server Integration Services Package as shown in screenshot #12. Proxy account should be created as shown in screenshot #13.
Now, if you go back to the step in SQL job, you should see the newly created Proxy account in the Run as drop down. Refer screenshot #14.