It is a very common business requirement that in our job we may receive documents in some particular formats, which the business team would like to have them converted into PDF files. Such requirements arise from many of the advantages when using PDF formats for file exchanges, such as portability, universal compatibility, reliability, security, size reduction, versatility, professionalism, etc. Given such common business requirement, it would be highly useful to have a tool that can pick up documents from a specific location and have them all converted to PDF files in an automated manner. Now with our KingswaySoft SSIS Productivity Pack, you could easily achieve this in an automated ETL process. The following would be the components that you can find our SSIS Productivity Pack product offering, which we will be showcasing how to achieve so.
- Premium File System Source component
- Premium Derived Column component
- Google Drive Destination component
- Premium File Transfer Task
The business scenario is, we assume that you have a process that would drop Microsoft Word document files in a local storage folder, from where our components would pick up the file and convert them into PDF files. In doing so, we will be uploading the document files to a Google Drive folder, and then use a Premium File Transfer task to download the document. At the time of the downloading, we will be requesting the files to be downloaded in PDF format. When processing the download, Google Drive platform performs an automatic document conversation (thanks to the versatile and robust documentation conversion features provided by Google Drive platform), and we leverage the Google Drive platform to achieve this conversion purpose that we just described above. Let's get started with the ETL process development
Configuration & Design
In our Google Drive Connection Manager, we have options to set the export MIME Type, as shown below, which would be pivotal for this task.
As you can see above, we have chosen “Documents” to be exported as “PDF” MIME Type. While in the connection manager, we have configured it to access the service using an OAuth token file. Once we have the connection manager ready, we could create your Data Flow Task as shown below.
The Premium File System Source component would be to read the files from the folder where the source Microsoft Word document files reside. And this in turn would give the actual binary File content column that would be used to map to the Google Drive Destination component in order to have the documents saved in Google Drive. We would add a Premium Derived Column component in between the Source and the Destination to add some additional details about the incoming documents before saving in Google Drive, such as the file name (if required - Or you could use the column "NameWithoutExtension" from the Premium File System Source component), the target or destination path, and the Mime Type. Please note that the name would need to be provided without the file extension. As you can also see, we are providing the Mime Type so that the incoming documents will be saved as the "Google Docs" type in Google Drive.
And now, the Google Drive Destination could be configured as below. In the General page, you could choose how the source documents should be written in Google Drive, and in the Columns page, we map each required field to achieve the writing.
Once this data flow is done, we have achieved the goal of importing the source documents into Google Drive. Now we would move out to the "Control flow" and add a Premium File Transfer Task which performs the download of just imported Google Doc files. Note that in the Premium File Transfer Task, we need to specify the source path with a ".pdf" extension. In doing so, we are requesting Google Drive to return a PDF file which matches the export MIME Type that we previously set in the Google Drive connection manager for the Documents type. Using such configuration, our software will be able to download the document as a .PDF file which is the design goal of this entire task.
Running the flow
The Data flow task that contains the Premium File System Source, Derived column component and the Google Drive Destination component can be chained in sequence with the Premium File Transfer Task as shown below, and in order for it pick all the files from a folder, you could easily parameterize the component and implement it within an SSIS foreach loop container. More information about parameterization can be found in one of our previous blog post.
When the Data flow path executes, you would see that the file has been populated in the Google Drive folder path you had specified.
And once the Premium File Transfer Task executes successfully, you would see the file converted and landed in the target path as a PDF file.
Conclusion
By using the above design, and by parameterizing and working with loop containers, you could easily have a package that would convert your documents to PDF format on a scheduled or automated way. This could be useful as a part of any processes or file archiving tasks that you have in your business. The design is not restricted to what we have shared below, and it can branch out as your requirement demands, and our components can be easily configured to handle them.