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 «««1234»»

Import Dynamic File Name with a Date/Time as the file type (YYYYMMDDHRMMSS) Expand / Collapse
Author
Message
Posted Tuesday, June 11, 2013 10:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
Jeff Moden (6/11/2013)
opc.three (6/11/2013)
Thank you for bringing the conversation back to the original intent. Maybe you doing it will make it stick.


Great idea but YOU are not the one to determine what is on track and what is not especially since you only provided pseudo-code to someone who may not know the language base (if he did, he'd have written it already).

Excuse me, I thought this was an SSIS Question. See your way out if you are not enthused with the track of most of the threads in this Forum.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1462447
Posted Tuesday, June 11, 2013 11:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
Jeff Moden (6/11/2013)
For example, sp_MakeWebTask was very well documented and supported and it went away virtually overnight because Microsoft thought everyone would go ga-ga over SSRS

sp_MakeWebTask was likely dropped from the product in part because it sucked, and also because it was accepted that CPU time available on a database server is much better spent managing data and not generating HTML documents. To that end moving development dollars into furthering these capabilities within the Microsoft Data Platform Stack into a system that could be scaled up, out and extended far more easily and cost effectively, namely SSRS. You can generate HTML by doing a bunch of string concatenation in T-SQL and people do, and that handles many basic needs, but certainly not in a structured way with a proper HTML parser or object translation layer.

Similarly, Microsoft will eventually come to their senses and drop xp_cmdshell, in part for the same reasons mentioned above (especially the first one I mentioned) and provide a replacement tool. Oh wait they already did drop it...have you had a look at SQL on the Azure Platform? And they already have provided a capable and more robust replacement to bridge the OS-to-SQL Server gap, namely PowerShell.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1462457
Posted Wednesday, June 12, 2013 7:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:02 AM
Points: 35,263, Visits: 31,752
opc.three (6/11/2013)
Jeff Moden (6/11/2013)
For example, sp_MakeWebTask was very well documented and supported and it went away virtually overnight because Microsoft thought everyone would go ga-ga over SSRS

sp_MakeWebTask was likely dropped from the product in part because it sucked, and also because it was accepted that CPU time available on a database server is much better spent managing data and not generating HTML documents. To that end moving development dollars into furthering these capabilities within the Microsoft Data Platform Stack into a system that could be scaled up, out and extended far more easily and cost effectively, namely SSRS. You can generate HTML by doing a bunch of string concatenation in T-SQL and people do, and that handles many basic needs, but certainly not in a structured way with a proper HTML parser or object translation layer.

Similarly, Microsoft will eventually come to their senses and drop xp_cmdshell, in part for the same reasons mentioned above (especially the first one I mentioned) and provide a replacement tool. Oh wait they already did drop it...have you had a look at SQL on the Azure Platform? And they already have provided a capable and more robust replacement to bridge the OS-to-SQL Server gap, namely PowerShell.


BWAA-HAAA!!!! It sounds like you never used it before because it certainly didn't suck! And it was a proper HTML "parser" (generator, actually). It allowed both structured and unstructured use which was great for both long term projects and the "gotta-have-it-now" urgencies. It even allowed for style sheets to be used and was incredibly easy to use. It was a nice, tight, little COM object that produced instant results instead of being better at producing little green timing circles on the screen. It was awesome!

You've also forgotten that a huge number of people have SSRS installed on the same server as their data. There goes the supposed CPU advantage. For those that install SSRS on separate systems, there goes the cost advantage because now they need another license and they have another machine to maintain, virtual or otherwise.

As for MS supposedly "coming to their senses with xp_CmdShell" goes, just because you disagree with something, doesn't make it bad. It just makes it bad for you.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1462638
Posted Wednesday, June 12, 2013 7:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:02 AM
Points: 35,263, Visits: 31,752
opc.three (6/11/2013)
Jeff Moden (6/11/2013)
opc.three (6/11/2013)
Thank you for bringing the conversation back to the original intent. Maybe you doing it will make it stick.


Great idea but YOU are not the one to determine what is on track and what is not especially since you only provided pseudo-code to someone who may not know the language base (if he did, he'd have written it already).

Excuse me, I thought this was an SSIS Question. See your way out if you are not enthused with the track of most of the threads in this Forum.


It absolutely is. But SSIS isn't capable of doing this without a little help, right? That's why you wrote a VB script and why I wrote a T-SQL script. My track is no worse than yours exccept that you don't happen to agree with my track. At least I wrote something other than pseudo-code.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1462646
Posted Wednesday, June 12, 2013 8:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
Jeff Moden (6/12/2013)
opc.three (6/11/2013)
Jeff Moden (6/11/2013)
opc.three (6/11/2013)
Thank you for bringing the conversation back to the original intent. Maybe you doing it will make it stick.


Great idea but YOU are not the one to determine what is on track and what is not especially since you only provided pseudo-code to someone who may not know the language base (if he did, he'd have written it already).

Excuse me, I thought this was an SSIS Question. See your way out if you are not enthused with the track of most of the threads in this Forum.


It absolutely is. But SSIS isn't capable of doing this without a little help, right? That's why you wrote a VB script and why I wrote a T-SQL script. My track is no worse than yours exccept that you don't happen to agree with my track. At least I wrote something other than pseudo-code.

First off, Script Tasks are part of SSIS, therefore you have the full power of .NET included natively within SSIS. There is no penalty for using .NET within SSIS, no security context changes, and no application scope changes, so no, that was all native SSIS baby. A few lines of .NET code to give me a custom file system interaction like "get me the newest file in a folder" is nice and neat from my perspective.

Both of your options are lesser solutions for several reasons. 1. In both, you're changing the perspective of the system action. If you were to stay in SSIS then the path to the folder is unchanged. That path may be local to the application server where the SSIS is running. How would you know how to reference that folder when trying to refer to it from within T-SQL? 2. Both of your solutions require a roundtrip to the database engine, more overhead. 3. The SSIS may not be running on the server where the database instance resides. There may be no database instance installed on the server where the SSIS is running, for that matter. 4. You are making the assumption that the SQL Server service account has access to the folder where these files reside. 5. You've served up a solution using an undocumented feature, and another using a controversial one that, yes, exposes prevalent and latent security exposures and has auditing shortcomings, but please, please, please, refer to my earlier post with a list of our greatest hits and let's not go there, again.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1462656
Posted Wednesday, June 12, 2013 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
Jeff Moden (6/12/2013)
opc.three (6/11/2013)
Jeff Moden (6/11/2013)
For example, sp_MakeWebTask was very well documented and supported and it went away virtually overnight because Microsoft thought everyone would go ga-ga over SSRS

sp_MakeWebTask was likely dropped from the product in part because it sucked, and also because it was accepted that CPU time available on a database server is much better spent managing data and not generating HTML documents. To that end moving development dollars into furthering these capabilities within the Microsoft Data Platform Stack into a system that could be scaled up, out and extended far more easily and cost effectively, namely SSRS. You can generate HTML by doing a bunch of string concatenation in T-SQL and people do, and that handles many basic needs, but certainly not in a structured way with a proper HTML parser or object translation layer.

Similarly, Microsoft will eventually come to their senses and drop xp_cmdshell, in part for the same reasons mentioned above (especially the first one I mentioned) and provide a replacement tool. Oh wait they already did drop it...have you had a look at SQL on the Azure Platform? And they already have provided a capable and more robust replacement to bridge the OS-to-SQL Server gap, namely PowerShell.


BWAA-HAAA!!!! It sounds like you never used it before because it certainly didn't suck! And it was a proper HTML "parser" (generator, actually). It allowed both structured and unstructured use which was great for both long term projects and the "gotta-have-it-now" urgencies. It even allowed for style sheets to be used and was incredibly easy to use. It was a nice, tight, little COM object that produced instant results instead of being better at producing little green timing circles on the screen. It was awesome!

You've also forgotten that a huge number of people have SSRS installed on the same server as their data. There goes the supposed CPU advantage. For those that install SSRS on separate systems, there goes the cost advantage because now they need another license and they have another machine to maintain, virtual or otherwise.

Oh I've used it. That's how I know it sucks. Talk about a finicky monolith.

With SSRS, you're right in that a lot of people go the "food court" option and install everything on one server. Great. Start out like that. But later, when you're BI stack is publicized and the accounting department loves you for it, you can move your SSRS operations to a new box. What happens when all your stuff is locked up in T-SQL? Add more hardware I guess, right? Start manually offloading operations to a new database server? It's much harder to "refactor" a database instance than it is to scale out your different services onto hardware that is built specifically for, and can be tuned specifically for, the type of workload it is being asked to do.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1462658
Posted Wednesday, June 12, 2013 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
As for MS supposedly "coming to their senses with xp_CmdShell" goes, just because you disagree with something, doesn't make it bad. It just makes it bad for you.

It's not about like. It's about empirical knowledge. I have literally logged hundreds, if not into the thousands of hours working with applications built around xp_cmdshell going back to the SQL 7 days. That is not even mentioning DBA solutions. I support an app today that uses ECHO and > to log to a file from T-SQL using xp_cmdshell. What a waste of time.

Anyway, it has been more than enough to learn and experience all the pros and cons of using it. From this I know it's worth steering people away from it. It's simply a shitty tool from a security perspective, an application design perspective, a maintenance perspective, a system stability perspective, from an interface perspective, the list goes on and on.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1462662
Posted Wednesday, June 12, 2013 8:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
Can SQL_Enthusiast's issue be solved with a WMI task or data reader? I have not used it in ages, nor am I a WQL expert
Post #1462664
Posted Wednesday, June 12, 2013 8:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
sneumersky (6/12/2013)
Can SQL_Enthusiast's issue be solved with a WMI task or data reader? I have not used it in ages, nor am I a WQL expert

A WMI Data Reader, maybe, since you can issue an "select" from the file system and the WQL supports the order by. I'll try it out and post back. Would that qualify as "native" SSIS for you Jeff since there is a "WMI Data Reader Task" built into SSIS?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1462671
Posted Wednesday, June 12, 2013 10:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
opc.three (6/12/2013)
sneumersky (6/12/2013)
Can SQL_Enthusiast's issue be solved with a WMI task or data reader? I have not used it in ages, nor am I a WQL expert

A WMI Data Reader, maybe, since you can issue an "select" from the file system and the WQL supports the order by. I'll try it out and post back. Would that qualify as "native" SSIS for you Jeff since there is a "WMI Data Reader Task" built into SSIS?

Upon further review, WQL does not support ORDER BY (or TOP) so I struck out there and could not find an alternate way to "select the newest file in a directory". It might be possible, just saying I could not get it going after some searching online for WQL examples.

You could definitely do this with a WMI Watcher Task having the SSIS process any files that arrived as soon as they arrived, but that might be a little bit much for this particular problem case. If anyone is interested here is an article I wrote that shows how to do that: Using the WMI Event Watcher Task in SSIS to Process Data Files. I would probably stick with the Script Task solution I proposed earlier over the WMI Watcher solution in the article for this simple of a problem case.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1462713
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse