SSIS package

Transfer data from .csv file to database table using temptable

Create new SSIS Integration Services Project

It will create new SSIS package ‘Package.dtsx’

Inside Package.dtsx

Inside Control Flow tab

Add one Execute SQL task from toolbox option-“Control Flow Items”

double click inside General tab in the option “SQL Statement”

write a script inside SQL Statement

//check for temptable if exist drop it and create new one.

“IF object_ID(‘tempdb..##temptable’) IS NOT NULL
DROP TABLE ##temptable
ELSE
CREATE TABLE [tempdb].dbo.##temptable (Header VARCHAR(200), Value VARCHAR(200))”

ON same tab “Control Flow”

add Data Flow Task

Double click on “Data Flow Task”

It will open inside “Data Flow” tab

Now here Drop “Flat File Source” from toolbox  option-“Data Flow Sources”

which will take .csv from source

Now on same tab “Data Flow” add “OLE DB Destination” from toolbox option – “Data Flow Destination”

Now again go back to Control Flow drag and drop “Execute SQL task”

which is having following SQL statements

“Declare @Datevar varchar(50)
declare @MV decimal(30,10)
select @Datevar = Value from ##temptable
where Header  = ‘Account Number’
select @MV = Value from ##temptable
where Header  = ‘2268510’
SET @Datevar = REPLACE(@Datevar, ‘Market Value’,”)

INSERT INTO mydata.dbo.ProgressDailyMarketValue (Date, MarketValue) values (CONVERT(Datetime, @Datevar), @MV)

DROP TABLE ##temptable”

which is actually taking values from temtable and inserted into database.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s