I can not figure this out in SSIS and its making me feel very stupid.




Scenario is to import several rows of data from Excel into SQL, but first I
have to get the next BatchID from a SQL table by inserting a new row.



I have the Excel import, transformations and import working fine without the
BatchID.




Now I need to figure out how / when I should execute a stored procedure to
get the next BatchID. Save that as a variable I assume, then run my import
from Excel and finally insert the results into SQL.




The thing I can't figure out is how I can use the variable that contains the
new ID. What am I missing.




Thanks for any help.




Mike

Hello Mike,

So Insert a new Row using the dataflow task. Timestamp it
Now move to an ExecuteSQL task and grab that value into a Variable

http://www.sqlis.com/default.aspx?58

Now you can used parameterised queries in the next data flow task.


If this is not poassible then let us know what your actual scenario is.



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com