use master
go
if exists(select * from sys.databases d where d.[name] = 'SSIS_Test')
 drop database SSIS_Test
go 
create database SSIS_Test
go
use SSIS_Test
go

if object_id('test1','U') is not null
 drop table test1;
if object_id('test2','U') is not null
 drop table test2;
 
create table test1
(  _id1 int identity(1,1) not null primary key
 , _date datetime not null
 , _value varchar(63));
   
create table test2
(  _id2 int identity(1,1) not null primary key
 , _id1 int	
 , _value varchar(63));
 
 insert into dbo.test1
 (_date,_value)
 select '20090101', '1_1' union all
 select '20090101', '1_2' union all
 select '20090101', '1_3' union all
 select '20090102', '2_1' union all
 select '20090102', '2_2' union all
 select '20090102', '2_3'

insert into dbo.test2
(_id1,	_value)
select t._id1, t._value + ' '+ss.tt   
from dbo.test1 t
cross join 
	(select '1' as tt union all 
	 select '2' as tt union all
	 select '3' as tt) as ss

--select * from dbo.test1 t
--select * from dbo.test2 t
go

create procedure dbo.pTest
@_id1 int
as
begin
	select _value 
	from dbo.test2 T
	where T._id1 = @_id1
end;
go

--execute dbo.pTest @_id1 = 1 

 - SQL statement :
	select _id1 from dbo.test1 where _date = ?
 - Parameter mapping :
	User::dt; Direction = Input; DataType = adDate; ParameterName = Param1

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    Dim _res As String
    Public Sub Main()
        _res = "execute dbo.pTest @_id1 = " + Dts.Variables("_id").Value;
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class