Lookup,Incremental load(Latest Records),removedup

How TO EXECUTE dowloded pacakage

Write data from multiple tables to single flat file.

Take 3 DataFlow Tasks in Control Flow

and One Flat File Destination
(click on the image for larger view)

Handling extra/missing columns in Delimited Flat file

Delimited Flat files (CSV) are one of the important non-relational source for SSIS ETL but if we get robust file all the time then we can use inbuild flat file source in SSIS. Surprisingly we are not that much lucky and get the data in which columns are missing (or we can say extra columns) like as follow...

Flat File INPUT

 

we Want Flatfile OUTPUT

 

3. Flat file source which reads all data into one column and then splits them based on index of the comma in a derived column.

Here I'm giving you the demonstration to handle such file with the help of derived column.We have to write expression (sometime complex) to get the proper data in column format. In the above example I am getting all the data as String and after splitting we are saving it as string only (If you want to change the data type then you can use Data Conversion task). If number of columns are more then this approach is not recommended, In that case you should go with the first 2 approach.

Step1: Inside the Data Flow Task (DFT), Select a Flat File Source and Derived column.

Step2: Configure the FlatFile Source in such a way that you will get all input data in single column.

Note:  We are keeping the Column name as "C" in the example.

Step3: Double click the derived column and use the following logic.

• For separating the column "C1" from the column "C", we need to fetch the data from position 1 to first occurrence of delimiter " , " so finding the first delimiter we have to use FINDSTRING SSIS function (Returns the location of the specified occurrence of a string within a character expression).

• SUBSTRING(C,1,FINDSTRING(C,",",1) - 1)

• Here we have to do "-1" because we have to exclude delimiter from the column value.

• For separating the column "C2" from the column "C", we have to fetch the data after the first delimiter to 2nd delimiter but before that we have to check whether we have 2nd delimiter in place

• FINDSTRING(C,",",2) != 0 ? SUBSTRING(C,FINDSTRING(C,",",1) + 1,FINDSTRING(C,",",2) - FINDSTRING(C,",",1) - 1) : SUBSTRING(C,FINDSTRING(C,",",1) + 1,LEN(C) - FINDSTRING(C,",",2))  

• For separating the column "C3" from the column "C"
• FINDSTRING(C,",",2) == 0 ? "NULL" : (FINDSTRING(C,",",3) != 0 ? SUBSTRING(C,FINDSTRING(C,",",2) + 1,FINDSTRING(C,",",3) - FINDSTRING(C,",",2) - 1) : "NULL")

• For separating the Last column "C4" from the column "C",we have to fetch the data after the third delimiter to end of the row. and for calculating the last position of the row, we have to use LEN SSIS function
• FINDSTRING(C,",",3) == 0 ? "NULL" : SUBSTRING(C,FINDSTRING(C,",",3) + 1,LEN(C) - FINDSTRING(C,",",2))

Step4: And result would be as expected..

SCD in SSIS

Create table Scdemp
 (EmpBusinessId int,EmpName Varchar(20),age int,MaritalStatus varchar(10),ManagerId int)
 go
 Insert Into scdemp values (1010,'M',45,'M',1008)


Create table DestScdemp
 (EmpBusinessId int,EmpName Varchar(20),age int,MaritalStatus varchar(10),ManagerId int,EmpSurrogateId Int,StartDate Date,Enddate DATE )

Insert multiple rows into a table

CREATE TABLE Sample1(UserId INT IDENTITY,UserName VARCHAR(8),Sales Decimal(6,2))
GO
INSERT INTO Sample1 (UserName, Sales)
 SELECT 'Ram', 100.00
UNION ALL
 SELECT 'Raheem ', 700.00
UNION ALL
 SELECT 'Robert', 400.00
UNION ALL
 SELECT 'Sachin', 800
 UNION ALL
 SELECT 'Pawan', 1000.00
 UNION ALL
 SELECT 'Mahesh',50.00

 Select * from sample1

Merge Join

OLEDB SOURCE1:

