SSIS YYYYMMDD date format - Use datetime in file path

SSIS YYYYMMDD date format using SSIS expression

Posted on November 4, 2015May 9, 2017 by ZappySys

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

"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"

"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

--// 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))

--// 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 […]

How to read Zendesk data in SSIS with REST API

Posted on October 27, 2015June 9, 2021 by ZappySys

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 […]

How to read Twitter data in SSIS using REST API Call

Posted on October 22, 2015December 7, 2019 by ZappySys

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 […]

JSONPath Expression Examples – JSON Cheatsheet (SSIS / ODBC)

Posted on October 21, 2015May 19, 2023 by ZappySys

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 […]

Working with HTTP Cookies in SSIS or ODBC

Posted on October 21, 2015October 28, 2021 by ZappySys

Introduction In this post you will learn how to Parse Cookies from Response and Pass Cookies to any Web Requests. SSIS PowerPack v1.8.2 Introduced new feature to send and receive cookies from HTTP Web Request/Response. We will use SSIS REST API Task to parse cookie values and then send cookies along with HTTP Web Request. Cookies […]

SSIS REST API Task - Response Header Mapping Grid

SSIS REST API Task – Mapping Response Header to Variable

Posted on October 21, 2015December 18, 2015 by ZappySys

In this post you will learn how to save Response header value to SSIS variable using SSIS REST API Task SSIS PowerPack v1.8.2 Introduced new feature for mapping Response Header to variable. When you click Test Request/Response button all response headers will be parsed into Response Header Grid with default [NOT-MAPPED] status. If you wish […]

SSIS export to excel dynamically (supports multiple tables)

Posted on October 19, 2015December 7, 2019 by ZappySys

Limitation of SSIS Excel Source/Destination SSIS comes with out of the box support for read/write to Excel. But its very restrictive if you want to make things dynamic because any metadata inside DataFlow cannot be changed at runtime. Here are few problems using native Excel Source or Destination. Metadata cannot be changed at runtime Datatype […]

How to read OData in SSIS – REST API Example

Posted on September 10, 2015September 29, 2020 by ZappySys

Introduction In this article we will learn how to use SSIS JSON Source Component to fetch JSON data from OData service and perform OData Pagination of large result. In OData specification data may come in JSON format or XML format. For XML format check this article. This article will cover mostly OData JSON API example. REST […]

SSIS dataflow Expression alternative - Using Variable Placeholders

How to set SSIS Data Flow component property using expression and variable

Posted on August 28, 2015March 19, 2022 by ZappySys

Introduction SSIS PowerPack supports different ways to make things dynamic. Sometimes you have to consume JSON Data from Dynamic URL. There are 3 different ways you can make JSON Source URL Dynamic Use Expression on DirectPath property of JSON Source (SSIS Data Flow Expression) Use variable placeholders directly inside URL (Only works when you use […]

Calling REST API in SSIS with Basic Authentication / Userid-Password (BASE64 Authorization header)

Posted on August 27, 2015September 8, 2023 by ZappySys

Introduction In this blog post you will explain how to pass basic credentials (i.e. UserID/Password) along with your web request. You will also learn about setting up Authorization Header for HTTP Web Request in Base64 manually. To learn more about how to consume / call REST API in SSIS check this article. HTTP Authorization Header […]