Hello -
I am making good progress with my ssis package. However, there is one new thing which I cannot graps yet. That is, how to use variables when I want to update or insert a new row. I have some columns in my tables that require the datetime that the update/insert occured, the person making the change, and a few other things that are not part of the incoming data source (an excel file).
I created some user variables for these things, but I cannot figure out how to use them with my OLE DB Command and OLE DB Destination. One handles Inserts and the other handles the Updates based on whether a row in the Excel file is new (an Insert) or already exists (an update). Along with the insert or update, I'd like to set the Lastupdate, Who, etc.
Thanks for any help
- will
How to use variables in OLE DB or OLE Destination?
deen
Gagandeep Singh
Well it depends on the requirements...
Do you need a consistent value for ALL rows in a load
This sounds like an audit field so what other applications are using it If a single user inserts or updates a record in addition to the data loads, and you want consistent behavior across your application then setting a default value for the column in the table definition makes sense. We've used GetDate() as the default value for LastUpdatedDate on many projects without any adverse effects on performance.
sgmuser
For the LastUpdated column you should consider using a default value on the column definition. Place GetDate() in the textbox beside Default.
Otherwise, do what Darren said and use a Derived Column, and put the variable in the Expression textbox.
xr280xr
Thanks to everyone for the advice. The idea of using the derived column transform sounds great. I have toyed with the idea of whether to use a consistent date/time (the same for all rows) or to evaluate per row, and I decided to use the same variable/value for all rows.
Thanks again for all of the help.
grnr_r
Not to mention there is no simple "DATE" type yet in SQL Server. So if you do use the getdate() function, you'll get the timestamp portion as well, which won't be unique for all rows processed in that batch. Do what Darren suggests, and use a system variable, or a variable that's been converted to a simple date prior to execution. You certainly do not want to evaluate getdate() for each row if you don't need to.
billqu