I need to use the variable in the oledb source

Question

I need to use the variable in the oledb source

itskumar2004

Default port

Points: 1446
More actions
October 25, 2010 at 11:55 am

#230359

Hi all,
i have posted this in SSIS but did not get any reply so i am trying my luck here:
I am creating a SSIS package for an FTP file as a destination and i am creating a temporary variable in the execute SQL task like this:
Create #temp
(
ID
)
Insert into #temp exec procedure @SeqID =39
Declare @ID int
select @ID=ID from #temp
Now this is going to be executed in execute sql task but i want to use this variable @ID in Oledb sourceTask( in Data flow task)
select * from
tablename
where ID = @ID
how is this possible ?creating a variable in SSIS and assigning values and use it in OLEDB Source?
If anyone know this could you help me ?right now i am creating a temporary table in that database and using the column ID in the OLEDB Source .
Can anyone please help me its urgent requirement

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11381 More actions · Answer 1

Your going to have to modify the OLEDB connection to use an expression. Then you can use variables. If you provide the code/package, it would be easier to provide a solution for you

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

itskumar2004 Default port Points: 1446 More actions · Answer 2

Thanks for the reply mike.

This is what i am trying to do:

I have a stored procedure which will send data to oracle using linked server .since the procedure is giving us the poor performance i have decided to FTP the file to oracle .there is one table in oracle which we need to write the status before and after FTP the file.

Table in Oracle:

create table Oracle

(

ID int not null,

Name varchar(20) null,

status varchar(20) null,

Start_Time datetime null,

End_Time datetime null

)

This table need to be filled before and after data transfer.

the ID column data will be filled by executing another procedure .and the other columns are filled manually.

This what i am doing in SSIS:

I have dragged execute task and in the sql statement i wrote this :

Declare

@ID int

create table #temp

(

ID int

)

Insert into #temp exec DataTransfer @TransferID =100 --the ID column id filled by executing the DataTransfer procedure

select @ID=ID from #temp

--Now i need to insert a row in the oracle table:

Insert into "Oracle"

(ID, NAME,STATUS,START_TIME,END_TIME)

values(@ID,' Data Transfer To Oracle','Transfer Started',getdate())

followed by a data flow task which is Oledb Source :

Here i am going to select data from a table by passing the parameter.

Create table dbo.TransferToOracle

(

CustomerName varchar(20) not null,

CustomerNumber int not null,

Quantity int null,

rev int null,

sequenceKey int null

)

This is what i am going to write in this task

select * from

Table dbo.TransferToOracle

where SequenceKey =@ID --This is one place where the variable needs to be used

followed by Flat file destination task and finally

followed by a another execute task :

Update "Oracle"

set

STATUS='Transfer Completed',

End_TIME=getdate()

where ID=@ID -- this is second place where the variable needs to be used

I need to use the variable in execute sql task in oledb source and second execute task.

I appreciate any help..

itskumar2004 Default port Points: 1446 More actions · Answer 3

well i donot have any idea to do this .

I have one more question, the destination of this file is a FTP like i mentioned how should i proceed by giving username and password to place it in that address .

i have seen some articles using script task for this .but i donot know how to proceed with that .

can anyone please guide me here