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)

SQL_Enthusiast

Hall of Fame

Points: 3805
More actions
May 31, 2013 at 10:27 am

#273353

For some unknown reason, the creator of this file set the naming convention to something I can't begin to fathom... :unsure:
The file name is like this: NAME1_NAME2_NAME3_NAME4_NAME5_YYYYMMDD_YYYYMMDDHRMMSS.YYYYMMDDHRMMSS -- Yes, twice...! And the folder continues to grow. So I need to pick up the most recent file...
I have figured out ways to pick up a dynamic name that ends in CSV or TXT (*.csv or *.txt), but I have never tried to figure out a dynamic file type before. If it helps, the NAME#'s never change. Only the date/times change.
Is there anythign I can do pick up the most recently added file in a folder where the file name and type continually change? Maybe a VB script???

Viewing 15 posts - 1 through 15 (of 39 total)

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

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

SQL_Enthusiast (5/31/2013)
For some unknown reason, the creator of this file set the naming convention to something I can't begin to fathom... :unsure:
The file name is like this: NAME1_NAME2_NAME3_NAME4_NAME5_YYYYMMDD_YYYYMMDDHRMMSS.YYYYMMDDHRMMSS -- Yes, twice...! And the folder continues to grow. So I need to pick up the most recent file...
I have figured out ways to pick up a dynamic name that ends in CSV or TXT (*.csv or *.txt), but I have never tried to figure out a dynamic file type before. If it helps, the NAME#'s never change. Only the date/times change.
Is there anythign I can do pick up the most recently added file in a folder where the file name and type continually change? Maybe a VB script???

What are you using to do imports for things that end with CSV or TXT?

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

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 2

Normally SSIS flat file source inside a data flow task that uses a variable to look for anything in a directory that ends with *.csv or *.txt.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

I have no clue how to do this in SSIS simply because I don't useSSIS but here's the way I'd do it in T-SQL...

CREATE TABLE #Files

(

RowNum INT IDENTITY(1,1),

FileObject VARCHAR(500),

Depth SMALLINT,

IsFile BIT

)

;

INSERT INTO #Files

(FileObject,Depth,IsFile)

EXEC xp_DirTree 'C:\Temp',1,1

;

WITH

cteEnumerateExt AS

(

SELECT FileObject,

SortOrder = DENSE_RANK() OVER (ORDER BY SUBSTRING(FileObject,CHARINDEX('.',FileObject)+1,500) DESC)

FROM #Files

WHERE ISDATE(SUBSTRING(FileObject,CHARINDEX('.',FileObject)+1,500)) = 1

AND IsFile = 1

)

SELECT FileObject

FROM cteEnumerateExt

WHERE SortOrder = 1

;

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

sneumersky SSCertifiable Points: 7667 More actions · Answer 4

Not sure if this helps, but I have this in my toolbox for the datetime portion--something similar I had to do a few years back. In my database the "util" schema is for generic functions--similar to what the ETL design team on Microsoft PROJECT REAL did back in 2005.

Steven J. Neumersky, CBIP, MCITP

CREATE FUNCTION [util].[uf_yyyymmddhhmiss] (@DT datetime)

RETURNS char(14)

/* Takes a date as input and returns the year, month, day, hour, minute, and second in ISO

format.

Example: select util.uf_yyyymmddhhmiss('1/1/2013')

select util.uf_yyyymmddhhmiss(getdate())

*/

AS

BEGIN

declare @result char(14)

select @result =

convert(varchar(8), YEAR(@DT)*10000+MONTH(@DT)*100+DAY(@DT))

+ convert(char(2), CASE WHEN DATEPART(HH, @dt) < 10 THEN '0' + CONVERT(char(1),DATEPART(HH, @dt)) ELSE CONVERT(char(2),DATEPART(HH, @dt))END)

+ convert(char(2), CASE WHEN DATEPART(MINUTE, @dt) < 10 THEN '0' + CONVERT(char(1),DATEPART(MINUTE, @dt)) ELSE CONVERT(char(2),DATEPART(MINUTE, @dt))END)

+ convert(char(2), CASE WHEN DATEPART(SECOND, @dt) < 10 THEN '0' + CONVERT(char(1),DATEPART(SECOND, @dt)) ELSE CONVERT(char(2),DATEPART(SECOND, @dt))END)

RETURN @result

END

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

Note that xp_dirtree is an undocumented stored procedure.

To find the newest file added to a folder using SSIS a Script Task might be the simplest way.

Some sample pseudo code

Dim latestFile as System.IO.File

For Each(System.IO.File file in System.IO.Directory.GetFiles(..))

Begin

' if the date of the latestFile is before file, set latest file to file

End

' latestFile isbthe file you want to process

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

opc.three (6/9/2013)
Note that xp_dirtree is an undocumented stored procedure.

I absolutely agree. It would be much better to use something that's not only well documented, but tried and true, as well... like xp_CmdShell to call a DOS Dir /b.

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

Jeff Moden (6/9/2013)
opc.three (6/9/2013)
Note that xp_dirtree is an undocumented stored procedure.
I absolutely agree. It would be much better to use something that's not only well documented, but tried and true, as well... like xp_CmdShell to call a DOS Dir /b.

I would strongly discourage anyone from using xp_cmdshell for any reason. I recommend leaving xp_cmdshell disabled and using a managed application programming language like SSIS to interact with the Windows file system.

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

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

And I'll encourage anyone and everyone to turn it on and leave it on because having it on isn't a security problem. Only those that can use it can turn it on ("SA" or "Control Server"). It's like the mice guarding the cheese. Only the honest mice will stay away. Having it turned off lulls people into a false sense of security thinking that any attacker, internal or external, can't turn it on and use it. Besides, even if it were off, an attacker can get to the command line with elevated privs using either CmdExec or OPENROWSET.

The key to security is to limit what the SQL Service and SQL Agent service logins can do. Turning off xp_CmdShell does nothing to keep anyone with "SA" privs (including an attacker) from turning it on. You MUST prevent that and you MUST limit what the services' privs are. Turning off xp_CmdShell doesn't even provide a layer of security.

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

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.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

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.

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 11

Eh, and really, who needs it? 😀

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

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.

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 13

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

shoestringdba SSCertifiable Points: 6226 More actions · Answer 14

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.