Create Table OrderMaster
(
      OrderID Varchar(5),
      ClientCode Varchar(10),
      ProjectName varchar(25),
      OrderDate DateTime
)
INSERT INTO OrderMaster
SELECT 'A1001','CHEM02','NY WaterField',GETDATE() UNION ALL
SELECT 'A1002','ACCU01','Plainfield Soil',GETDATE()
GO

Flat File Source:

OrderID,SampleNumber,Matrix
A1001,A1001-01,Water
A1001,A1001-02,Soil
A1002,A1002-01,Water
A1002,A1002-02,Water

Oledb Destination:

Create Table OrderMasterAndDetail
(
OrderID Varchar(5),
ClientCode Varchar(10),
SampleNumber Varchar(10),
Matrix Varchar(10)
)
GO

Alter & Update Table with Null

select * from Prod

update Prod set Quantity=NULL where rowguid='D4544D7D-CAF5-46B3-AB22-5718DCC26B5E'

Alter table Prod alter column quantity smallint null

 

DELETE DUP REC"S

DELETE DUP REC"S

create table A (I int, J int, K int)

insert A select 1,1,1
insert A select 1,1,1
insert A select 1,1,1
insert A select 1,1,1
insert A select 2,1,1
insert A select 2,1,1
insert A select 2,2,1
insert A select 2,2,2
insert A select 2,2,3
insert A select 2,2,4
insert A select 3,3,3
insert A select 3,3,3
insert A select 3,3,3

select * from A

1. simple table recreate


select * into B from A where 1 = 0
insert B select distinct * from A
begin tran
delete A
insert A select * from B
commit tran
drop table B

2. delete and replace duplicates

select * into B from A where 1 = 0
insert B select i,j,k from A group by i,j,k having count(*) > 1
begin tran
delete A from B where A.i = B.i and A.j = B.j and A.k = B.k
insert A select * from B
commit tran
drop table B

3. delete duplicates one by one leaving single row
set rowcount 1
select 1
while @@rowcount > 0
delete A
where 1 < (select count(*) from A a2 whereA.i = a2.i and A.j = a2.j and A.k = a2.k)
set rowcount 0

4. delete all duplicates for one row value at a time
select *, cnt = 0 into B from A where 1 = 0
declare @rowcount int
select 1
while @@rowcount <> 0
begin
insert B (i,j,k,cnt) select top 1 i,j,k, count(*) - 1 from A group by i,j,k having count(*) > 1
select @rowcount = cnt from B
set rowcount @rowcount
delete

 

deleting duplicates.

CREATE TABLE DUPTest (A int not null,
B int not null,
C int not null,
ID int not null identity) on [Primary]
GO
INSERT INTO DUPTest (A,B,C) VALUES (1,1,1)
INSERT INTO DUPTest (A,B,C) VALUES (1,1,1)
INSERT INTO DUPTest (A,B,C) VALUES (1,1,1)

INSERT INTO DUPTest (A,B,C) VALUES (1,2,3)
INSERT INTO DUPTest (A,B,C) VALUES (1,2,3)
INSERT INTO DUPTest (A,B,C) VALUES (1,2,3)

INSERT INTO DUPTest (A,B,C) VALUES (4,5,6)
GO
Select * from DUPTest
GO

Delete from DUPTest where ID <
(Select Max(id) from DUPTest t where DUPTest.A = t.A and
DUPTest.B = t.B and
DUPTest.C = t.C)
GO
Select * from DUPTest
GO

Removing duplicate records by using Oracle's ROWID

Select col1,col2,col3,col4 from TableName where ROWID in (select max(ROWID) from TableName group by col1,col2)

Copy a table from one database to another database

Copy a table from one database to another database in another ms sql server using ..

Copy a table from one database to another database in another ms sql server using sql server management studio :
If your table was in the same ms sql server but in another database, you would copy it using t-sql like this :

SELECT * INTO NewTable FROM existingdb.dbo.existingtable

SELECT * INTO NewTable FROM Sarma.dbo.DimAccount

However, if your database is in another ms sql server, you can do this :
Connect to your database using management studio. Right click on your table and select Script Table As > Create To > New Query Editor Window
Connect to the other database you want the table to be copied, create a new query and paste the sql previously generated. Run the query and you are done!

