Introduction SQL Server 2012 and all previous versions lacking native JSON support, same theory is true for their ETL Platform SSIS. If you are one of them who wants to export JSON from SQL Server or any other RDBMS like MySQL, Oracle then you can easily output complex JSON using any of the following components. Five ways of […]
Introduction Many times, you need to search and replace content in files (single or multiple) using SSIS. If you are not a C# or VB.NET programmer, you may find yourself at a dead end, struggling to figure out how to achieve this in SSIS. If you have this need, then you can use the Advanced […]
Introduction Many times during your ETL process, you receive files that are encoded differently than you expect. Example: all files are in UTF-16, but your application expects them in UTF-8. Or sometimes files are in ASCII format, and you want to convert to UTF-8. In this post, you will see how to use the Advanced […]
Introduction In our previous article, we saw how to call a REST API or a SOAP Web Service in SSIS. In this article, we will focus only on how to perform an HTTP POST in SSIS using a RESTful approach. HTTP Protocol supports many methods, also referred to as verbs (e.g. GET, PUT, POST, HEAD, DELETE). […]
Introduction In this article you will learn how to Setup Amazon Redshift Cluster in few clicks. You will also learn how to set Inbound and Outbound Firewall Rules so you can access Redshift Cluster from outside of AWS Network (e.g. from your corporate network or your home). By default Redshift Cluster cannot be access from outside […]
Format Date – SSIS YYYYMMDD from date Often, you need to create file paths with a timestamp using an SSIS Expression. You can use SSIS Expressions Below is a simple expression that will produce a file path with the current timestamp
|
1 2 3 4 |
"C:\\Folder\\MyFile_" + (DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE()) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) + ".json" |
Sample Output C:\Folder\MyFile_20151231.json Convert Date – SSIS YYYYMMDD to Date
|
1 2 3 4 5 6 7 8 9 |
--// For Control Flow //--- (DT_DATE)(SUBSTRING(@[yourDateAsStr],1,4) + "-" + SUBSTRING(@[yourDateAsStr],5,2) + "-" + SUBSTRING(@[yourDateAsStr],7,2)) --// For Data Flow //-- (DT_DATE)(SUBSTRING([field],1,4) + "-" + SUBSTRING([field],5,2) + "-" + SUBSTRING([field],7,2)) |
Set the SSIS […]
Introduction Zendesk is one of the most popular customer service platforms available today. Zendesk offers a REST API to interact with its cloud-based service programmatically (to manage or read data). The problem is that not everyone is a programmer, and not everyone has time to learn how to write C# or JAVA code to access a […]
Introduction In our previous article we saw how to load data into Redshift using SSIS or load data into Redshift using ZappyShell Redshift Command Line In this article we will walk through various steps to Extract/UNLOAD Redshift Data into SQL Server using Amazon S3 Storage Task and ExecuteSQL Task for Amazon Redshift. Below is the […]
What is JSONPath expression JSONPath expression is an expression language to filter JSON Data. Its a similar concept to XPath expression in XML but has limited features compared to XPath. If you are extracting data from REST API Services using SSIS JSON / XML Source or ODBC JSON / XML Driver then you will quickly realize […]
Introduction In this post, you will learn how to Parse Cookies from a Response and Pass Cookies to any Web Requests. SSIS PowerPack v1.8.2 introduced a new feature that allows sending and receiving cookies with HTTP Web Request/Response. We will use the SSIS REST API Task to parse cookie values and then send cookies along with […]