In SSIS, I need an easy way to see if a file exists, and if not wait for it until a timeout period expires. Here are the options I've discovered, along with the issues I've had:
a) The File Watcher task from www.sqlis.com
This was my first attempt. The task works great, BUT only detects when there is a change on the file. If the file already exists, it keeps waiting which is not the behavior I need. b) The WMI Event Task
There is very sparce documentation on this event and how to write a WQL query. There are numerous examples of monitoring a folder and if any files appear, cause an event to happen. I need to detect for a specific file. I found maybe one example of this using "PartComponent" but wasn't able to get the sytax right to make it work for me. I also need to access a remote file share using a UNC path (e.g. \servernamepathfile.txt) which I could not get to work. c) Script Task using the File.Exists() method
I imported the System.IO namespace, and used a File.Exists(\servernamepathfile.txt) with actual success, but am not sure of the best way to continue to wait if the file is not found immediately. I also want to modularize this approach so I can wait for several files simultaneously so was thinking of implementing this script task as a package by itself to accept variables (filepath & timeout period) but need to know if anyone has had success with this approach. I'm open to suggestions or ways to get options a) and b) to work for my needs. Thanks! Kory
I need to check if a file exists (using a filename pattern as the filename will be dynamic) in a directory.
I am trying to use the File.Exists() method in a Script Task. However, it does not allow me to do something like - File.Exists("C:Files*.xls") Or File.Exists("C:Filesabc*.xls")
DECLARE @File_Exists INT EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUT print @File_Exists
1
And if check folder, can use "nul", but it doesn't work for UNC path
DECLARE @File_Exists INT EXEC Master.dbo.xp_fileexist 'serverBSQL_Backup ul', @File_Exists OUT
print @File_Exists 0
If use xp_subdirs like:
EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'
If the folder doesn't exists,
Msg 22006, Level 16, State 1, Line 3 xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'
How to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.
I need to create a script task in sql server 2008 R2 to check if a file exists in a directory. For example, to see if output.dat exist under c:results. If the file exists, then send out an email stating the file exists, if not then send out another email stating the file does not exists.I noticed there is a huge difference between the script task in sql 2005 and sql 2008 r2.
DECLARE @File_Exists INT EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUT print @File_Exists 1
And if check folder, can use "nul", but it doesn't work for UNC path
DECLARE @File_Exists INT EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupul', @File_Exists OUT print @File_Exists 0
If use xp_subdirs like:
EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'
If the folder doesn't exists, Msg 22006, Level 16, State 1, Line 3 xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'
how to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.
i'm trying to write this script that check my database file and log size(in MB) and insert them into a table.i need the following columns dbid,dbname,compatability_level,recovery_model,db_size_in_MB,log_size_in_MB. i try to write this a got stuck. select sysdb.database_id,sysdb.name,sysdb.compatibility_level, sysdb.recovery_model_desc,sysmaster.size from sys.databases sysdb,sys.master_files sysmaster where sysdb.database_id = sysmaster.database_id
I need to set up create a package so that I could check the date of the files posted in a folder, e.g. H:source. If there is no file created later than one day exists, then continue to check again one hour later. If files do exists, then copy then to c:dest and then upzip the files. Once this is done, sent an notification email to user@mydomain.com.
In my script task I have the following code. The task I'm trying to accomplish is: If the filename on FTP can be found in the local archive folder of e: drive then show message "FileAlreadyThere" (I will ultimatley change it to do nothing); if the filename on FTP cannot be found in the local archive folder of e: drive then transfer the file to the local package folder on d: drive.
While the script task is executing I was watching it closely, but the problem i saw is that: If some files on FTP are already in local archive folder and some are not, then it the files which are already in the archive folder are dumped to the package folder; then after that the files which are not in the archive folder are then dumped to the package folder. But I only want the new files on FTP to be transferred to the package folder for further processing.
Then after this is finished, I saw all the files in the package folder are refreshed one after another, after the first round of refresh the second round starts, after the second round finishes it then stopped. I saw it refreshes itself because the 'Date Modified' of the file changes. And I saw the script task turned green.
I don't see how the code below produced this result. Something is wrong in the logic of the loop? Anyone has any idea why it's behaving the way it is now? And how to change the code to accomplish what I want? Thanks a lot!!
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Imports System Imports System.IO Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Dim cm As ConnectionManager = Dts.Connections.Add("FTP") cm.Properties("ServerName").SetValue(cm, "ftp2.name.com") cm.Properties("ServerUserName").SetValue(cm, "username") cm.Properties("ServerPassword").SetValue(cm, "password") cm.Properties("ServerPort").SetValue(cm, "21") cm.Properties("Timeout").SetValue(cm, "0") cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb cm.Properties("Retries").SetValue(cm, "1") Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing)) ftp.Connect() ftp.SetWorkingDirectory("/directory") Dim fileNames() As String Dim folderNames() As String ftp.GetListing(folderNames, fileNames) If fileNames Is Nothing Then MsgBox("NoFileOnFTP") Else Dim fileName As String For Each fileName In fileNames If File.Exists("c: emp" + fileName) Then MsgBox("FileAlreadyThere") Else ftp.ReceiveFiles(fileNames, "c: emp", True, True) End If Next End If
yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET. I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page. Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer. Cheers ~ J
I need to set up a package to copy a file from a network share to a server. Before the copy job, I need to check whether the file, e.g. test.txt, is created within one day, if not, then check again an hour later.
1)My source files[.txt] come from an FTP server.I need to find a way to include within my SSIS package,a task that would watch a certain folder of the FTP Server and send a mail notification as soon as the file arrives.Tried using filewatcher task http://www.sqlis.com/23.aspx, but the task stays "yellow" for a long time while package is being excuted.I dont know if this is what I need to use.Are they any other task/ways?
Another Problem I'm facing is;
2)If the source file coming in the FTP Server is zipped.How do I unzip the file and move it back to the FTP Server folder for the task in 1) to watch on it .Any ideas ?
Hi, I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel. So I want to include a code that checks if file "file.xls" is open on user's machine. If file is open, then message "file "File.xls" is already open. Generating File_1.xls" Run the code but create the file with file name "file_1.xls" If file doesn't exist, then run code and create file with file name "File.xls"
So basically I want the code to run and generate the file. Only difference is that if file with same name is already open, then just rename the newly created file.
Here's the code I've created for generating the file:
Public Function getrmpricing() Dim queryoption As String Dim ans, Msg As String Dim fs As Object Dim sTemplateFile As String Dim e_TemplateFile As String On Error Resume Next
First I want to thank everyone that has given help to me and everyone else with the issues involving migrating to 2005... Thanks alot..
Now for the problem. I am looking for (an not finding anything of help) to check the date of a file on an ftp server. A file always exists but once a month the day changes. I would just download the file and check it locally but the files are several hundred megs in size so that would be inefficient.
So is there anyway to do that??
On another note, can anyone point me to a good resource for learning the scripting language that SSIS uses??
We have a really annoying job here that relies on a particular file to be created before several imports run. An old file may already exist, but if it isn't recent, we don't want the import to run. This job can't delete it, since other jobs use that file. What we'd like to do is to be able to check the creation date of the file, and if it is after a certain time of day, run the import, else, delete the file. I know of xp_fileexists. Is there anything similar in SQL that can return file information or am I stuck parsing the output from xp_cmdshell 'dir F:ftpcoreinputready.txt'? Any help or hints are appreciated. Let me know if you need more info. Thanks. -D.
Everyday they are sending to our servers a file named like CC_20080501 . it is CC_YYYYMMDD So I have to check it and move into the database table. I am thinking to create standard database connection and name like CC_std And When the file arrives to create something like CC_std (actually copying my daily file CC_YYYYMMDD to CC_std ? BUT how can I do it? any other way to do it?
I'm attempting to check to see if a file exists on our server before showing a link it.
I put this function in the Report Properties:
Code Block Function isFile(PathFileName As String) As Boolean Return isFile = System.IO.FileExists(PathFileName) End Function
I chose a situation where I'm getting back two rows of data, one row is true and the other row is false however, when I run the report they're both coming back as 'True'.
We created a virtual directory to the file system that houses our files, so my PathFileName variable will contain something like http://Sales/North/Sales110907.pdf.
Can I use the FileExists in this fashion or am I going down the wrong path?
As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.
However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.
So i want to write a job that checks the percentage space used for all databases.
My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @DBNAME' and execute it the cursor context stays local you do not actually change database context.
So how do i loop though databases, i have found
sp_msforeachdb, however this is undocumented in BOL.
Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.
Obviously i would rather use documented methods.
So bottom line what tsql could i use to check the percentage of file space use for all databases?
I've production sql server 7 sp3 on windows NT. I had a 8GB data file ofwhich 5GB were used and 3GB were unused. I wanted to take back the unused3GB.So I did the following with EM GUI:1. I tried to "truncate fre space from end of the file". Didn't truncatethe file. I believe there was no empty space at the end of the file.2. Next I chose the option to "shrink file to 5GB". And to my horror thedata file instead of taking just 5GB took the empty spaces also and the sizeof the used data file went to 8GB.Any idea what's going on?TIA,SP
Here's a really annoying problem. Let's say you have a text file with 2 million rows.Delimiters all look good and rows are previewed well but the file has a missing row at say lin 1234567 - way deep in the file. When SSIS encounters the blank row, an error is raised and processing on the file STOPS! I verified this in by checking the SSIS log and have even developed an error routine to notify me via email when the error occurs (really cool if I do say so myself ). The main problem still remains - how to resume processing from the point of failure in the file? Any help is appreciated. Thanks.
Hi I am using bcp command to export data from a table to a text file. I want to be able to save this output from bcp into log.txt how can I do that.
this is the output that I need to save it , If I run the bcp command from sql window, this output is shown in the result window, but I need to save it in a text file in c: thanks for your help
Ali
output NULL Starting copy... 1000 rows successfully bulk-copied to host-file. Total received: 1000 1000 rows successfully bulk-copied to host-file. Total received: 98000 1000 rows successfully bulk-copied to host-file. Total received: 99000 1000 rows successfully bulk-copied to host-file. Total received: 100000 NULL 100491 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total 85143 Avg 0 (1180.26 rows per sec.)
We have a database called Itemphotos. In this table is a field called 'PID' picture ID. We have a photos directory with files like 2181.jpg, 2182.jpg, 2184.jpg. The number is the ID number for the picture of the part. If a record exist in the database based on the PID "Primary Key" , check the directory to make sure the jpg file exist. Here is the code I have so far.
use [ItemPhotos] select pid from dbo.itemdata declare @file_path nvarchar(500) declare @file_exists int set @file_path = 'av-sql2c$inetpubphotosmacolaphotos' + [itemdata].[pid]
I have a data flow task inside a foreach loop container which will take multiple excel files and load into a sql table. Once all the excel files are loaded into the table, then at the end one of the column gets updated using execute sql task. Now I am trying to check for a file existence, if the file is not present in the folder then the data flow task should not be executed. Any help is greatly appreciated, I am thinking of using file system task for this, but not exactly sure. Thanks in advance.
I am deploying programatically an Excel 2007 file to a SQL Server 2005 Reporting Server. The problem is that if a file with the same name already exists, that file isn't replaced. I would like the opposite to happen. I'm using the following code:
--Executable
set svr=http://w3sdwsqld1/reportserver set src_fld="\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\" set dest_fld="Associados" set script="\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss" REM Sample: deploy.bat http://w3sdwsqld1/reportserver "\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\" "Associados" "\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss" for /R %src_fld% %%f in (*.xlsx) do rs -i %script% -s %svr% -v ParentFolder=%dest_fld% -v reportP="%%~nf" -v path=%src_fld% PAUSE
--rss Code
' ' Script Variables ' ' Variables that are passed on the command line with the -v switch: ' ' (a) parentFolder - corresponds to the folder that the script creates and uses ' to contain your published reports
' (b) reportP - corresponds to the report to publish
Dim ROOT As String = "/SAD/Ecrans/Ecrans/AM"
Dim definition As [Byte]() = Nothing Dim warnings As Warning() = Nothing Dim parentPath As String = ROOT + "/"+ parentFolder Dim filePath As String = path Dim report As String = reportP
'Create the parent folder Try rs.CreateFolder(parentFolder, ROOT,Nothing) Console.WriteLine("Parent folder {0} created successfully", parentFolder) Catch e As Exception
Console.WriteLine(e.Message)
End Try
'Create shared data source 'CreateSampleDataSource("Solucao_Integrada", "OLEDB-MD", "Data Source=dwareas1;Initial Catalog=SAD_Solucao_Integrada")
'Publish the sample reports PublishReport(report)
End Sub
Public Sub CreateSampleDataSource(name As String, extension As String, connectionString As String) 'Define the data source definition. Dim definition As New DataSourceDefinition() definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated definition.ConnectString = connectionString definition.Enabled = True definition.EnabledSpecified = True definition.Extension = extension definition.ImpersonateUser = False definition.ImpersonateUserSpecified = True 'Use the default prompt string. definition.Prompt = Nothing definition.WindowsCredentials = False
Catch e As Exception Console.WriteLine(e.Message) End Try
End Sub
Public Sub PublishReport(ByVal reportName As String) Try Dim stream As FileStream = File.OpenRead(filePath + reportName + ".xlsx") Console.WriteLine(reportName)
definition = New [Byte](stream.Length) {} stream.Read(definition, 0, CInt(stream.Length)) stream.Close()
Catch e As IOException Console.WriteLine(e.Message) End Try
Catch e As Exception Console.WriteLine(e.Message) Console.WriteLine("Failed to publish report") End Try End Sub --------------------------------------------------------------------------------------------------------------------
Why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:
Please run the script attached to see what the end result is.
This is what I set up last night on my test machine.
1) Create database [FGTest] size 200MB 2) Create table called TEST on primary 3) Insert 40MB of data into test 4) Create another file group called temp in primary size 200MB 5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group. 6) Add another 2 files called DATA2 and DATA3. Both are 200MB. 7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3 8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly
I would expect at this stage to have the following:
FGTest = 13MB, DATA2 = 13MB, DATA3 = 13MB
(40MB of data over 3 files should be about 13 MBish in each file)
What I actually end up with is this:
FGTest = 20MB DATA1 = 10MB DATA2 = 10MB
It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over the remaining files in PRIMARY.
Let me apologize in advance for what I know is a very basic question, but so far I'm not being terribly successful trying to find sample code to learn from.
I'm trying to write (my first!) .Net Script in SSIS to check for the existance of a file (since that seems to be the only way to do it).
I've created the Script Task, with the following settings:
PrecomplieScriptIntoBinaryCode = False (it complained at one point in time (possibly on validation))
Let me apologize in advance for what I know is a very basic question, but so far I'm not being terribly successful trying to find sample code to learn from.
I'm trying to write (my first!) .Net Script in SSIS to check for the existance of a file (since that seems to be the only way to do it).
I've created the Script Task, with the following settings:
PrecomplieScriptIntoBinaryCode = False (it complained at one point in time (possibly on validation))