DFT SSIS ISSUE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

DFT SSIS ISSUE

Author

Topic

h.singh10
Starting Member

16 Posts

Posted - 2012-01-16 : 13:18:33

I have two task in DFT 1.EXECUTE SQL TASK 2. DATA FLOW TASK.
In data flow task, there is an oledb source that extract data from oracle and load into flat file.

In execute sql task, I am logging the sysdate in oracle table like: insert into logging values(sysdate)

In the next step, I am using a source query which extract data from test table as:
select * from test where load_tmstp between (select max(tmspt) from batch) and sysdate

Now here is the issue, i dont want to use the current value os sysdate but want to have the same value as we did in execute sql task (insert into logging values(sysdate))
As going from execute sql task to dft, the value of sysdate will change.
Is there any way, i can freeze that value and use that value later in the package.

Thanks
Harry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 13:30:58

you need to store that value in a variable created in ssis and use it later. just make resultset as single row and add a select statement as

select sysdate as currentdatevalue

then in variable mappings tab of execute sql task map currentdatevalue to variable created and use it wherever you want to get stored value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

h.singh10
Starting Member

16 Posts

Posted - 2012-01-16 : 13:49:42

Hi visakh
So what will be the data type when we define the user variable in the result set tab. if it is datetime, then the value will be hard coded as we need to provide a value by ourself.

Harry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 14:09:31

it will be datetime as sysdate function returns a datetime value.

didnt understand why you need to hardcode it? your query inside execute sql task will give it right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

h.singh10
Starting Member

16 Posts

Posted - 2012-01-16 : 14:21:06

When we define datatype in result set tab, we are prompt to add a value to procced. Thats why i was asking. Unless we give a datetime value, we can proceed (OK button doesnt get activated). This is case only with datetime datatype.

h.singh10
Starting Member

16 Posts

Posted - 2012-01-16 : 14:22:54

It says "a variable must be specified for the selected datatype"

h.singh10
Starting Member

16 Posts

Posted - 2012-01-16 : 14:41:25

Hi Visakh

Your suggestion works really well. Now i am able to insert correct sysdate by mapping it to the result set variable.

Now if i want to use this variable in an oledb source, how should i proceed. I guess sql command from a variable is the option but i am not sure how to write the query in a expression.

So far i am writinh like this:
"SELECT * FROM TEST WHERE LOAD_TMSTP = "+@V1+""

(@v1 is the variable i defined in resultset and has value of sysdate)

Error:Error HRESULT E_FAIL has been returned from a call to a COM component. (Microsoft Visual Studio)

Please help
Harry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 22:28:58

you need to create a new variable to hold the above query expression and set EvaluateAsExpression property true for it.

then set above expression to it
map this variable to oledb source command after setting type as sql command from variable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources