{"id":6707,"date":"2019-04-06T10:22:55","date_gmt":"2019-04-06T10:22:55","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=6707"},"modified":"2019-04-11T14:48:02","modified_gmt":"2019-04-11T14:48:02","slug":"ssis-amazon-s3-storage-task-examples-download-upload-move-delete-files-folders","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/ssis-amazon-s3-storage-task-examples-download-upload-move-delete-files-folders\/","title":{"rendered":"SSIS Amazon S3 Storage Task examples (Download, Upload, Delete Files \/ Folders)"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

\"Amazon<\/a>In this article, we will show\u00a0SSIS Amazon S3 Storage task examples<\/strong>. Uploading files using Amazon S3 Storage Task or downloading files are common requirements in SSIS. In order to work with Amazon S3 Storage, Microsoft SSIS includes the Amazon S3 Storage Task that allows to upload files to Amazon S3 Storage, download files, creating local and remote directories an more. In this article, we will show how to use the\u00a0SSIS Amazon S3 Storage task<\/a>\u00a0included in ZappySys\u00a0SSIS PowerPack<\/a>.<\/p>\n

Amazon S3 Storage Task can be used to perform various operations with Amazon S3 Storage objects (files and containers) (e.g. Download, Upload, Copy, Move, Rename, Delete, etc). You can also get single property of any Amazon file or get a list of Amazon files as ADO.net Table and which can be easily looped through using ForEachLoop Task. Most of the operations in this task support recursive search using wild card pattern (e.g. Myfile*.txt).<\/p>\n

Prerequisite
\n<\/span><\/span><\/h2>\n
    \n
  1. First, you will need to have SSIS installed<\/li>\n
  2. Secondly, make sure to have SSDT<\/li>\n
  3. You have obtained Amazon S3 account access key \/ secret key.<\/li>\n
  4. Finally, do not forget to install\u00a0ZappySys\u00a0SSIS PowerPack<\/a><\/li>\n<\/ol>\n

    What is Amazon S3 Storage<\/span><\/h2>\n

    Amazon Simple Storage Service (Amazon S3) is an object storage service that offers industry-leading scalability, data availability, security, and performance. This means customers of all sizes and industries can use it to store and protect any amount of data for a range of use cases, such as websites, mobile applications, backup and restore, archive, enterprise applications, IoT devices, and big data analytics. Amazon S3 provides easy-to-use management features so you can organize your data and configure finely-tuned access controls to meet your specific business, organizational, and compliance requirements. Amazon S3 is designed for 99.999999999% (11 9\u2019s) of durability, and stores data for millions of applications for companies all around the world.<\/p>\n

    Getting Started<\/h2>\n

    In order to start, we will show several examples. ZappySys includes an SSIS Amazon S3 Storage Task<\/a> that will allow you to access\u00a0files \/ folders from Amazon S3 Storage to the Local machine, Upload files(s) to Amazon S3 Storage. It will also support Delete, Rename, List, Get Property, Copy, Move, Create, Set Permission \u2026 and many more operations. Here we are showing you is, How to upload, download, move and delete files \/ folders from Amazon S3 Storage.<\/p>\n

    SSIS Amazon S3 Storage Task Examples<\/h2>\n

    Let\u00b4s start with an example. In this SSIS Amazon S3 Storage Task File task example, we will upload, download, move and delete files \/ folders from Amazon S3 Storage.<\/p>\n

      \n
    1. First\u00a0of All, Drag and Drop Amazon S3 Storage Task from SSIS Toolbox and double click it to edit\n
    2. The following options are available in Amazon S3 Storage Task:\n
    3. You can also overwrite files and folder, skip all, overwrite if source is older, overwrite if the file size is different, rename files and more:\n
    4. Let’s select the Download Amazon file(s) to local directory<\/strong>. Now select\/add The Source path, Check Recursive Scan to get all the sub files and sub folders and it’s files and add Target Path, This option will download files. In Path AccessMode we can use Direct to write the path directly or to use an SSIS variable.\n
    5. That’s it now execute the package and it will download all the files and sub folders from the Amazon S3 Storage Task to Local File Machine.<\/li>\n
    6. Now Let’s configure the task to upload the local directory files and folder to Amazon S3 Storage like below and execute the package. And also in this example i created the variable and stored target folder path in it and used that variable Target Path Variable.\n
    7. Let’s configure the task to delete Amazon S3 Storage files like below and execute the package.\n
    8. Let’s configure the task to move Amazon S3 Storage files to another directory like below and execute the package.\n
    9. That’s it like upper examples you can configure Amazon S3 Storage Task to use it’s other options also.<\/li>\n
    10. Let’s see other SSIS Amazon S3 Storage Task examples in below sections<\/li>\n<\/ol>\n

      Working with advanced filters<\/h2>\n

      In order to understand filters, we will create an example for you. This time we will create SSIS Amazon S3 Storage task example to show how to exclude some files from the downloading process.<\/p>\n

        \n
      1. In addition, it is also possible to add advanced filters to the SSIS Amazon S3 Storage task to exclude or include files and folders. The following example shows how to use the Advanced filters. In the Amazon S3 Storage\u00a0Task,\u00a0<\/strong>go to the Advanced Filter page and in Exclude RegX we will write \\.exe$ to exclude the file with exe extension to be downloaded. You can use RegX (regular expressions) in the advanced filter:\n
        \"Advanced<\/a>

        Advanced filters<\/p><\/div><\/li>\n<\/ol>\n

         <\/p>\n

        Working with wildcards in\u00a0SSIS Amazon S3 Storage task (Multiple files only)<\/h2>\n

        In order to understand wildcards, the new example will show how to work with Wildcards will help you to work with multiple files or folder with common characteristics.<\/p>\n

          \n
        1. The following example shows how to copy all the files will all the extensions in the source folder. We use the *.*, which means that any file name with any extension will be downloaded:\n
        2. If we want to download all the files with extension txt, use the following Path:\n
          \/source\/*.txt;<\/pre>\n<\/li>\n
        3. Another example would be to download all the files with the following names: file1.txt, file2.txt and file3.txt.\u00a0 We could use the following Path:\n
          \/source\/file?.txt<\/pre>\n<\/li>\n<\/ol>\n
          <\/h2>\n
          Working with SSIS expressions and variables in Amazon S3 Storage Task<\/strong><\/h2>\n
          In order to understand expressions, the following example will show how to work with SSIS expressions combined with variables in the SSIS Amazon S3 Storage task. In this example we will delete files in Amazon S3 Storage using variables and expressions.<\/p>\n
          \n
          ZappySys Providers simple and powerful Placeholders. You can still use SSIS expression language. Placeholders allow direct Variable Use easy to see compared to hidden SSIS Expression (C# style). Placeholders have simple embedding use of variables.<\/p>\n<\/div>\n
            \n
          1. \u00a0First, we will first create a variable in SSIS. In this example the file name is MyFile<\/strong> and the Value file1<\/strong>.\n
          2. As a second step, we will write in path the value\u00a0\\destination\\{{User.MyFile}}.txt.\u00a0<\/strong>This will concatenate the folder destination with the variable MyFile. The task will delete the file1.txt from the Amazon S3 Storage folder.\n
          3. The next example will show how to create a Amazon S3 Storage file using expressions and variables in SSIS:\n
          4. Content to write is the content of the file. In\u00a0Target,\u00a0<\/strong>go to path and write the following: For more information on placeholders check this post<\/a>\u00a0and also check this post<\/a>.\n
            \/destination\/{{User::FileName,MM-dd-yyyy}}<\/pre>\n<\/li>\n
          5. We are using a variable in SSIS named FileName of type DateTime. We are concatenating the Amazon S3 Storage folder destination with the variable. As you can see, the expressions with ZappySys are very simple.<\/li>\n<\/ol>\n
            Example to verify if file exists in SSIS Amazon S3 Storage task<\/h2>\n
            Finally, we will verify if a file in Amazon S3 Storage exists or not using the SSIS Amazon S3 Storage task.<\/p>\n
              \n
            1. In order to start, we will use the SSIS variables, create a variable of Boolean type named FileExists. In\u00a0Storage Action,\u00a0<\/strong>select\u00a0Get Amazon file exist status.\u00a0<\/strong>In path, select the path of the file to verify if it exists or not.\n
            2. Secondly, we will use the\u00a0ZS Logging Task\u00a0<\/strong>to check the value of the variable File Exists. This task will show the value of the variable in the log. Drag and drop the task and join it with the ZS Amazon S3 Storage Task:\n
            3. In order to get the value, we will insert it in the Logging Task. To do that, insert the Variable and select the FileExists variable:\n
            4. Finally, run the package and verify the Output (you can use View and Output to see the output) and check the value of the Logging Task. If the file exists, the value will be true. If it does not exist, the value will be false.\n
              \"Verify<\/a>
              Verify the variable in Output<\/p><\/div><\/li>\n<\/ol>\n
              How to loop through Amazon S3 Storage files in SSIS<\/h2>\n
              You can also loop through files to perform custom action file by by. Amazon S3 Storage Task support Action to get File List as ADO.net Recordset<\/strong><\/p>\n
              To get File list from Amazon S3 Storage Server perform the following steps.<\/p>\n
                \n
              1. From Storage Action select Get Amazon files as ADO.net DataTable
                \n<\/strong><\/strong><\/p>\n
              2. Create new Amazon S3 Storage Connection by clicking New next to connection dropdown.<\/li>\n
              3. Select Source Path (e.g\u00a0 \\myroot\\folder\\*.txt\u00a0<\/strong> )<\/li>\n
              4. Select or create new SSIS Variable (e.g. varFiles<\/strong> ) which will hold file list result (Must be Object datatype variable).<\/li>\n
              5. Click OK to close.<\/li>\n
              6. Create 3 more SSIS variables to hold File Path (String), Name (String) and Size (Int32). We will use in the next step.<\/li>\n
              7. Now drag SSIS ForEach Loop Container Task from SSIS Toolbox<\/li>\n
              8. Edit Loop Task and Select Foreach ADO Enumerator<\/strong> from the Enumerator dropdown<\/li>\n
              9. Select variable (e.g.\u00a0varFiles)
                \n<\/strong><\/strong><\/p>\n
              10. Click on Variable Mappings Tab and configure like below.\n
              11. Drag ZS Logging Task and place inside Loop and Display variables<\/li>\n
              12. That’s it<\/li>\n<\/ol>\n
                For more information to loop through files on local machine using SSIS check this article<\/a>.<\/p>\n
                Conclusion<\/h2>\n
                To conclude, we can say that working with Amazon S3 Storage is now very simple. In this article, we looked at SSIS Amazon S3 Storage Task examples\u00a0on how to upload, download, delete files and folders. We used variables, wildcards and expressions. If you liked the tasks you can start using them by downloading SSIS PowerPack from our web site here<\/a>. We saw different SSIS Amazon S3 Storage Task examples to understand how to use the task in different scenarios.<\/p>\n
                Thanks for your time.<\/p>\n
                References<\/h2>\n