{"id":3584,"date":"2018-05-22T22:16:51","date_gmt":"2018-05-22T22:16:51","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=3584"},"modified":"2018-05-30T14:37:19","modified_gmt":"2018-05-30T14:37:19","slug":"read-and-write-data-to-html-in-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-and-write-data-to-html-in-ssis\/","title":{"rendered":"How to read and write data to HTML in SSIS"},"content":{"rendered":"

Introduction to read and write data to HTML in SSIS<\/h2>\n

\"HTML<\/a>In this article, we will show how to send values from an SSIS Variable to an HTML file. We will use the SSIS Advanced File System Task <\/a> to store the list of system files of a folder into a variable and then we will use the SSIS Logging Task<\/a> to store the variable in an HTML file.<\/p>\n

In the second part, we will read values from an HTML table and export the data into SQL Server. We will use the HTML file created in the first example. We will also use the ZS HTML Table Source<\/a> to read the data from the HTML table and export to SQL Server using the OLEDB Destination.<\/p>\n

We will be using this\u00a0ZappySys SSIS PowerPack<\/a>\u00a0component to make things work:
\n

\n\n\n\n\n\n

\n\"SSIS<\/a><\/td>\n		SSIS Logging Task (Free)<\/a><\/td>\n<\/tr>\n

\n\"SSIS<\/a><\/td>\n		SSIS HTML Table Source<\/a><\/td>\n<\/tr>\n

\n\"Advanced<\/a><\/td>\n		Advanced SSIS File System Task
\n<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

Finally, we will show how to read data from a SQL Server query and store into an HTML file. We will use the Execute SQL Task combined with the ZS SSIS Logging Task<\/a>.<\/p>\n

Requirements<\/h2>\n
    \n
  1. First, you will need SSDT installed.<\/li>\n
  2. Secondly, ZappySys PowerPack for SSIS.<\/a><\/li>\n
  3. Finally, SQL Server installed.<\/li>\n<\/ol>\n

    Getting started<\/h2>\n

    How to save the list of system files into SQL an HTML table<\/h3>\n

    In the first example, we will use our ZappySys Advanced File System to copy a list of the system files in a folder. This list of files will be stored in an object variable and then we will save to a file.<\/p>\n

      \n
    1. In the first place, we will use the Advanced File System Task and select the Get file list ADO.net DataTable action. We will list the files of the c:\\sql folder and save the results into a variable:\n
    2. In addition, we will create a variable of type object named listfiles that will store the files information:\n
    3. Also, we will use the ZS Logging Tasks and join with the ZS Advanced File. The ZS Logging Task will be used to store the variable into a file:\n
    4. In addition, it is possible to send the variable values into the Execution Log (SSIS output), to a file, to a MessageBox:\n
    5. In this example, we will show the values into an HTML file named listfiles.html\n
    6. After executing the package, you will be able to see the HTML with a list of files, names, size, modification and creation information:\n
      \"SSIS<\/a>

      ssis list files<\/p><\/div><\/li>\n<\/ol>\n

      How to copy data from an HTML table to SQL Server<\/h3>\n

      The next example will save data from a file named listfiles.html created in the previous example with an HTML table. The example will export the table to SQL Server. However, with our HTML Table Source, you can export to any destination.<\/p>\n

        \n
      1. In order to start, we will Drag and drop a data flow task and double click it:\n
      2. Next, in the Data Flow task, use the HTML Table Source and join to the OLEDB Destination:\n
      3. Also, in URLs enter the path of the HTML file created in the first example:\n
      4. In addition, go to the OLEDB Source, specify the SQL Server name, database, and log in to it.
        \nIn the name of table or view press new to create a new table:<\/p>\n
        CREATE TABLE [dbo.listfiles] (\r\n[_rowNumber] int,\r\n[_groupName] nvarchar(255),\r\n[FullPath] nvarchar(255),\r\n[Name] nvarchar(255),\r\n[Size] int,\r\n[AgeInDays] int,\r\n[LastEditInDays] int,\r\n[LastModifiedDate] datetime,\r\n[CreationDate] datetime,\r\n[LastModifiedDateUtc] datetime,\r\n[CreationDateUtc] datetime,\r\n[Extension] nvarchar(255),\r\n[IsReadOnly] nvarchar(255),\r\n[FolderPath] nvarchar(255)\r\n)<\/pre>\n
        \"ssis<\/a>
        SSIS OLEDB\u00a0create table<\/p><\/div><\/li>\n
      5. Also, go to Mappings page to map all the columns.\n
      6. Finally, run the package and you will have all the data exported.<\/li>\n<\/ol>\n
        How to export SQL Server results to an HTML in SSIS<\/h3>\n
        The following example will show how to export the results of an SQL query into an HTML file.<\/p>\n
          \n
        1. In order to start, we will use the Execute SQL Task. Specify the option Full result set and create an OLEDB connection specifying the Server Name, database name and the select statement to a table:\n
        2. Secondly, go to the Result Set page and in the Result Name, set 0 and in Variable name, create a new variable:\n
        3. Also, create a variable of type object:\n
        4. Finally, use the ZS Logging Task to save the results in a File. Specify the Log file path and insert the variable:\n
          \"ssis<\/a>
          SSIS save to HTML file<\/p><\/div><\/li>\n<\/ol>\n
          Conclusion about\u00a0read and write data to HTML in SSIS<\/h2>\n
          To conclude, in this article, we learned how to export a list of files system properties and names to SQL Server. How to read an HTML table to SQL Server and finally how to export SQL Server results to an HTML in SSIS. There are nice tools to read and write data to HTML in SSIS<\/p>\n
          If you liked the tools, you can download them HERE.<\/a><\/p>\n
          References<\/h2>\n