Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Creating Dynamic Outputs in SSIS to an SQL Database Expand / Collapse
Author
Message
Posted Tuesday, November 9, 2010 9:20 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
Comments posted to this topic are about the item Creating Dynamic Outputs in SSIS to an SQL Database

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #1018362
Posted Wednesday, November 10, 2010 12:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 9:00 AM
Points: 11, Visits: 156
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 that
means 'WRONG'!
Thanks,
BF
Post #1018389
Posted Wednesday, November 10, 2010 12:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 6:59 AM
Points: 3, Visits: 13
Why not just partition the table? You get parallelism too.
Post #1018397
Posted Wednesday, November 10, 2010 1:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 2:18 AM
Points: 1, Visits: 73
A 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.
Post #1018413
Posted Wednesday, November 10, 2010 3:00 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
Bertil Forsberg: I've learnt something new today! every day is a school day.

fszendzielarz: 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).

106600.3363: 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 string
2 - 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 courses


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #1018448
Posted Wednesday, November 10, 2010 3:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:37 AM
Points: 337, Visits: 1,986
fszendzielarz (11/10/2010)
Why not just partition the table? You get parallelism too.


Partitioned Tables are Enterprise Edition only
So the answet to why not is 10's of thousands of dollars!
Post #1018449
Posted Wednesday, November 10, 2010 3:02 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
Bertil Forsberg (11/10/2010)
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 that
means 'WRONG'!
Thanks,
BF


Also I guess if I stuck to the MS naming convention suggest then it should really be FELC - but I'm not sure what that would mean in Swedish


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #1018450
Posted Wednesday, November 10, 2010 5:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 6:59 AM
Points: 3, Visits: 13
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?
Post #1018489
Posted Wednesday, November 10, 2010 5:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:45 PM
Points: 40, Visits: 269
I would love to see what the partitioned table solution would look like.
Post #1018505
Posted Wednesday, November 10, 2010 6:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:24 PM
Points: 1,393, Visits: 478
Are the images not showing up for anyone else?

Even when I right click and say Show Picture nothing happens.
Post #1018526
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse