Auto Align SSIS diagram in dataflow

trim and change date time if in numeric format ssis and sql

trim ssis


LEN(TRIM(my_numeric_value)) == 0 ? "0.00" : TRIM(my_numeric_value)



change date time format


Eq :
input : 20190731
output: 2019-07-31 00:00:00.000

SUBSTRING([FILE-DTE],1,4) + "-" + SUBSTRING([FILE-DTE],5,2) + "-" + SUBSTRING([FILE-DTE],7,2)



change datetime and  numeric format to varchar

'IN1035_' +CAST(col1 AS nvarchar) + '_' + 
FORMAT([LoadDate],'yyyyMMddHHmmss')
AS BT_ID


using CASE:
CASE
WHEN [Code] LIKE '9%' THEN  [aaDate]
WHEN [Code] LIKE '0%' THEN  DATEADD(d,-1,[aaDate])

END AS [PostingDate]

ELT process

What’s ETL again?

When designing the ETL process it’s good to think about the three fundamental things it needs to do:

• Extract data from the external data sources such as line-of-business systems, CRM systems, relational databases, web services, and SharePoint lists.
• Transform the data. This includes cleansing the data and converting it to a OLAP-friendly data model. The OLAP-friendly data model traditionally consists of dimension and fact tables in a star or snowflake schema and closely maps SSAS’s dimensional model (SSAS stands for SQL Server Analyses Services ).
• Load the data so that it can be quickly accessed by querying tools such as reports. In practice this implies processing SSAS cubes.

Staging :

There’s a quick and easy way to create staging tables from within SSIS. When you add a destination data source to a data flow, connect it to incoming data and then edit the destination’s properties, you will be asked to select a destination table. At that point you also have the option of creating a new table. Doing so will create a database table with all the right columns.

get newly inserted identity column value sql

Table X has as its primary key the column Y which is defined as  int IDENTITY(1,1). After inserting a row into table X, how would you SELECT the newly inserted value of column Y?

Answer:      SELECT IDENT_CURRENT (‘X’) AS Current_Identity;

3.            Table R contains columns ProductID AND OrderDate, amongst others, and contains many rows of data? How would you return just rows 20 to 30 of the data (when ordered by OrderDate and Product ID).

Answer:

SELECT * FROM R order by Product ID, OrderDate  desc LIMIT 19,11