How to use variables in OLE DB or OLE Destination?

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

Use a Derived Column transform, add a new column, and use the variable as the expression. This column will now contain your variable value. It can now be used in either the OLE-DB Destination or Command just like any other column, so you can insert or update the target column as required.|||

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.

|||Would it not make more sense to use a consistent date value for all rows in a load? A variable is ideal for this. GETDATE would be evaluated for every row, so you could get a raneg of dates accross teh load, and it *might* cost more. Perhaps one of the system variables would actually do better than a user variable here, e.g. @.[System::StartTime] or @.[System::ContainerStartTime].|||

DarrenSQLIS wrote:

Would it not make more sense to use a consistent date value for all rows in a load? A variable is ideal for this. GETDATE would be evaluated for every row, so you could get a raneg of dates accross teh load, and it *might* cost more. Perhaps one of the system variables would actually do better than a user variable here, e.g. @.[System::StartTime] or @.[System::ContainerStartTime].

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.|||

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.

|||

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.

sql