Introduction to read and write data to HTML in SSIS
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 to store the list of system files of a folder into a variable and then we will use the SSIS Logging Task to store the variable in an HTML file.
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 to read the data from the HTML table and export to SQL Server using the OLEDB Destination.
We will be using this ZappySys SSIS PowerPack component to make things work:
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.
Requirements
- First, you will need SSDT installed.
- Secondly, ZappySys PowerPack for SSIS.
- Finally, SQL Server installed.
Getting started
How to save the list of system files into SQL an HTML table
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.
- 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:
SSIS list files
- In addition, we will create a variable of type object named listfiles that will store the files information:
SSIS object variable
- 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:
SSIS save variable to HTML
- In addition, it is possible to send the variable values into the Execution Log (SSIS output), to a file, to a MessageBox:
SSIS output variable to log, message box and file variable to read and write data to HTML in SSIS
- In this example, we will show the values into an HTML file named listfiles.html
SSIS save variable iHTMLhtml
- After executing the package, you will be able to see the HTML with a list of files, names, size, modification and creation information:
ssis list files
How to copy data from an HTML table to SQL Server
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.
- In order to start, we will Drag and drop a data flow task and double click it:
SSIS data flow
- Next, in the Data Flow task, use the HTML Table Source and join to the OLEDB Destination:
SSIS HTML table SQL server
- Also, in URLs enter the path of the HTML file created in the first example:
SSIS HTML table read
- In addition, go to the OLEDB Source, specify the SQL Server name, database, and log in to it.
In the name of table or view press new to create a new table:
12345678910111213141516CREATE TABLE [dbo.listfiles] ([_rowNumber] int,[_groupName] nvarchar(255),[FullPath] nvarchar(255),[Name] nvarchar(255),[Size] int,[AgeInDays] int,[LastEditInDays] int,[LastModifiedDate] datetime,[CreationDate] datetime,[LastModifiedDateUtc] datetime,[CreationDateUtc] datetime,[Extension] nvarchar(255),[IsReadOnly] nvarchar(255),[FolderPath] nvarchar(255))
SSIS OLEDB create table
- Also, go to Mappings page to map all the columns.
SSIS map columns
- Finally, run the package and you will have all the data exported.
How to export SQL Server results to an HTML in SSIS
The following example will show how to export the results of an SQL query into an HTML file.
- 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:
SSIS SQL query results
- Secondly, go to the Result Set page and in the Result Name, set 0 and in Variable name, create a new variable:
ssis sql result to variable
- Also, create a variable of type object:
SSIS object data type
- Finally, use the ZS Logging Task to save the results in a File. Specify the Log file path and insert the variable:
SSIS save to HTML file
Conclusion about read and write data to HTML in SSIS
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
If you liked the tools, you can download them HERE.
