4

I have a task where I am trying to assign a random PIN to an account. I have SQL code that will generate a random integer between 1000 and 9999. I was wondering if I could put that in a Derived Column task in SSIS and not do it in the source data?

Also, I can probably write a Script Task to accomplish this. At this point it's more an exercise to see if it's possible since I have alternatives.

  • I don't believe the SSIS expression language has a random function (and you still cant create custom functions). I would likely avoid the source and transfer altogether, and generate the value on the destination side only. Set the pin using an update statement later in the package flow, filling with "1 + ABS(CHECKSUM(NEWID())) % @n" TSQL. Likely, you might want to later regenerate a pin number later, so might be better centralizing the logic there alone. – Andrew Loree Mar 30 '16 at 4:36
  • Thanks. That's an alternative I hadn't considered. I can update the account table later on. – Chris The DBA Mar 30 '16 at 12:30
2

You don't actually have to alter your source data, but you can use a customised SQL command in your OLE DB Datasource. Basically change the Data access mode from Table or view to SQL command or even SQL command from variable and create your statement to include your PIN code, something like this:

SELECT *, 1 + ABS(CHECKSUM(NEWID())) % 9999 PIN
FROM dbo.ssisTest

Test results: Customised SQL command in OLE DB Datasource

No need for Derived Column, Script Task etc. Make sense?

Another approach would be to assign some kind of arbitrary row id to your source table and a similar id to your unique account ids table and join on that, something like this:

SELECT *
FROM 
    (
    SELECT
        ROW_NUMBER() OVER( ORDER BY ( SELECT NULL ) ) rowId,
        *
    FROM dbo.ssisTest
    ) x
    INNER JOIN dbo.uniqueAccounts a ON x.rowId = a.rowId

Similar to the above approach, you can hold this SQL in your OLE DB Source without affecting your actual source data. In order for this to work you must a) persist your unique accounts and give them an arbitrary row id and b) there must be more unique accounts than there are original records, eg generate a few million up front.

  • I had looked at doing this on the OLE Source but ran into some problems with creating multiple accounts with the same account number. I will probably revisit the SQL command to keep the distinct accounts. – Chris The DBA Mar 30 '16 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.