Randomize customer names in test db

Question

Randomize customer names in test db

dmcnair1881

Old Hand

Points: 301
More actions
January 9, 2015 at 10:03 am

#300175

I am currently trying to use SSIS to create a copy of a production database, rename it, and randomize customer information inside the customer table (first name and last name). This may not be the best approach, but I have been trying to take all first names in the production database and add them to a single variable named FirstNames. Also doing the same with LastNames. I envisioned somehow breaking these variables up in a separate script component into some sort of a collection for each. For each customer record coming through, I wanted to randomly select a first name from the FirstNames collection and replace the actual first name with this one. I want to also do the same with the last name. I have tried using string variables, and build them through a script component with a delimiter, and was just going to do a split on the delimiter in another script component to populate a collection, but the process of building the string variables was taking way too long, and I can understand that because we're talking about around 500,000 first names and last names. I've tried using object variables, but I can't seem to figure out how to break everything out into a collection from there. I would appreciate any assistance on how to break out the object variable into a collection, or any guidance on a more efficient approach to accomplish this task.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

dmcnair1881 (1/9/2015)
I am currently trying to use SSIS to create a copy of a production database, rename it, and randomize customer information inside the customer table (first name and last name). This may not be the best approach, but I have been trying to take all first names in the production database and add them to a single variable named FirstNames. Also doing the same with LastNames. I envisioned somehow breaking these variables up in a separate script component into some sort of a collection for each. For each customer record coming through, I wanted to randomly select a first name from the FirstNames collection and replace the actual first name with this one. I want to also do the same with the last name. I have tried using string variables, and build them through a script component with a delimiter, and was just going to do a split on the delimiter in another script component to populate a collection, but the process of building the string variables was taking way too long, and I can understand that because we're talking about around 500,000 first names and last names. I've tried using object variables, but I can't seem to figure out how to break everything out into a collection from there. I would appreciate any assistance on how to break out the object variable into a collection, or any guidance on a more efficient approach to accomplish this task.

What is the PK of the Customer table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dmcnair1881 Old Hand Points: 301 More actions · Answer 2

I apologize, but right after I posted for help, I realized how dumb I was being in trying to do everything through the SSIS script components when I could just write a stored procedure to make things to much simpler. Thanks for looking at my question anyways. Sorry for wasting everyone's time.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

dmcnair1881 (1/9/2015)
I apologize, but right after I posted for help, I realized how dumb I was being in trying to do everything through the SSIS script components when I could just write a stored procedure to make things to much simpler. Thanks for looking at my question anyways. Sorry for wasting everyone's time.

Excellent! That's where I was headed with this.:-D

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)