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 have requirement to search and replace in files (single or multiple file) content using SSIS. If you are not C# or VB.net programmer then you may find yourself at dead end struggling how to achieve this in SSIS. If you have this need then you can use Advanced File System Task […]
Introduction Many times during your ETL process you receive files which are in different encoding than you expect. Example all files are in UTF-16 format but your application expect them to be 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 […]
Introduction In our previous article we saw how to call REST API or SOAP Web Service in SSIS. In this article we will only focus how to perform HTTP POST in SSIS using RESTful approach. HTTP Protocol supports many methods also referred as verb (e.g. GET, PUT, POST, HEAD, DELETE). Based on this verb, target […]
Introduction ZappySys provides high-performance drag-and-drop connectors for MongoDB Integration. In our previous post, we discussed how to query/load MongoDB data (Insert, Update, Delete, Upsert). In this post, you will see how to query MongoDB by date (or ISODate) using the SSIS MongoDB Source. To see full details about possible query syntax, see online help. MongoDB doesn’t have SQL Like query language […]
In this post you will learn how to use SSIS Script Task (C# Code) to detect specific URL found or not (Detect HTTP 404 Error) If you using SSIS JSON Source or our REST API Task then if URL/Document you trying to read not found then server may return 404 (Not Found) Exception. In order […]
Format Date – SSIS YYYYMMDD from date Many times you have to create file paths with timestamp using SSIS Expression. You can use SSIS Expressions Below is simple expression which will produce file path with 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 Download FREE SSIS Components 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 SSIS […]
Introduction Zendesk is one the most popular customer service platforms available in the market today. Zendesk offers REST API so you can interact with their cloud based service programmatically (manage or read data). The problem is not every one is programmer and not everyone has time to learn how to write C# or JAVA code […]
Introduction In this article you will how to Read Twitter data in SSIS using SSIS JSON Source and SSIS REST API Web Service Task. You will also learn about latest OAuth 2.0 Protocol to simplify REST API access. Twitter REST API Authentication In order to fetch any data from twitter using OAuth REST API calls […]
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 […]