SELECT *
INTO TargetDatabase.dbo.MyTable
FROM SourceDatabase.dbo.MyTable

SQL QUERIES

CREATE TABLE Person

(

LastName varchar(20),

FirstName varchar(20),

Address varchar(50))

sp_help Person

ALTER TABLE Person ADD City varchar(30)

SElect * from Person

ALTER table Person ALTER COLUMN firstname varchar(30)

INSERT INTO Person Values('Hetland','Camilla','Hegabakka 24','Sandness')

INSERT INTO Person Values('Pettersen','Kari','Storgt 20','Stavanger')

INSERT INTO Person (LastName,Address) values ('Rasmussen','Storgt 67')

Select * from Person

Update Person SET FirstName='Nina' WHERE LastName='Rasmussen'

Renaming a TABLE:

sp_rename 'Person', 'Persons'

alter table persons add year date

insert into persons (year) values ('1951')

select * from persons

delete from persons where YEAR ='1951-01-01'

update persons set year='1976-08-21' where firstname='Nina'

Functions available to create EXPRESSIONS

Functions available to create EXPRESSIONS

Mathematical Functions
FUNCTION	Result	DataType
ROUND( 2.23 , 2 )	2	Integer
ROUND( 2.53 , 2 )	3	Integer
CEILING(2.23 )	3	Numeric
FLOOR(2.9)	2	Numeric
ABS(3.2)	3	Numeric
ABS(3.9)	4	Numeric
ABS(-3.2)	3	Numeric
ABS(-3.9)	4	Numeric

String Functions
FUNCTION	Result	DataType
LOWER( "ABD" )	abc	String
UPPER( "abd" )	ABD	String
LTRIM( "    abc   " )	"abc   "	String
RTRIM( "   abc   " )	"   abc"	String
TRIM( "   abc   " )	"abc"	String
REVERSE( "abc" )	"cba"	String
RIGHT( abc, 2 )	"bc"	String
SUBSTRING( "abcde", 2 , 3 )	"bcd"	String
REPLICATE("a",3)	"aaa"	String
FINDSTRING("abcd","ab",1)	"ab"	String
REPLACE("Apple", "pp", "n app")	"An apple"	String

Date/Time Functions
FUNCTION	Result	DataType
DATEADD( "mm", 1, (DT_DATE)"12/24/2009" )	1/24/2010 0:00	DateTime
DATEADD( "dd", -1, (DT_DATE)"12/24/2009" )	12/23/2009 0:00	DateTime
DATEADD( "yy", 1, (DT_DATE)"12/24/2009" )	12/24/2010 0:00	DateTime
DATEDIFF( "dd", (DT_DATE)"12/24/2009", DT_DATE)"01/26/2010" )	33	Integer
DATEDIFF( "mm", (DT_DATE)"12/24/2009", (DT_DATE)"01/26/2010" )	1	Integer
DATEDIFF( "yy", (DT_DATE)"12/24/2009", (DT_DATE)"01/26/2010" )	1	Integer
DATEPART( "dd", (DT_DATE)"12/24/2009" )	24	Integer
DATEPART( "mm", (DT_DATE)"12/24/2009" )	12	Integer
DATEPART( "yy", (DT_DATE)"12/24/2009" )	2009	Integer
DAY( (DT_DATE)"12/24/2009" )	24	Integer
MONTH( (DT_DATE)"12/24/2009" )	12	Integer
YEAR( (DT_DATE)"12/24/2009" )	2009	Integer

NULL Functions
FUNCTION	Result	DataType
ISNULL( «expression» )	TRUE/FALSE	Boolean
NULL(DT_DATE)	NULL	DateTime

Type Casts

FUNCTION

(DT_STR, «length», «code_page»)

(DT_WSTR, «length»)

(DT_NUMERIC, «precision», «scale»)

(DT_DECIMAL, «scale»)

Operators
FUNCTION	Comments
?:	If then Else -> "Condition"?"True Action" : "False Action"
&&	Logical AND
||	Logical OR