Hi,
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.
Hello World,
I'm new to SSIS and would like a little assistance getting started, if possible...
Here is what I want to do:
Check if file exist (C:DTS UpgradeFilexxx.txt) --->
Archive file (C:DTS UpgradeArchive) --->
Check if file has data (true or false)
AND/OR
If there are any good website that have good direction, let me know
Thanks in advance for your help!!!
I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View RelatedI am wondering how easy is to check for file locks and have our SSIS Package to wait until file has been release by the process which is using it.
Also, same question when we're writing to a Flat File (or Flat File Destination).
Thanks,
Hi,
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
can anyone help me with this script?
THX
Hi,
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.
Thanks,
Is there is a piece for code ot sample code that can let me check if a file exists? Has any one done this before?
Thanks
Hi All,
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, how to check the log file size if more than 10 Mb, then shrink it to the target size?
Thanks.
Hi, can someone tell me how to check if a file exists. If it exists then i want to continue with the process and if it fails then send a mail to me.
Thanks in advane.
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
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
sTemplateFile = g_dashboard & "crm proposal input.XLT"
e_TemplateFile = "C:"
If Forms!rmpricingdataform!BU = "CS" Then
MsgBox "No template available for CS!", vbOKOnly, "RM Pricing Report"
Else
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile sTemplateFile, e_TemplateFile, True
Dim xl As New Excel.Application
xl.Workbooks.Open e_TemplateFile & "crm proposal input.XLT"
DoCmd.OutputTo acOutputQuery, "CustPricingbyRMCrosstabquery", acFormatXLS, "c:customerpricing.xls", True
Dim xs As New Excel.Application
xs.Workbooks("customerpricing").Activate
xs.ActiveWorkbook.Activate
Select Case Forms!rmpricingdataform!BU
Case "CRM"
xl.Run "'crm proposal input.XLT'!CRM_CAPSPriceTemplate.CRM_CAPSPriceTemplate"
End Select
'xs.Workbooks.CLOSE - NEWLY COMMENTED OUT
xl.Workbooks("crm proposal input.XLT").CLOSE
'xl.Workbooks("crmpricing.xls").Save - NEVER USED
'fs.DeleteFile e_TemplateFile & "crm proposal input.XLT", True - NEWLY COMMENTED OUT
Set fs = Nothing
DoCmd.CLOSE acForm, "rmpricingdataform"
Call AuditTrail("RM Pricing report", "Execute")
End If
End Function
Please advise.
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??
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
View 4 Replies View RelatedWe 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.
View 14 Replies View Related
Hi,
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?
thanks,
John
Hi Folks,
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?
I appreciate any help you can give.
Best regards,
Hi There
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?
Thanx
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.)
(106 row(s) affected)
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]
[code]....
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.
Hi all,
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))
EntryPoint = ScriptMain
ReadOnly Variables = ProductBuyListFileLocation, ProductBuyListFileName
ReadWrite Variable = FileFound
Now the ReadOnly variables have the 2 components necessary for a file location, i.e.:
ProductBuyListFileLocation = 'D:TestingProductInformationEnhancements'
ProductBuyListFileName = 'buyList.xls'
So, I'm now trying to check the existance with the following .Net Script code:
-------code--------
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
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()
'
' Add your code here
'
Dim FileLocation As String
Dim DTSVariables As Variables
If
System.IO.File.Exists((CStr(DTSVariables("ProductBuyListFileLocation").Value)
+ (CStr(DTSVariables("ProductBuyListFileLocation").Value)))) Then
DTSVariables("FileFound").Value = 1
Else
DTSVariables("FileFound").Value = 0
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
-------code--------
if fails with this message, which I'm not finding too helpful:
at
ScriptTask_6c9d28f1bd3045739c4237bd588e3835.ScriptMain.Main() in
dts://Scripts/ScriptTask_6c9d28f1bd3045739c4237bd588e3835/ScriptMain:line
28
Is it complaining about not being able to find/address my ReadOnly variable? If so, what am I doing wrong?
TIA
Hi all,
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))
EntryPoint = ScriptMain
ReadOnly Variables = ProductBuyListFileLocation, ProductBuyListFileName
ReadWrite Variable = FileFound
Now the ReadOnly variables have the 2 components necessary for a file location, i.e.:
ProductBuyListFileLocation = 'D:TestingProductInformationEnhancements'
ProductBuyListFileName = 'buyList.xls'
So, I'm now trying to check the existance with the following .Net Script code:
-------code--------
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
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()
'
' Add your code here
'
Dim FileLocation As String
Dim DTSVariables As Variables
If
System.IO.File.Exists((CStr(DTSVariables("ProductBuyListFileLocation").Value)
+ (CStr(DTSVariables("ProductBuyListFileLocation").Value)))) Then
DTSVariables("FileFound").Value = 1
Else
DTSVariables("FileFound").Value = 0
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
-------code--------
if fails with this message, which I'm not finding too helpful:
at
ScriptTask_6c9d28f1bd3045739c4237bd588e3835.ScriptMain.Main() in
dts://Scripts/ScriptTask_6c9d28f1bd3045739c4237bd588e3835/ScriptMain:line
28
Is it complaining about not being able to find/address my ReadOnly variable? If so, what am I doing wrong?
TIA
Hi,
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")
Any help on this?
Thanks in advance.
Regards,
B@ns
Dear all,
Could you tell me which code of vb scrip that i can check file size as follow ?
Dim FSO, Fyl
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fyl = CreateObject("File")
If FSO.FileExists("C: emplcbseg.log") Then
Set Fyl = FSO.GetFile("C: emplcbseg.log")
If Fyl.Size > 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End if
Thanks
Best regards,
Kusuma
Usual way to check if file exists
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.
View 3 Replies View RelatedIn my SSIS Package I am using Script task to check whether file exists or not.
I am having 3 variable as below
User::V_Filepath >File path
User::V_GeographyLevelFileName >Filename
User::V_FileExistsFlg >Flag
Below is the code I am using for same
String filepath = Dts.Variables["User:V_Filepath"].Value.ToString() + Dts.Variables["User::V_GeographyLevelFileName"].Value.ToString();
if (
File.Exists(filepath)
)
{
Dts.Variables["User::V_FileExistsFlg"].Value = 1;
}
MessageBox.Show(filepath);
MessageBox.Show(Dts.Variables["User::V_FileExistsFlg"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
But I am getting exception error.
Find the attached screenshot of error ....
usual way to check if file exists
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.
Now I'm trying to implement logic to check if excel file is opened or not if open send out email in Script task. Using c#
I'm using this one but it's not working fine. always give me file is opened
public void Main()
{
Boolean FileLocked = true;
// Check if the file isn't locked by an other process
try
[Code] ....
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?
mdf file size 5,738,944 KB
ldf file size 10,176 KB
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