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 Friday, May 31, 2013 10:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
For some unknown reason, the creator of this file set the naming convention to something I can't begin to fathom...

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???
Post #1458783
Posted Friday, May 31, 2013 4:01 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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...

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

(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 #1458893
Posted Saturday, June 1, 2013 8:47 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
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.
Post #1458980
Posted Monday, June 3, 2013 9:45 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(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 #1459520
Posted Thursday, June 6, 2013 1:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
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

Post #1460862
Posted Sunday, June 9, 2013 4:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1461358
Posted Sunday, June 9, 2013 9:01 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(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 #1461371
Posted Sunday, June 9, 2013 9:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1461372
Posted Monday, June 10, 2013 8:11 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(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 #1461575
Posted Monday, June 10, 2013 9:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1461626
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse