Loading multiple Rows in table from single row of input

• smick

  SSC Enthusiast

  Points: 159

  More actions

  October 29, 2008 at 12:53 pm

  #126573

  I have a website that is running on a SQL 2005 database. Whenever a new user is created, four tables are populated with data. Using an SSIS package with OLE DB Destinations I've successfully been able to populate three of those tables because they are one to one. Meaning for every user created those tables get one new row. My fourth table is the AccountOptions table. This table will create 34 rows for every new user.

  There are only three columns in this table as shown below. The data being populated in this table with the exception of the account id will be the same for every new user. This data is not coming from an outside source but instead will be "hard coded".

  This step is flowing out of a multicast and all four tables are being populated simultaniously. I've tried a few different things with no success. Does anyone have an idea for the best way to accomplish this?

  acc_id acc_opt_type acc_opt_value

  8accountAllow

  8accountReceivablen

  8addressModn

  8altCarriern

  8attempts

  8browseViewd

  8calcAvailMetha

  8catalogName8

  8cod

  8creditAllow

  8creditReqb

  8display_cinn

  8display_uomd

  8dispPickuplocationd

  8emailDoc

  8endOrderMessagen

  8endOrderMessageText

  8freightCalc0

  8hide_inn

  8holdCode

  8homePaged

  8inquiryOnlyn

  8maxOpenInvoices1000

  8maxPaidInvoices1000

  8openDaysBackDateAge7

  8openDaysBackDateInv7

  8orderConfDocd

  8orderConfirmd

  8orderEntryy

  8paidDaysBackDateAge7

  8pl_group_id

  8pwChangen

  8quantAvailn

  8quoteEntryn

  8quoteReleasen

  8reviewCode

  8showQuantIfAvailn

  8soldToModn

  8statusDays7

  8stockAmountv

  Thanks for any help you might give.

  Smick

• michaelwelcome

  SSC-Addicted

  Points: 411

  More actions

  November 3, 2008 at 8:03 pm

  #893382

  You could do the following:

  1. Create a "NewAccounts_Work" table.

  2. Add the fourth stream into the New Accounts work.

  3. Create a "NewAccount_Template" table. This would have all the User Option types and their default values. There would be one row for each option.

  4. Create a SQL Task the would run after the other steps are complete.

  5. This task would do an insert using a join between the NewAccounts_Work and NewAccounts_Template. The join condition could be just 1=1 or something like that so you would get 34 rows in the output for each row in the NewAccounts_work.

  6. Delete all the records in the NewAccounts_Work so it is ready for next time.