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,
The Sql Server database can only see the local drive.
I would like to set up a batch file that will copy a SQL Server
backup file from the local drive to the network drive. I would
like to append the file date to the end of the copied file. I
assume a batch file can accomplish this but I am new to batch
file writing. Does anyone have code that they already created
for this sort of task??
Thank you!
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!!!
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??
I 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,
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
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
According to the help for SSIS, one method of deploying an SSIS package
to a SQL Server,
http://msdn2.microsoft.com/en-us/library/ms137565.aspx, is to use the
File...Save a Copy of <package file> as... menu option.
I don't have that menu option at all. And yes, the package is in
focus. My save menu options are simply; Save Selected, Save
<package file> As... and Save All.
I am using Version 9.00.1399.00 of the SSIS Designer.
At one time I did have the Management Studio's CTP installed.
However it was uninstalled before installing the tools from the
Standard Edition. (it would seem like not completely however)
Your help would be greatly appreciated. Thanx much.
p.s. Almost forgot to mention... I am already aware of using the
DTSInstall utility as a workaround. It should be noted, however,
that despite enabling the "CreateDeploymentUtility" property, the
DTSInstall.exe is not copied to the binDeployment directory.
I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View RelatedI have a ssis package where I need to have excel destination. In the Excel file, I need to have few rows with some text and then populate data below the text. One the text is like this:
Data as of: 08/25/2015
if the report ran today, then Data as of will have Yesterday. So, if the user opens that excel file after a week, then user should see same Data as of: 08/25/2015. not today()-day(1).
I was planing to handle on excel side with today()-day(1). but it only works the day it was run. Then the excel file is open after few days later, then it might as Data as of: 08/30/2015 which is not true. It should still stay Data as of:
08/25/2015 on what ever date the excel file is open. The SSIS package runs only once.
How do I handle this so that whenever user open the file, they will see Data as of: 08/25/2015. This is not a column in excel. It is like a description of data in excel.
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 RelatedHello,
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
Or is the datebase name part of the .mdf file?
Regards
Markus
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.
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.
hi,
I need to transfer my database from one computer to another. I am using SQL SERVER 2000. So I made the script of the database and transfered to other computer. Now when I try to run the script it gives me error that (mydatabase name).MDF file missing. So I tried to copy the .MDF file from my computer. but it says that access is denied and sharing volilation. Can anyone please help me that how can I copy the .MDF file and remove the sharing volilation.
Can anyone give best way to copy file from one server to another in the network in an automated way?
Di.
how can i create a mdb file which represents a current database in sql server managment studio? i thought this would be obvious but it's not..
View 1 Replies View Relatedhi guys,
anybody know the syntax in SQL to copy file to other server.
we want to copy our db backup files automaticaly to other server.
thanks
-wei
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.
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
I need to copy files from one machine to another machine. It should be done automatically. Could you please give me a suggestion. I have no idea about this. It should write a script or program. But I have no idea yet,
Thanks
i have other question here, i would like to copy file from server to a PC. I create a script (.dat file):
xcopy \serverNameUsersyyu
\ComputerNamecyyu
Can you guy help me? Many thanks.
hi
i am using isp_backup store procedure to get the daily backup of database and this is work fine but i have copy the database file in network sharing folder to use
following cmd
EXEC master..xp_cmdshell 'D:filename M:foldernam'
M: is network folder to window2003
but their no copy this folder
thank
I have a windows service that connects to a regular sql server 2005 database and basically bulk copies data into a SQL express database. Afterwards, I just want to copy the MDF file into a different directory. I keep getting the "Process cannot access file because it is being used by another process" error. I've tried changing the connection strings but nothing seems to work. I'm closing the connections in the code as well. Here is the code. Any thoughts or help would be appreciated. RefreshDB calls Sync 3 times and if all three calls are successful, it attempts to copy the database file to the specified location. This is where I get the error.
Public Function RefreshDB() As Boolean
Dim success As Boolean = True
Dim tables() As String = {"Job", "Equipment", "PMScheduled"}
For Each tableName As String In tables
If SyncTable(tableName) = False Then
success = False
Exit For
End If
Next
If success Then
'copy the new database to the target directory
Try
File.Copy(My.Settings.DBFilePath, My.Settings.TargetDirectory + "EMField.mdf")
Catch ex As Exception
My.Application.Log.WriteEntry(ex.Message + "(RefreshDB)", TraceEventType.Critical)
Return False
End Try
End If
Return success
End Function
Private Function SyncTable(ByVal tableName As String) As Boolean
Dim reader As SqlDataReader
Dim sourceViewName As String = String.Format("EMField{0}View", tableName)
Dim connectionString As String = My.Settings.EMFieldConnectionString
'insert the path to the database into the connectionstring
connectionString = connectionString.Replace("[DBFilePath]", My.Settings.DBFilePath)
Try
'clear the target table first
Using targetConnection As New SqlConnection(connectionString)
Using truncateCommand As New SqlCommand(String.Format("TRUNCATE TABLE {0}", tableName), targetConnection)
targetConnection.Open()
truncateCommand.ExecuteNonQuery()
targetConnection.Close()
End Using
End Using
'create a datareader from the source database
Using sourceConnection As New SqlConnection(My.Settings.EMLiteConnectionString)
Using readCommand As New SqlCommand(String.Format("SELECT * FROM {0}", sourceViewName), sourceConnection)
sourceConnection.Open()
reader = readCommand.ExecuteReader
BulkCopy(tableName, reader, connectionString)
reader.Close()
sourceConnection.Close()
End Using
End Using
Return True
Catch ex As SqlException
My.Application.Log.WriteEntry(ex.Message + "(sync)", TraceEventType.Critical)
Return False
End Try
End Function
Public Sub BulkCopy(ByVal tableName As String, ByRef reader As SqlDataReader, ByVal connectionString As String)
Try
'bulk copy from source to target database
Using bulkCopy As New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = tableName
bulkCopy.WriteToServer(reader)
bulkCopy.Close()
End Using
Catch ex As SqlException
'throw exception back to calling sub
Throw ex
End Try
End Sub
Hi Guys,
What approach should I use to copy content of a text file. Is BCP capable of doing this? How about SSIS?
Example of the text file's content:
Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90
In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.
Regards,
Lars
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)