SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211893 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38902 Visits: 14411
Eh, and really, who needs it? :-D

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211893 Visits: 41977
opc.three (6/10/2013)
Eh, and really, who needs it? :-D


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38902 Visits: 14411
Jeff Moden (6/10/2013)
opc.three (6/10/2013)
Eh, and really, who needs it? :-D


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

You mentioning Surface Area...laughable :-P

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
lshanahan
lshanahan
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1487 Visits: 438
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211893 Visits: 41977
opc.three (6/10/2013)
Jeff Moden (6/10/2013)
opc.three (6/10/2013)
Eh, and really, who needs it? :-D


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

You mentioning Surface Area...laughable :-P


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tom Hamilton
Tom Hamilton
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3469 Visits: 792
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
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38902 Visits: 14411
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
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211893 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211893 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search