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
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 Related
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!!!
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, 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
I wanted to create a package to copy the objects from one database to another and replace those object if they already exist. Therefore, before the package executes you do not know whether all the objects exist on the target server or only some of them.
Using the 'Transfer SQL Server Objects Task' I have found that I cannot get this to execute cleanly by itself. If I set the 'DropObjectsFirst' to false then an error is thrown if the object exists and if I set it to true then an error is thrown if it does not exist.
In order to get round this I have had to create an 'Execute SQL Task' to list all the objects and then go through them dropping them on the target server in a for each loop before executing the 'Transfer SQL Server Objects Task' with 'Transfer SQL Server Objects Task' set to false.
However, is there a better way of achieving this or am I missing something in the 'Transfer SQL Server Objects Task'?
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.
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.
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
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
In 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 ....
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] ....
I tried creating one simple bar chart report in SSRS, with background fill with pattern "UpwardDiagonal", the stripes on the column when viewed insider report viewer control is quite prominently visible, whereas when exporting the same report to PDF the lines are very thin and dense and hence makes it very difficult to differentiate between one column with pattern inside and one without. I have even tried various other pattern like "LightUpwardDiagonal" and "WideUpwardDiagonal" but all have same result when exported to PDF.why?
View 2 Replies View RelatedHello,
I'm SQL Server 2005 newbie, could you be so kind and help me how to write the most efficient way stored procedure, which checks by PK if specified row exists, then updating the row, otherwise inserting new one.
Thank you for your kind help.
Hello,I created the following SQL script to check if a record exists:IF (EXISTS (SELECT LevelName FROM dbo.by27_Levels WHERE LOWER(@LevelName) = LOWER(LevelName))) Return (1)ELSE Return (0)And I also found in a web page another solution:IF EXISTS(SELECT 1 FROM TABLENAME WHERE LevelName=@LevelName) SELECT 1ELSE SELECT 0- Which approach should I use?- Why "SELECT 1 FROM"?- And when should I use SELECT or RETURN?All I need is to know if the record exists ... nothing else.I will use this procedure on an ASP.NET 2.0 / C# web site.I am not sure if this important but anyway ...Thank You,Miguel
View 6 Replies View RelatedHi,
I was wondering if someone can help.
In vb.net what is the best way to check if a record exists if you are using an sql data reader?
For my application I need to display a button control (make it visible on the page) if a record is available after executing my sql select statement.
cheers
Mark :)
Hi all,I am in the process of creating a page that checks to see if a particular user exists in the database and if it does then send to welcome.aspx if not then accessdenied.aspx. The userid is requested from the query string.I have done some research and cannot find anything directly related, I have tried to add bits of code into what i think is the right place but I dont think what i am doing is correct. Can someone help and show me where my code is going wrong? Also is there a better/more efficient way to do what I am doing?Thanks in advance. default.aspx.csusing System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class _Default : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { string UserID = Request.QueryString["uid"]; //string TransferPage; if (UserID != null) { //initiate connection to db SqlConnection objConnect = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString); string sql = "SELECT COUNT(*) FROM members WHERE UserID = '" + UserID + "'"; SqlCommand query = new SqlCommand(cmd, test); int count = (int)query.ExecuteScalar(); int aantal = -1; // some default value if can't insert record if (count == 0) // no existing record for username { Server.Transfer("accessdenied.aspx"); } else { Session["UID"] = UserID; Server.Transfer("welcome.aspx"); } } }}
View 7 Replies View RelatedDoes anybody can tell me how to check if cursor exists ?
I have a "try and catch" blocks.
I want to destroy the cursor when error occurred and it jumps into "catch" block.
Before I destroy the cursor I want to check if it exists, because the error could occurred before I have declared or opened the cursor.
Hi!
I have created a formview which I among other things uses to insert new values into a database. What I want to check is if the primary key which is put into the form already exists in the db. If it is I want to get a message to my web page, if not the data can be inserted.
How can I do this?
And if the only way to control this is to create a stored procedure. How do I write such a proc?
Hi there,
I am quite new to SQL Server and am having a bit of trouble.
I have created a linked server with the datasource an excel spreadsheet.
I am trying to write a bit of T-SQL that basically checks if the linked server exists. If it does do X, if not do Y.
Can anyone help, its driving me bananas!
I declare a cusror named crInv inside a loop.
Since I open this cursor a lot of times I want to check if is already opened.
I try to use the Cursor_status function but it always returns -3.
The syntax is:
DECLARE crInv SCROL CURSOR FOR
SELECT Val1, Val2 FROM TABLE1 WHERE Val3=450
If Cursor_Status('local','crInv')>0 BEGIN
CLOSE crInv
DEALLOCATE crInv
END
This code is inside a loop.
If I PRINT Cursor_Status('local','crInv') before and after the DECLARE statement it always returns -3.
What is wrong??
Best regards,
Manolis
Hi,
This my first time using the link server to read the files directly from within SQL2005, so issues the following link:-
EXEC sp_addlinkedserver
@server = N'MYVFPSERVER', -- Your linked server name here
@srvproduct=N'Visual FoxPro 9', -- can be anything
@provider=N'VFPOLEDB',
@datasrc=N'"C:PROGRAM FILESMICROSOFT VISUAL FOXPRO 9Samplesdata estdata.dbc"'
After that i can open query and do the import issues, but how can check if the table exists before issues the query.
Best regards
Hello all.
Can anyone tell me how to go about establishing if a particular view exists on a table?
Thanks.
Hi,
I'm wondering is there a command that I can use to check if a certain value exists in the database? Something that returns true or false perhaps?
SELECT * FROM Customers WHERE ID ='1'
Ok so that's simple, but how can I return a true or false if '1' is a value that exists in the db?
thanks.
Hello all.
Can anyone advise me how to check to see if a particual triggers exists through a SQL script?
Thanks
Is there a way in T-SQL to check to see if a #tempTable exists? I want to write a proc the uses a temp table, but I first need to see if the table already exists. if it does I want to drop it, otherwise skip
View 23 Replies View RelatedWell, actually, as in the title. I have a table. The script should add a column if that column doesn't exist already. I use VB to combine the two queries. So what I want:
Query1: Does the column exists:
if No,
query2: create the column
How can I achieve this?
Is there any way to check whether the login exists before creating login ?
create login should be executed after check.
thanks,
Sreenath
What’s the easiest way to check if a single value exists in a table column? I’m building a simple login page, and I want to get the username and check if it exists in my Users table. Here’s my SQL: SELECT UserID FROM UsersWHERE UserID = "admin" Could someone give me code to check for “admin” in my UserID column? Here’s some code I tried, using my SqlDataSource, but it returns an error “Could not load type 'System.Data.OleDb'” protected void Button1_Click(object sender, EventArgs e) { // Retreive the results from the SqlDataSource as a DataReader OleDbDataReader reader = (OleDbDataReader) SqlDataSource1.Select(DataSourceSelectArguments.Empty); // Read in the value if (reader.Read()) { } // Close the reader reader.Close(); } I don’t have to use the SqlDataSource, but originally thought it might be easier. I know how to use SqlDataSource to fill a whole GridView but this is different.
View 2 Replies View RelatedI was able to get this code to work but now I get a SQL error if you try to submit the same information twice. How can I add a message saying that the "email" already exists in database without the SQL error? protected void Button1_Click1(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=123456; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("INSERT INTO [main] (, [userid], [fname], [lname], [degree]) VALUES (@email, @userid, @fname, @lname, @degree)", conn); conn.Open(); cmd.Parameters.AddWithValue("@email", email.Text); cmd.Parameters.AddWithValue("@userid", uscid.Text); cmd.Parameters.AddWithValue("@fname", fname.Text); cmd.Parameters.AddWithValue("@lname", lname.Text); cmd.Parameters.AddWithValue("@degree", degree.SelectedItem.Value); int i = cmd.ExecuteNonQuery(); conn.Dispose(); }
View 5 Replies View RelatedBasically, i am still relatively new to ASP.net and SQL. And i have the following query.
I have a skills table to which the user enters their skills using the following fields: Skillcatagory, SKill, Current Level, Target Level, target date and comments and the serial of the user.
I need to check via our staff table, which people have had a skill entered for them. And then produce a report on who has not had a skill entered for them.
This table has a serial of the user column aswell which is unique.
If there is more information that i can give you to assist me, please ask me.
You help would be greatly appreciated.