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 Monday, June 10, 2013 12:32 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
opc.three (6/10/2013)
If anyone is interested in taking the detour named "xp_cmdshell" (I am not) please see these posts below where Jeff, and sometimes myself too, espouse the merits and demerits of xp_cmdshell. Please read the discussions as well as Microsoft MVP's comments and Best Practices documentation available all over the internet and in books have said on the topic and make up your own mind.

If you could use xp_CmdShell securely, would you?
Editorial: The Command Shell
x-cmdShell access
How to prevent ANY use of xp_CmdShell?
How to call a batch file to execute from an SP
Why powershell?

@SQL_Enthusiast, I apologize for the potential derailment of the thread that you started to hopefully get some help writing some lines of code to help you with your project. Hopefully my previous post to this one addressed your original question about how to find the newest file in a directory using a VB.net Script Task in SSIS. If not, then feel free to send me a Private Message, or it might be easy to simply start a new thread.


And please understand that some "Best Practices" are merely perceptions on the part of the writer. Microsoft, for example, makes no claims as to the accuracy of even the latest security document. Keep in mind that such "official" documents say "Use only if needed" and that changes to "Use if needed" if you need 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."

(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 #1461711
Posted Monday, June 10, 2013 12:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 7,125, Visits: 12,721
Eh, and really, who needs it?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1461713
Posted Monday, June 10, 2013 1:07 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
opc.three (6/10/2013)
Eh, and really, who needs it?


Everyone who wants to avoid increasing the surface area by firing up SSIS.


--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 #1461735
Posted Monday, June 10, 2013 1:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 7,125, Visits: 12,721
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


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1461758
Posted Tuesday, June 11, 2013 5:57 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 140, Visits: 260
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.


____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
Post #1462017
Posted Tuesday, June 11, 2013 8:10 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #1462151
Posted Tuesday, June 11, 2013 8:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:00 AM
Points: 2,306, Visits: 597
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/
Post #1462182
Posted Tuesday, June 11, 2013 8:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 7,125, Visits: 12,721
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
Post #1462190
Posted Tuesday, June 11, 2013 8:58 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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 .

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

(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 #1462441
Posted Tuesday, June 11, 2013 9:12 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #1462442
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse