Creating Dynamic Outputs in SSIS to an SQL Database

  • Samuel Vella (11/10/2010)

    fszendzielarz (11/10/2010)

    Oh yes. I forgot about that.

    Doesn't it seem ridiculous that functionality that is already implemented is disabled for those who cannot pay for it? The irony is that MS aim to support SME and break into this sphere, so why support enterprises by denying features to smaller organisations?

    Thats how software pricing works for almost every software company out there.

    Not just software. Ever look at the electronic board for a low-end product from a company which offers a higher-end product? 9 times out of 10 it's the same board with a different firmware and some (usually quite inexpensive) key components not included. Think of all the different versions of the same model car; the only difference (besides cost) is features. How about cable/satellite television? All of the channels are being transmitted regardless of which ones you paid for, so the receiver has to "disable" (i.e, not decode) the ones you haven't paid for.

  • Very cool exercise. I needed to revise the create table T-SQL to the code below in order for it to work for me.

    -- Check if destination table already exists, If it does not, execute the create table statement

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(@TableName) AND type in (N'U'))


    EXEC (@TableCreateCmd)


    I was using SQL 2005.



  • Some additional steps: some code to also have a table of unknown records , in case there's a typo, id10t error or addition to the types of records... otherwise you will skip rows.

    I've done the same thing previously but handcrafted the tables upon various output results. with replacement tables cycling through table.1 - table.x to so have record of the previous events for review or edit.

  • Some claim the idea of segmented pricing for software and similar products is debunked (eg: Joel Spolsky). Some say that ultimately it's he who simply offers the lowest price that should win. I wonder....

  • Love the detailed instructions. Worked for me "out of the box". Thank you!

  • mishaluba (11/10/2010)

    Love the detailed instructions. Worked for me "out of the box". Thank you!

    Thats what I like to hear 🙂

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • Hi,

    Thanks for posting the solution. I am trying to implement the same solution in my test environment and i am getting error. It states "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly". It throws error on Execute SQL Task. I have followed the same steps as mentioned. I am using SQL Server 2005.

    Any help would be appreciated.

  • two things to double check first.

    1 - when mapping the parameter User::TxType variable. make sure you set the Parameter name to 0 (zero) (by default it'll be something like parameter 1 ....

    2 - check that the result set on the main property page is set to none

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • I did check both the things and still i am getting the same error.

  • sorry it's taken a few days to get back to you.

    I suspect it not actually anything to do with the result set. Double check your SQL statement is correct.

    When I put the wrong syntax or value, I got an error message complaining about the result set, when in actual fact I had an error in my T-SQL syntax

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • Dave, good article. Thanks for the mention of my earlier publication as well!

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant | @Tim_Mitchell |
    ETL Best Practices

  • Tim Mitchell (11/24/2010)

    Dave, good article. Thanks for the mention of my earlier publication as well!

    no problem - credit where credits due! 🙂

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • Hi, I am also getting an error when try configuring 'OLE_DST - transactions_':

    Error at DFT - Extract Transactions [OLE_DST - transactions_ [28]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E37 Description: "Table/view either does not exist or contains errors.".

    Error at DFT - Extract Transactions [OLE_DST - transactions_ [28]]: Opening a rowset for " transactions_" failed. Check that the object exists in the database.

  • that read like you haven't yet set up the 'dbo.transactions_' table.

    this is need so that you configure the adapter. It servers 2 purposes. 1- it provides an actual table for the connection to use and validate against, 2-should you get an issue, it can in error some cases be used as a capture table i.e. duplicate key errors could be redirected to an error output that uses the default table.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Viewing 14 posts - 16 through 28 (of 28 total)

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