December 9, 2008 at 1:12 pm
Confused yet? So am I.
Here's the setup. The tables where I need to insert records have unique PK values. But of course, they aren't set using the IDENTITY parameter or this would be a cakewalk. The PK values are stored in another table. So I need to pull the value from the second table so I can increment the records to be inserted.
I know I can use the Execute SQL Task in the Control Flow to set the variable and that's what I'm currently doing. But since this process happens at times when others can be updating the tables I'd like to move the variable set and reset closer to the end of the data flow so there's less chance of PK violations.
I'd use the Script Component transformation except that as far as I know I would have to hardcode the connection string to pull that value out of the table. This won't work when the package is deployed onto production servers. Hardcoding isn't my favorite way to go.
Yes, I'm doing set based transactions. I'm rather new to this whole SSIS thing and while I know I can do this in a stored procedure (and have done so for 2000) the hardcoding issue arises again.
I know I can drop the results into an object variable and then use a ForEach sequence container but I'm still in the dark about how to get that container to use the recordset.
I think I'm on the right track here one way or another. Please, let me bask in your wisdom here and find a solution to what' I'm going to have to write in various permutations far too many times.
December 10, 2008 at 6:35 am
If I understand your issue correctly then a script transform is in fact just what you're looking for, check out the following link which should tell you all you need to know.
http://www.sqlis.com/post/Generating-Surrogate-Keys.aspx
As for the issue of many people trying to insert at the same time, depending on how many rows you will be loading at a time and how long this may take you could consider locking the table during the insert which would cause other inserts updates and delete to wait until you have committed your transaction.
Hope that helps
Rob.
December 10, 2008 at 6:56 am
I knew I wasn't explaining it clearly.
I can get the record increments using the lovely Row Number Transformation. So I have the data, the increments, but not the PK value.
Example:
1,Joe,Smith
2,Susan,LoStat
3,Mary,Jones
I need to add those records to TableA.
The PK value for TableA is kept in TableB. When using the application and going into 'new record' mode on a form the app will grab the value from TableB, increment by 1, and then update TableB with the new 'last one' value.
I need to get the value from TableB and use it so I can get:
TableB.[Last One] = 12345
1,Joe,Smith,12346
2,Susan,LoStat,12347
3,Mary,Jones,12348
Note that I know how much to increment the [Last One] value since I have that from the Row Number transformation.
Currently I'm using the Execute SQL Task to set the value in a variable going into the data flow, then setting it again with the maximum value from TableA after the data flow. It works, but in a production environment I can't trust that the value won't be incremented in the middle of the data flow. Nor can I lock the table since that would cause headaches galore for the users of the application.
Playing around I've found that I can use a derived column to set the variable from the data source but I can't figure out how to integrate this little branch into the data flow proper. I end up with a derived column and no where to put it into the flow.
December 10, 2008 at 7:18 am
MrsPost (12/10/2008)
I knew I wasn't explaining it clearly....When using the application and going into 'new record' mode on a form the app will grab the value from TableB, increment by 1, and then update TableB with the new 'last one' value...
Sorry you've lost me. What application?
In SSIS, what I would do is get the max identity, TableB.[Last One] = 12345 in your example.
Add this value as a derived column in your data flow and then in the script transform task increment this value by one for each record.
Please have another read of the link I posted earlier... I'm sure it has what you want.
Rob.
December 12, 2008 at 8:48 am
I fell prey to one of my most common failings here - overexplaining what I want to do.
In the most simple terms, I want to set a variable value within a data flow using a select statement against an existing data connection.
I know I can set the variable in the control flow using the Execute SQL Task but I would prefer to set the variable value within the data flow using an existing connection manager rather than using a Script Component Transformation and creating a connection string.
Taking out the unnecessary chatter about the application(s) this is for, does my request make more sense?
September 28, 2010 at 12:01 am
I am facing a similar issue. I can read/write to variables via a script component in Control Flow, however when I try a similar thing in the Data flow the “Dts” object is not available. Besides T-SQL I have not programmed in years, I have tried adding references however no luck. (Maybe it is just not possible). In my case all I am trying to do is capture an error message to pass to the error handler which will inturn email the users an appropriate message.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply