Create SSIS Package/Import package into SQL As object

1. File > New > Project > Business Intelligence Projects > Integration Service Project (project.dtsx)

2. ( Control Flow > Data Flow > EVent Handlers )

3. In Control flow > Drag Execute SQL Task -> Data Flow Task ( establish connection between them)

4. RC Execute Sql Task > Edit > Customize Properties.

5. Connection Type = OLEDB , Connection=Dev1.SLIM052208 , SQL Source Type=Direct Input ,

Bypassprepare =false (Let RDBMS prepare Sql Statement instead of task )

6. SQL Statement :

Go

if exists(select * from sys.tables where [name] like 'TS0503100Fee')

drop table TS0503100Fee

else

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[TS0503100Fee](

[ParcelNo] [nvarchar](14) NULL,

[TaxYear] [int] NULL

) ON Primary

7. In Data Flow, drag OLEDB Source > Data Conversion > OLEDB Destination

8. In OLE DB Source > RC > EDIT >specify OLE DB Connection Manager ( Where data comes from ) > COlumns (External Column & Output column )

9. In Data Conversion > Input column ( output cols from 8 ) , Output Alias (data name to destination ) , Change datatype fixs into destination database.

10.In OLEDB Destination > Connection Manager (destination databse and table ) > Mapping > Input column = Output Alias from 8 > Destination column (final table columns)

11.Rebuild project and debugging.

1. Bin > Deployment > project.isdm

2. Double click > Next > SQL Server Deployment > keep Next > This will deploy all .dtsx package into specified SQL Server(Dev1)

3. Go to Dev1 > SQL Server Agent > Job > Right Click > New Job

4. General> Name = "Job Name " , Category="choose category"

5. Steps > New > Step Name ="Given name " , Type = "SQL Server Integration Service package " , Run as : SQL Agent Service Account

Package Source : SQL Server , Server =DEV1 , Package ="Choose one of all imported SSIS package .

6. Job containing SSIS package is NOW ready to be executed.

Public Function ExecuteDataConversion(ByVal Jobname As String, ByVal dbServername As String) As Integer

Dim flag As Integer

dbconn = New DatabaseConn

Dim sqlConnMigrate As SqlConnection = dbconn.GetConnection()

Dim spProcName As String = "sp_start_job"

Dim sqlCmd As SqlCommand = New SqlCommand()

SetCommandType(sqlCmd, CommandType.StoredProcedure, spProcName, sqlConnMigrate)

Try

AddParamToSQLCmd(sqlCmd, "job_name", SqlDbType.VarChar, 50, ParameterDirection.Input, Jobname)

AddParamToSQLCmd(sqlCmd, "server_name", SqlDbType.VarChar, 50, ParameterDirection.Input, dbServername)

sqlConnMigrate.Open()

flag = ExecuteScalar(sqlCmd, sqlConnMigrate)

Return flag

Catch ex As Exception

Throw (ex)

Finally

sqlConnMigrate.Close()

End Try

End Function