Update Recordset and Export to Report 1

[Bonediggler1]

Hello

I am trying to do the following via SSIS:

1)Extract data from SQL Server
2)Put this data into a recordset
3)Update the recordset (e.g. if column 1 has value "tomorrow" update it to say "today")
4)Export to Excel

I am not having trouble with any of this except #3. Not sure how to update data in a recordset. This could easily be done by storing the data in a physical table and running an Execute SQL task, but i'd rather do this in-memory.

Is this possible?

Thank you!!

 

[SQLScholar]

When you do the extract, instead of extracting the table - change it to a query.

Then in the query you can make any alterations to the data output that you require. By the sound of it it will be something like this:

select
replace(col1,'tomorrow','today') as col1
col2,
col3,
from table

or this may work quicker

select
case when col1 = 'tomorrow' then 'today else col1 end as col1,
col2,
col3,
from table


If you need help writing the query any more then above then please post table definition and what you require.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[Bonediggler1]

Hi

Sorry, I oversimplified the package.

Actually I am pulling data from 2 different (heterogeneous) sources and joining via a merge join, then pushing to a recordset. As such, I cannot manipulate the data via SQL/extract via a query.

Again, this can be accomplished by throwing everything in tables on the backend but I am looking to find out if it's possible in a recordset. I read some stuff about doing this via a loop and script task, but couldn't quite adapt it to my own package...

 

[SQLScholar]

OK. That changes the goalposts a little.

In this case i would then, after the merge join do a derived column.

The derived column can replace a column, and you would then be able to define an expression which can then make the column be whatever you want.

The expression for an iff for you will be something like this

([col1]=="tomorrow"?"Today":[col1])

http://msdn.microsoft.com/en-us/library/ms141680.aspx

Dan


----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[Bonediggler1]

I need this for multiple columns. is this possible with a derived column?

 

[SQLScholar]

Input for a derived column can be from multiple columns (whatever you can build in an expression)

Using the derived column task you can create more then one column.

I hope this answers whatever way round you require.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------