InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:07:51 GMT20http://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxthat 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.Wed, 01 Dec 2010 02:43:04 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxHi, 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.Tue, 30 Nov 2010 13:03:07 GMTVR_SQLhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspx[quote][b]Tim Mitchell (11/24/2010)[/b][hr]Dave, good article. Thanks for the mention of my earlier publication as well![/quote]no problem - credit where credits due! :-)Thu, 25 Nov 2010 02:22:42 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxDave, good article. Thanks for the mention of my earlier publication as well!Wed, 24 Nov 2010 20:37:56 GMTTim Mitchellhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxsorry 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 syntaxTue, 16 Nov 2010 06:44:33 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxI did check both the things and still i am getting the same error.Mon, 15 Nov 2010 05:46:54 GMTvshah555http://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxtwo 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 noneMon, 15 Nov 2010 02:26:32 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxHi,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.Sun, 14 Nov 2010 13:00:47 GMTvshah555http://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspx[quote][b]mishaluba (11/10/2010)[/b][hr]Love the detailed instructions. Worked for me "out of the box". Thank you![/quote]Thats what I like to hear :-)Thu, 11 Nov 2010 02:24:44 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxLove the detailed instructions. Worked for me "out of the box". Thank you!Wed, 10 Nov 2010 22:34:36 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxSome 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....Wed, 10 Nov 2010 15:08:22 GMTfszendzielarzhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxSome 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.Wed, 10 Nov 2010 14:42:52 GMTEdward W. Stanleyhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxVery 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 statementIF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(@TableName) AND type in (N'U'))beginEXEC (@TableCreateCmd)endI was using SQL 2005.Thanks!Wed, 10 Nov 2010 14:40:33 GMTbigcraiginjaxhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspx[quote][b]Samuel Vella (11/10/2010)[/b][hr][quote][b]fszendzielarz (11/10/2010)[/b][hr]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?[/quote]Thats how software pricing works for almost every software company out there.[/quote]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.Wed, 10 Nov 2010 08:18:12 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxNow I can see them - I'm further enlightened now. Thanks.Wed, 10 Nov 2010 08:06:35 GMTtfergusonhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspx[quote][b]fszendzielarz (11/10/2010)[/b][hr]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?[/quote]Thats how software pricing works for almost every software company out there.You want more features, then you have to pay.SME's typically have much smaller data sets, infact even in some of the large multi national organisations I've worked for none of the enterprise features they paid for were used, even with many millions of rows, a non partitioned table structure can be made to behave.Where there is a need for some enterprise features, SME's then just have to evaluate the cost benefit. Is paying for the extra features in Enterprise edition cheaper or more expensive than paying someone to code round the issue?Wed, 10 Nov 2010 07:51:56 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxHere is a PDF copy of the article with images for those that can't see them. ( I can only guess it's because you can't access the flickr server they are on)Wed, 10 Nov 2010 07:46:05 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxThe images are also not showing up for me, either. When I hover, it says 'screenshot' or 'query extract', but a lot of blank spaces in this article.Wed, 10 Nov 2010 07:27:47 GMTtfergusonhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspximages are hosted on flickr - but they should be fine.Wed, 10 Nov 2010 06:41:35 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxAre the images not showing up for anyone else?Even when I right click and say Show Picture nothing happens.Wed, 10 Nov 2010 06:20:01 GMTmbova407http://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxI would love to see what the partitioned table solution would look like.Wed, 10 Nov 2010 05:44:37 GMTSmendlehttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxOh 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?Wed, 10 Nov 2010 05:00:18 GMTfszendzielarzhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspx[quote][b]Bertil Forsberg (11/10/2010)[/b][hr]Hi Dave!A nice and clean solution, quite interesting to follow.My only objection right now is the phrase 'FEL' in your namings, in swedish thatmeans 'WRONG'! :-)Thanks,BF[/quote]Also I guess if I stuck to the MS naming convention suggest then it should really be [b]FELC[/b] - but I'm not sure what that would mean in Swedish :-)Wed, 10 Nov 2010 03:02:00 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspx[quote][b]fszendzielarz (11/10/2010)[/b][hr]Why not just partition the table? You get parallelism too.[/quote]Partitioned Tables are Enterprise Edition onlySo the answet to why not is 10's of thousands of dollars!Wed, 10 Nov 2010 03:01:50 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspx[b]Bertil Forsberg:[/b] I've learnt something new today! every day is a school day. :-) [b]fszendzielarz:[/b] Partitioning for me was not an option as we are only using SQL Server Standard Edition - Partitioning is an Enterprise edition feature. But also, the transaction types that I refer to in the article represent different information that ideally should have been designed as different tables within a database anyway (source is a 3rd party system).[b]106600.3363: [/b] Potentially, yes it could be done via T-SQL. I think you touch on a interesting point. Personally, I've used SSIS for a number of reasons:1 - visually you can sort of see what is going on (albeit there is some slightly more hidden details, such as defining the connection string2 - secondly, this is part a much larger project and the production package I use, contains several other package run related variables and a few additional task.3 - SSIS offers better event handling functionality. (again some of this could be done in SQL) ...... horses for coursesWed, 10 Nov 2010 03:00:18 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxA very clever solution but I think I must be missing something. Why can't all the logic associated with sucking the data in, creating new tables if necessary and appending the new records to the relevant table all be encompassed in one T-SQL block instead of many SSIS flows / tasks? Most of the required code would be virtually as Dave Lumley has already written it. That could then be called by SQL Agent? It could become a stored procedure but the performance gain would presumably be small if the quantity of INSERTs is high relative to the "think time" associated with coming up with an execution plan. The solution already requires the use of dynamic SQL and I can't see that can be avoided.Wed, 10 Nov 2010 01:49:10 GMT106600.3363http://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxWhy not just partition the table? You get parallelism too.Wed, 10 Nov 2010 00:51:08 GMTfszendzielarzhttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxHi Dave!A nice and clean solution, quite interesting to follow.My only objection right now is the phrase 'FEL' in your namings, in swedish thatmeans 'WRONG'! :-)Thanks,BFWed, 10 Nov 2010 00:19:21 GMTBertil Forsberghttp://www.sqlservercentral.com/Forums/Topic1018362-1634-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Integration+Services+(SSIS)/70980/">Creating Dynamic Outputs in SSIS to an SQL Database</A>[/B]Tue, 09 Nov 2010 21:20:34 GMTdave-dj