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.
| Author |
Topic |
|
mgreen84
Yak Posting Veteran
94 Posts |
 Posted - 2014-12-01 : 15:14:17
|
| How can I generated a ID field in an expression? I don't won't every record loaded to have a unique ID, but rather all the records to receive the same ID. And then the next time the package is ran the next ID for all those records would be incremented +1 based off previous package run ID. |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-12-01 : 15:39:17
|
Since you posted under 2012, I assume that's what you're running. Which means that you can use a sequence object.http://msdn.microsoft.com/en-ca/library/ff878091.aspxSay you create a SO: create sequence myseq MINVALUE 1 NO CYCLE Then, in SSIS, add a SQL task that gets the next value. The query is simply:select ? = next value for myseq Set the lone parameter to a SSIS variable, type integer and flag it as output. Then you can use the value of the variable in your insert statements. Just add a Derived Column transformation to add it to the data flow and include it in the output. |
 |
|
|
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2014-12-03 : 09:19:26
|
quote:
Originally posted by mgreen84
How can I generated a ID field in an expression? I don't won't every record loaded to have a unique ID, but rather all the records to receive the same ID. And then the next time the package is ran the next ID for all those records would be incremented +1 based off previous package run ID.
gbritton in 2012, how do you set the results to a variable? I don't see the same options that were available in 2008. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-12-03 : 09:38:30
|
| You're running 2012, right? (That's where you posted the question) In 2008-2014, in the BIDS/SSDT control flow, drag an execute sql task to the surface. edit the task, add a connection. in the parameters tab, choose your variable, set the direction to output and the parameter name to 0. |
|
|
|
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2014-12-03 : 13:21:47
|
quote:
Originally posted by gbritton
You're running 2012, right? (That's where you posted the question) In 2008-2014, in the BIDS/SSDT control flow, drag an execute sql task to the surface. edit the task, add a connection. in the parameters tab, choose your variable, set the direction to output and the parameter name to 0.
Thanks, sorry about that I was using the SQL statement task. but you method worked.Thanks again |
|
|
|
|
|
|
|
|
How to create a ID in an expression