November 5, 2010 at 5:09 pm
I have a package that needs altering. What I have are files that I am moving to a different server location based on its name. I have the folder and a partial filename, but I will not know that last few characters.
Ex: ..\folder\filename_YYYY_MM_DD_*.pdf
The * is some time value that will change depending on file completion which will vary. I am trying to find a way to continue to use the file.exists(\folder\filename_YYYY_MM_DD_ <some way to grab or ignore the time variable> .pdf) or grab the file regardless of this time variant.
There will always for only one file with the same name and date in that folder
Had it working till the vendor changed the filename to include finish time.
Any help would be greatly appreciated
November 6, 2010 at 12:51 pm
I guess you are using a script task here with VB.NET?
Try using the DirectoryInfo and FileInfo, you can use wildcards there.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 8, 2010 at 11:15 am
You'll have to excuse me as I am not well versed in VB. Again I am trying to locate a pdf file that was created on x day and has roughly x*pdf name. Here is the code that is currently being used
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
'Dim TCopy As String
Dim mdate As String 'Ending Date Format
Dim mmonth As String 'Month portion of final date
Dim mday As String 'Day portion of final date
Dim myear As String 'Year portion of final date
Dim wday As String 'Week portion of the final date
Dim mdate2 As String 'Alternate Ending Format exluding days
Dim mdate3 As String 'Alternate Ending for Executive Dashboard report scheduler formats
Dim mdate4 As String 'Alternate Ending for Executive Dashboard report scheduler formats
Dim wyear As String 'Week/Year for Executive Dashboard report scheduler format
...
'Weekly Roll Up Reports
' First check to see if the file exists, delete existing destination file, and move_rename file to webserver
If File.Exists("..\Divisional_Summary_rollup" + mdate4 + ".pdf") Then
If File.Exists("..\reports\dailysummary\Region_Rollup.pdf") Then
File.Delete("..\reports\dailysummary\Region_Rollup.pdf")
Else
End If
File.Move("..\Divisional_Summary_rollup" + mdate4 + ".pdf", "..\reports\dailysummary\Region_Rollup.pdf")
Else
End If
This code has been working great until recently, as explained above. What would be the best route to take to get the file(s) that are in the folder and move them. Again I am not a scripter in VB so the more detail the better. I am trying to use this in an SSIS job that is run daily.
November 9, 2010 at 12:17 am
Chris,
A design involving no Script Task/Component would be a bad idea considering the scenario you have in hand. Though it might be possible!!!
Raunak J
November 9, 2010 at 12:22 am
I don't have access to my code right now, but I'll post a script later that will check a given directory for files that match a certain filename, with wildcards allowed.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 9, 2010 at 12:34 am
It seems that you are hoping that someone here will actually write the code for you. The people who respond here are extremely helpful, yet are generally pushed for time as we mostly have full-time jobs to perform. Most, I believe, offer help because they enjoy passing on their knowledge to others and watching their development.
The people that get the best responses are therefore those who demonstrate, through their posts, that they have listened to any advice or suggestions given and then gone away to try and make things work in the light of those responses. If you do not follow that question, response, try it, follow-up cycle, you will find that respondents start to suspect that you are trying to get them to do all of the work for you, ie to provide free consulting, and that is what I'm feeling here.
On the other hand, if you are happy to slip someone $50 via Paypal to write that script for you, we're in happier territory and I'm sure that you will get some takers.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 9, 2010 at 12:35 am
da-zero (11/9/2010)
I don't have access to my code right now, but I'll post a script later that will check a given directory for files that match a certain filename, with wildcards allowed.
Stop undermining my post! 😀
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 9, 2010 at 12:39 am
On the other hand, if you are happy to slip someone $50 via Paypal to write that script for you, we're in happier territory and I'm sure that you will get some takers.
SSC should have a like button 🙂
Raunak J
November 9, 2010 at 2:26 am
Phil Parkin (11/9/2010)
da-zero (11/9/2010)
I don't have access to my code right now, but I'll post a script later that will check a given directory for files that match a certain filename, with wildcards allowed.Stop undermining my post! 😀
Well I'm sorry 😀 (but I did post it before you :-))
I do agree with your point, but I happen to have a script lying around here that does the same thing the original poster is asking, so it is just copy paste for me.
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
' Create a variable to hold the upload directory:
Dim di As New IO.DirectoryInfo(Dts.Variables("myDirectory").Value.ToString)
' Create an array with all the files found in the upload directory that matches the search pattern.
Dim aryFi As IO.FileInfo() = di.GetFiles(Dts.Variables("myFileName").Value.ToString + Dts.Variables("myFileType").Value.ToString)
'Create a variable to serve as an iterator through the for loop.
Dim fi As IO.FileInfo
' Check if there are any matching files at all. If not, the package fails.
If aryFi.Length() > 0 Then
' Move all the files in the array to the right directory.
For Each fi In aryFi
'Create the destination file path.
Dim newfs As String = Path.Combine(CStr(Dts.Variables("myArchive").Value), fi.Name())
fi.CopyTo(newfs, True) 'Copy the file to the new destination.
fi.Delete() 'Delete the file.
Next
'The script and the package succeed.
Dts.TaskResult = Dts.Results.Success
Else
'Fail the script and the package.
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
This script will check a directory (provided through a variable) for files with a certain filename. The filename consists of two variables, the name itself and the filetype (extension). You can place the wildcard character anywhere you want.
Example: "TestFile*" + ".xls" --> this will search for all excel files that start with TestFile.`
The found files are then moved to an archive folder (also provided through a variable). If no files are found, the package fails.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 9, 2010 at 2:35 am
Well I'm sorry [BigGrin] (but I did post it before you [Smile] )
Yeah I know. I was in full artistic composition mode while you nipped in and posted a quickie under the radar 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 9, 2010 at 10:17 am
Thanks for the response and code example. This gives me a better idea on how to use it. I will have to give it a try.
SSCRAZY: I am not trying to get someone to do the work for me. as I stated I am not familiar with VB, and was simply trying to get some direction on how to use the directed fileinfo(). I do not claim to be a VB scripter at all. Yes, I will also admit to being a little bit of a scipt kitty. But as soon as I can see how things are being used in the context I am trying to use it I am able to design my own working scipt.
Thanks for the concern and help and we all understand that we are all busy and collective collaboration is always useful and encouraged.:-D
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply