使用PowerShell和SSIS导出SQL Server FILESTREAM对象

最新推荐文章于 2021-07-30 12:14:19 发布

culuo4781

最新推荐文章于 2021-07-30 12:14:19 发布

阅读量924

点赞数

文章标签：数据库 java python 大数据 linux

原文链接：https://www.sqlshack.com/export-sql-server-filestream-objects-with-powershell-and-ssis/

版权

In this series of articles on SQL Server FILESTREAM (see TOC at bottom), we explored various ways to store unstructured data in the file system with the metadata in SQL Server tables. If we have a large number of objects in the file system, it is advisable to use the fast disk for storage purpose. It is faster and provides better IO in comparison with the traditional file system.

在有关SQL Server FILESTREAM的系列文章（请参见底部的TOC）中，我们探索了使用SQL Server表中的元数据在文件系统中存储非结构化数据的各种方法。如果文件系统中有大量对象，建议使用快速磁盘进行存储。与传统文件系统相比，它更快并且提供了更好的IO。

In my previous article, Importing SQL Server FILESTREAM data with SSIS packages, we used SSIS packages to import the files into SQL Server FILESTREAM table. Sometimes, we want to export FILESTREAM objects to different drive. We might need to copy the complete data or particular data from the file system. Even though you might think that we can copy the files directly by accessing the file system. Let us first look at the issues with this approach.

在上一篇文章“ 使用SSIS包导入SQL Server FILESTREAM数据”中，我们使用SSIS包将文件导入到SQL Server FILESTREAM表中。有时，我们想将FILESTREAM对象导出到其他驱动器。我们可能需要从文件系统复制完整数据或特定数据。即使您可能认为我们可以通过访问文件系统直接复制文件。让我们首先看一下这种方法的问题。

Suppose we have the following files stored in the SQL Server FILESTREAM container. First, we do not know the exact name of the files. SQL Server does not store the files in their original name. Instead, it assigns the names as per an internal mechanism.

假设我们在SQL Server FILESTREAM容器中存储了以下文件。首先，我们不知道文件的确切名称。 SQL Server不会以其原始名称存储文件。而是根据内部机制分配名称。

Let us say we just want to copy few files from SQL Server FILESTREAM container to local disk. We copied the above-highlighted objects into different drive.

假设我们只想将几个文件从SQL Server FILESTREAM容器复制到本地磁盘。我们将上面突出显示的对象复制到了不同的驱动器中。

Once we have copied the files into a different drive, we’ll try to open it, but the OS does not recognize the extension of these files. If we try to open it, it asks us to select the program in which we want to open the file.

将文件复制到其他驱动器后，我们将尝试打开它，但操作系统无法识别这些文件的扩展名。如果尝试打开它，它将要求我们选择要在其中打开文件的程序。

We do not necessarily know the appropriate type of this file, therefore; let us open with the Notepad. It does not open in the correct format because it is an image file. Further complicating things, it is difficult to identify the file type because it does not have any file extension.

因此，我们不一定知道此文件的适当类型。让我们用记事本打开。由于它是图像文件，因此无法以正确的格式打开。更复杂的是，由于文件类型没有文件扩展名，因此很难识别文件类型。

In this article, we want to demonstrate how to export the images as per the following sources and destinations.

在本文中，我们要演示如何根据以下来源和目的地导出图像。

SQL Instance: .\SQL2019 SQL实例 ：。\ SQL2019
Source FILESTREAM Database: FileStreamDemoDB_test 源FILESTREAM数据库 ：FileStreamDemoDB_test
Source FILESTREAM table: [Tbl_Support_Documents] 源FILESTREAM表 ：[Tbl_Support_Documents]
Destination Folder: ‘C:\sqlshack\Objects’ 目标文件夹： “ C：\ sqlshack \ Objects”
Object Count: 343 物件数量 ：343

PowerShell is a very powerful management tool that can interact with SQL Server easily and do administrative tasks with minimal configurations. We can use PowerShell to interact with the SQL Server FILESTREAM and export the objects into the destination folder.

PowerShell是一种非常强大的管理工具，可以轻松与SQL Server进行交互，并以最少的配置即可完成管理任务。我们可以使用PowerShell与SQL Server FILESTREAM进行交互并将对象导出到目标文件夹中。

To fully understand this step, we need to review the PowerShell script in parts. Once we have covered the script, we will use the combined script to export the objects in Windows PowerShell. I have taken this script from Microsoft TechNet and modified it as per our requirement.

为了完全理解此步骤，我们需要分部分回顾PowerShell脚本。覆盖脚本后，我们将使用组合的脚本在Windows PowerShell中导出对象。我已从Microsoft TechNet中获取此脚本，并根据我们的要求对其进行了修改。

Define the parameters for connection to SQL Server: In this part, we will define the variable and pass the required input. In the following command, you will notice the SQL Server instance details, database name in which FILESTREAM table exists, destination folder. We also need to define the buffer size, let us define is as 16,384 bytes.
定义用于连接到SQL Server的参数：在这一部分中，我们将定义变量并传递所需的输入。在以下命令中，您将注意到SQL Server实例详细信息，FILESTREAM表所在的数据库名称，目标文件夹。我们还需要定义缓冲区大小，让我们定义为16,384字节。

$Server = ".\sql2019";              
$Database = "FileStreamDemoDB_test";
$Dest = "C:\Export\";     
$bufferSize = 8192;

Specify the SQL Server statement to fetch details from SQL Server FILESTREAM table: In the Second step, we need to specify the query to retrieve information from the FILESTREAM table
指定SQL Server语句以从SQL Server FILESTREAM表中获取详细信息：在第二步中，我们需要指定查询以从FILESTREAM表中检索信息

$Sql = "SELECT [Document_Name],[DocumentBin]
  FROM [FileStreamDemoDB_test].[dbo].[Tbl_Support_Documents] 
";

It is the same query that we can run in SSMS to retrieve the information from the FILESTREAM table.

这与我们可以在SSMS中运行以从FILESTREAM表中检索信息的查询相同。

Open a database connection and specify the credentials to connect with the instance: In this part, we want to connect to the Database with the windows authentication (integrated security). In this article, we are using windows authentication.
打开数据库连接并指定用于与实例连接的凭据：在这一部分中，我们要使用Windows身份验证（集成安全性）连接到数据库。在本文中，我们使用Windows身份验证。

Note: user should have sufficient permission to connect to the specified Database instance and query the FILESTREAM table.

注意：用户应具有足够的权限才能连接到指定的数据库实例并查询FILESTREAM表。

$con = New-Object Data.SqlClient.SqlConnection; 
$con.ConnectionString = "Data Source=$Server;" + 
 "Integrated Security=True;" + 
 "Initial Catalog=$Database"; 
  $con.Open();

Write a message to show that the export process started: We can use the PowerShell command ‘write-output’ to print the required message.
编写一条消息以显示导出过程已开始：我们可以使用PowerShell命令'write-output'打印所需的消息。

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Export FILESTREAM objects Started ...");

Create the command and execute the reader: Now, we will open a new command to execute the query, we specified in step 2, on SQL Server Database. We might also specify the connection timeout in seconds however here for simplicity we will avoid using that.
创建命令并执行阅读器：现在，我们将在SQL Server数据库上打开一个新命令以执行在步骤2中指定的查询。我们也可以指定连接超时（以秒为单位），但是为简单起见，我们将避免使用它。

$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con; 
$rd = $cmd.ExecuteReader();

Create a byte array and loop through the records in the table: In this step, we are going to loop through each row in the SQL Server FILESTREAM table. We will use ‘System.IO.FileStream’ FILESTREAM class to provide synchronous read-write along with the synchronous method.
创建一个字节数组并遍历表中的记录：在这一步中，我们将遍历SQL Server FILESTREAM表中的每一行。我们将使用“ System.IO.FileStream” FILESTREAM类提供同步读写以及同步方法。

$out = [array]::CreateInstance('Byte', $bufferSize) 
  
  While ($rd.Read()) 
{ 
 try 
  { 
   Write-Output ("Exporting Objects from FILESTREAM container: {0}" -f $rd.GetString(0)); 
   # New BinaryWriter 
   $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write; 
   $bw = New-Object System.IO.BinaryWriter $fs; 
 
   $start = 0; 
   # Read first byte stream 
   $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1); 
   While ($received -gt 0) 
   { 
    $bw.Write($out, 0,      $received); 
    $bw.Flush(); 
    $start += $received; 
    # Read next byte stream 
    $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1); 
   } 
   $bw.Close(); 
   $fs.Close(); 
  } 
  catch 
  { 
   Write-Output ($_.Exception.Message) 
  } 
  finally 
  { 
   $fs.Dispose();         
  }
 }

Closing the objects: We need to close all the objects in this step and print the success message.
关闭对象：在此步骤中，我们需要关闭所有对象并打印成功消息。

$rd.Close(); 
$cmd.Dispose(); 
$con.Close(); 
 
Write-Output ("Finished");
Read-Host -Prompt "Press Enter to exit"

Now let us combine all parts of the script in a single file.

现在，让我们将脚本的所有部分组合到一个文件中。

Note: You need to change the connection parameters and the destination folder path as per your requirement before executing the script. The complete script to copy the SQL Server FILESTREAM data into file system is as below.

注意：在执行脚本之前，您需要根据需要更改连接参数和目标文件夹路径。将SQL Server FILESTREAM数据复制到文件系统中的完整脚本如下。

Launch the Windows PowerShell from the start menu.

从开始菜单启动Windows PowerShell。

Now execute the query in PowerShell. It will start copying the files from the SQL Server FILESTREAM to the desired folder specified in the script. Script execution may take time depending upon the number of objects, their size, network bandwidth. You get the printed message in the PowerShell window for each file that is copied to the folder.

现在在PowerShell中执行查询。它将开始将文件从SQL Server FILESTREAM复制到脚本中指定的所需文件夹。脚本执行可能需要一些时间，具体取决于对象的数量，它们的大小，网络带宽。您会在PowerShell窗口中看到复制到该文件夹的每个文件的打印消息。

Once the script completes the files export from FILESTREAM container, you get the message ‘Finished’. We need to press ‘Enter’ to exit.

一旦脚本完成了从FILESTREAM容器的文件导出，您将收到消息“完成”。我们需要按“ Enter”退出。

We can verify the files from the destination folder. Below you can the files are now present in this folder with the actual file name and the extension. You can open the files, and OS recognize these files. Therefore, you can see the icon image of each file type as well.

我们可以验证目标文件夹中的文件。现在，您可以在下面的文件中包含实际文件名和扩展名的文件。您可以打开文件，操作系统可以识别这些文件。因此，您也可以看到每种文件类型的图标图像。

使用SSIS包导入和导出FILESTREAM对象 (Use an SSIS package to Import and Export FILESTREAM objects)

In the previous article, Importing SQL Server FILESTREAM data with SSIS packages, we created an SSIS package to import the files into SQL Server FILESTREAM tables. We want to use the same package to export the objects into the destination folder as well.

在上一篇文章“ 使用SSIS包导入SQL Server FILESTREAM数据”中，我们创建了一个SSIS包以将文件导入SQL Server FILESTREAM表。我们也想使用相同的包将对象导出到目标文件夹中。

Therefore, we will use this SSIS package to execute the PowerShell script as well. Drag the ‘Execute Process Task’ in the control flow task.

因此，我们还将使用此SSIS包来执行PowerShell脚本。将“执行流程任务”拖到控制流任务中。

Double click on ‘Execute Process Task’ and it opens the execute process task editor. In this, rename the task to reflect its usage. We renamed it to ‘Export FILESTREAM Object’.

双击“执行过程任务”，它将打开执行过程任务编辑器。在此，重命名任务以反映其用法。我们将其重命名为“导出文件流对象”。

We saved the PowerShell script with ‘.PS1’ extension to save it in the Windows PowerShell Script format.

我们以“ .PS1”扩展名保存了PowerShell脚本，将其保存为Windows PowerShell脚本格式。

In the execute process task editor, go to Process and provide the below parameters.

在执行流程任务编辑器中，转到“流程”并提供以下参数。

Executables:C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe 可执行文件 ：C：\ Windows \ System32 \ WindowsPowerShell \ v1.0 \ powershell.exe
Arguments: -ExecutionPolicy ByPass -command “. ‘C:\sqlshack\Draft articles\ExportFILESTREAM.PS1′” 参数：-ExecutionPolicy ByPass -command“。 'C：\ sqlshack \ Draft文章\ ExportFILESTREAM.PS1'”

Click ‘Ok’, and our SSIS package looks as shown below.

单击“确定”，我们的SSIS包如下所示。

Before we execute the SSIS package to do the import and export together, truncate the existing FILESTREAM table. We have only 13 files in the source folder ‘ C:\images’ to keep this execute quick. We will also remove all the files from the ‘C:\Export’ folder.

在执行SSIS包一起进行导入和导出之前，请截断现有的FILESTREAM表。源文件夹“ C：\ images”中只有13个文件，以确保快速执行。我们还将从“ C：\ Export”文件夹中删除所有文件。

Truncate table [FileStreamDemoDB_test].[dbo].[Tbl_Support_Documents]

The follow task will execute in the SSIS package.

Follow任务将在SSIS包中执行。

Import files from ‘C:\Images’ folder to SQL Server FILESTREAM table Tbl_Support_Documents
将文件从“ C：\ Images”文件夹导入到SQL Server FILESTREAM表Tbl_Support_Documents
Export files from FILESTREAM container to ‘C:\Export’ folder using the PowerShell Script.
使用PowerShell脚本将文件从FILESTREAM容器导出到“ C：\ Export”文件夹。

Now, we are ready to execute this SSIS package. Execute this package and monitor it. Once it reaches the ‘Export FILESTREAM object’, it launches the PowerShell Script window as shown below.

现在，我们准备执行此SSIS包。执行此程序包并对其进行监视。到达“导出FILESTREAM对象”后，将启动PowerShell脚本窗口，如下所示。

The SSIS package is successful now for both the import and the export tasks.

现在，对于导入和导出任务，SSIS包均已成功。

Let us perform the validations. We can see data in the FILESTREAM table (13 rows). It shows that Import is successful.

让我们执行验证。我们可以在FILESTREAM表（13行）中看到数据。它表明导入成功。

Now go to the export folder ‘ C:\Export’ and view the 13 files. It shows that the export task is also successful.

现在转到导出文件夹“ C：\ Export”并查看13个文件。它表明导出任务也成功。

结论 (Conclusion)

We explored the task to export FILESTREAM objects into the file system directory using aPowerShell script and the SSIS package. We will continue covering the more topics on the SQL Server FILESTREAM in upcoming articles.

我们探索了使用aPowerShell脚本和SSIS包将FILESTREAM对象导出到文件系统目录中的任务。我们将在以后的文章中继续介绍有关SQL Server FILESTREAM的更多主题。