Import Dynamic File Name with a Date/Time as the file type (YYYYMMDDHRMMSS)

Question

Import Dynamic File Name with a Date/Time as the file type (YYYYMMDDHRMMSS)

Viewing 15 posts - 16 through 30 (of 39 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

opc.three (6/10/2013)
Jeff Moden (6/10/2013)
opc.three (6/10/2013)
Eh, and really, who needs it? 😀
Everyone who wants to avoid increasing the surface area by firing up SSIS. 😉
You mentioning Surface Area...laughable 😛

What's laughable is that you still think that just turning off xp_CmdShell will somehow decrease the surface area exposed to an attacker. I guess that, technically, you're correct but only for the 3ms that it takes an attacker with "SA" privs to turn it on or create their own replacement for it.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tom_Sacramento SSCertifiable Points: 6298 More actions · Answer 2

Not to oversimplify, but once the newest file is identified one option is to use script to copy it to a predetermined name like workfile.csv. Then when done, dispose or archive.

I have dealt with dynamic names, and I have also coerced them into a 'working file' name, just depends if you need to preserve the original, I usually recommend doing this for audit and recovery.

HTH

Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Tom_Sacramento (6/11/2013)
Not to oversimplify, but once the newest file is identified one option is to use script to copy it to a predetermined name like workfile.csv. Then when done, dispose or archive.
I have dealt with dynamic names, and I have also coerced them into a 'working file' name, just depends if you need to preserve the original, I usually recommend doing this for audit and recovery.
HTH

Thank you for bringing the conversation back to the original intent. Maybe you doing it will make it stick.

I do not have a problem with what you're saying, but I usually would not bother doing this unless it's a decoupled process. If the SSIS Package that determines the newest file is the same one that will eventually process the file then setting a local variable to the name of the newest file and having the Flat File Connection Manager that points to that file take it's Connection String from a Variable built from an Expression then there is no need to rename anything. Once we know the name of the newest file the rest of the SSIS Package can be setup to refer to the name dynamically. In the case of a Script Task this would mean passing in a Read/Write Variable and setting it once the newest file were found.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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

@sql_enthusiast,

If you don't know enough about the scripting language that Orlando (opc.three on this thread) used to complete it, the example working code I gave will work and I believe you can execute it as an SQL Task (don't know the official name of the task, though). As has been identified, it does use an undocumented stored procedure that could become unsupported at any time. Before you let that shake you, remember that it is still available in all current versions of SQL Server from at least 2005 through 2012. Also remember that something doesn't have to be documented to be quickly removed or change shape. 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 :sick:.

I am in no way trying to force such code on you (besides, I've seen you in action and know that couldn't happen if I tried). I am, however, trying to give you options. Before you had none. Now you have at least 3. If you know enough to complete Orlando's pseudo-code, that would certainly be a good way to go when using SSIS (I still can't believe that something like that wouldn't be included in an ETL tool, though).

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

lshanahan (6/11/2013)
Building on opc.three's earlier answer about using a script task (and steering us away from the xp_cmdshell debate), I've used similar logic to pick up the most recent filename in a directory then use an SSIS File System task to move/rename it to a csv file rather than fuddle around with xp_cmdshell, etc.
I haven't looked into it in depth, but I would imagine the permissions would be derived from the proxy account used for package execution in SQL Agent, which would be more secure.

But you DIDN'T steer us away from it. Rather, you drove the conversation headlong into it. AND, like so many others on this thread, you badmouth ("fuddle") something and speak of an alternative that you've "used similar logic to pick up the most recent filename in a directory", but you offered no concrete VB or other code to do it. THAT's what the OP needs help with.

Help the OP. If you have something more than pseudo-code to offer, please post it.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

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 :sick:

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 11

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 :sick:
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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

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

sneumersky SSCertifiable Points: 7667 More actions · Answer 13

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 🙁

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 14

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 15

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[/url]. 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