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 |
|
reidkell
Starting Member
16 Posts |
 Posted - 2013-06-07 : 17:47:33
|
| Hello, all. I'm coming from SQL 2000 to 2008 and am struggling with SSIS. Love it so far, but still trying to educate myself.I have a process where I'm copying a spreadsheet, importing and manipulating/filtering the data. So far so good there.However, before doing this, I want to write a record to my ImportBatch table, which will track the import date, how long it took, success/fail status, etc. The BatchNumber will simply be the new record's @@IDENTITY value.Can someone tell me the high-level steps to accomplish this? Can I do it directly within the SSIS data flow, or do I need to call a stored proc? Either way, how do I get the BatchNumber value, then use it during my data import? Any info appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-08 : 03:15:35
|
| Since its not actual data that you're populating inside table but just control information you could simply use execute sql task for this. Inside that Call a procedure to do this insert. Inside procedure, write INSERT statement and also declare an OUTPUT parameter which will be set to SCOPE_IDENTITY to get the identity value returned. Inside execute sql task inside variable tab map output parameter against a SSIS variable and you'll get batchid inside which you can use in all the following steps.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
reidkell
Starting Member
16 Posts |
Posted - 2013-06-10 : 17:46:45
|
| Thank you! That was exactly what I needed to know and I accomplished the task (although it was by no means straightforward on *how* to get all that working in SSIS). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 01:19:11
|
| welcomeGlad that you problem got sorted------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
SSIS steps to get Batch #