Creating Dynamic Outputs in SSIS to an SQL Database

Question

Creating Dynamic Outputs in SSIS to an SQL Database

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

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

sknox SSChampion Points: 12388 More actions · Answer 1

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.

bigcraiginjax Old Hand Points: 353 More actions · Answer 2

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'))

begin

EXEC (@TableCreateCmd)

end

I was using SQL 2005.

Thanks!

bigcraiginjax

Edward W. Stanley SSCertifiable Points: 6482 More actions · Answer 3

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.

fszendzielarz SSC Rookie Points: 33 More actions · Answer 4

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....

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 5

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

dave-dj SSChampion Points: 11297 More actions · Answer 6

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)

vshah555 SSC Eights! Points: 949 More actions · Answer 7

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.

dave-dj SSChampion Points: 11297 More actions · Answer 8

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)

vshah555 SSC Eights! Points: 949 More actions · Answer 9

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

dave-dj SSChampion Points: 11297 More actions · Answer 10

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)

Tim Mitchell SSCoach Points: 15681 More actions · Answer 11

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

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

dave-dj SSChampion Points: 11297 More actions · Answer 12

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)

VR_SQL Grasshopper Points: 17 More actions · Answer 13

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.

dave-dj SSChampion Points: 11297 More actions · Answer 14

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)