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

A script task alternative to a massive SSIS multicast transformation Expand / Collapse
Author
Message
Posted Monday, January 16, 2012 10:42 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 123, Visits: 880
Comments posted to this topic are about the item A script task alternative to a massive SSIS multicast transformation
Post #1236984
Posted Tuesday, January 17, 2012 9:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:06 AM
Points: 337, Visits: 1,981
Hi Stan, nice article, however I can't really see this as a replacement for the multicast problem mentioned, as that is a problem which should never exist in the first place.

What it does look like a potentially robust replacement for is the File System task as this would be able to wrap up some of does file checking logic and keep it out of the Control Flow area.

Should mention that error handling using a message box should only be used in a demonstation scenario. If this was a scheduled package which behaved like this then it would stop processing as it waited for the phantom desktop user to click OK!
Post #1237288
Posted Tuesday, January 17, 2012 9:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 7, 2014 1:46 PM
Points: 247, Visits: 419
I'm not a fan of this. Aside from it being a maintenance issue (editing the SSIS package every time a new folder is needed or removed), you could at least use a for each task and iterate over a list of directories. Inside of that loop, you could use expressions within a file task to copy it around.


Post #1237326
Posted Tuesday, January 17, 2012 10:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 123, Visits: 880
To be honest, the only reason I wrote this is because somebody responding to one of my previous articles asked how it could be done.

Just because something can be done doesn't mean it should be done, but sometimes it's fun to do it anyway.
Post #1237350
Posted Tuesday, January 17, 2012 10:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 7, 2014 1:46 PM
Points: 247, Visits: 419
Fair enough. I feel the same way about Powershell.


Post #1237380
Posted Tuesday, January 17, 2012 12:16 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Obs (1/17/2012)
I'm not a fan of this. Aside from it being a maintenance issue (editing the SSIS package every time a new folder is needed or removed), you could at least use a for each task and iterate over a list of directories. Inside of that loop, you could use expressions within a file task to copy it around.


It's still good as a teaching exercise. In practice it might be better to have a scratch location for the master and then read the destinations from some table. If you are going in for the script task (VB or C#) then take a closer look into the File I/O objects. I seem to remember that you can specify overwrite or not.


ATB

Charles Kincaid

Post #1237433
Posted Tuesday, January 17, 2012 1:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:32 PM
Points: 388, Visits: 1,593
I'd definitely go with a For Each Loop and use the File Copy Task. I'd either read from a database table or configuration file. That way you don't have to touch the SSIS package when you have to change the copy locations. Honestly, I hate having tasks buried in code when there are already SSIS components available to use. I find having to crack open the code editor a PITA, and even more annoying when the developer doesn't include comments on what the code is doing and why.

Mwise



Post #1237466
Posted Tuesday, January 17, 2012 1:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 7, 2014 1:46 PM
Points: 247, Visits: 419
Charles Kincaid (1/17/2012)

It's still good as a teaching exercise. In practice it might be better to have a scratch location for the master and then read the destinations from some table. If you are going in for the script task (VB or C#) then take a closer look into the File I/O objects. I seem to remember that you can specify overwrite or not.


Perhaps as a "How to make this better" exercise



Post #1237482
Posted Tuesday, January 17, 2012 2:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
Charles Kincaid (1/17/2012)
...In practice it might be better to have a scratch location for the master and then read the destinations from some table.


I like this idea. While reading the article, this is what stood out to me as an obvious way to improve on the package.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1237548
Posted Wednesday, January 18, 2012 2:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:49 AM
Points: 1,179, Visits: 783
nice article, How about just writing a file to the single location and then use a batch job to copy it to the diffrent folders where ever you want. may be we can include this in another step of the job that we call the ssis package.
Post #1237749
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse