How Can I Check File Size Equal Zero Or Not With Scrip Task ?
Mar 15, 2007
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
View 8 Replies
ADVERTISEMENT
Feb 22, 2008
Hi, how to check the log file size if more than 10 Mb, then shrink it to the target size?
Thanks.
View 6 Replies
View Related
Apr 27, 2008
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
View 13 Replies
View Related
Jul 31, 2007
I have two procedures (old and new) that return queries and have to check that for every possible parameter they return the same data. Any hints?
Thanks, Pablo
View 13 Replies
View Related
Jul 3, 2006
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
View 8 Replies
View Related
Jul 3, 2006
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
View 1 Replies
View Related
Dec 6, 2007
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
View 3 Replies
View Related
Mar 17, 2015
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
Aug 7, 2015
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 ....
View 4 Replies
View Related
Mar 14, 2007
Hi,
can any one help me , i am facing an issue on SSIS mail task, i want to attach a file of size 2 MB. it's getting error :
[Send Mail Task] Error: An error occurred with the following error message: "The operation has timed out.".
thanks,
sun.
View 4 Replies
View Related
Nov 13, 2015
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] ....
View 4 Replies
View Related
Sep 4, 2007
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
View 1 Replies
View Related
Mar 10, 2008
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!!!
View 5 Replies
View Related
Jun 14, 2006
Dear Friends,
In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.
Now, I have to show two set of results.
A. List of Companies as per selection ( dataset with equal to selection )
B. List of Companies which are not selected ( ie dataset with not equal to selection )
I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.
Please advice. Thanks.
warm regards
Rakin
Singapore.
View 6 Replies
View Related
May 20, 2015
I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies
View Related
Apr 15, 2008
I have a log file that is approximately 50 GIG. I backed up just the log and the file size of the .bak is 192 GIG . Why is this? Shouldn't it be closer to the 50 GIG.
Normally I wouldn't let log grow this much. But we are in process of getting new server up and running and don't have backups going yet. They are working on getting that up and running this week.
So I did a log backup to give me back some log space for now but was concerned when I saw the size of the .bak file.
When I view media contents of the backup device it shows one tranaction log back up and size of 192 GIG.
What is up with this. I know in SQL 2000 the log backup files where never this big. they were about the size of the log itself.
Any ideas?
Stacy
View 8 Replies
View Related
Jun 15, 2006
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
View 11 Replies
View Related
Jan 16, 2004
I wanna to have a script that will read a sertain table in a database. If a csrtain record that is being added exists in the table already I want it not to be added into teh specific tables. If it does not exist, i want it to be added.
Here is the logic that I wanna have:
READ OPCSHTO
GET CUST_CODE + LOC_CODE
IF EXIST CUST_CODE + LOC_CODE RECORD IN DPTORGANIZATIONSLOCATIONS
GO TO READ OPCSHTO
ELSE
DO ADD TO DPT.ORGANIZATIONLOCATIONS TABLE
DO ADD TO DPTORGANIZATIONS
View 6 Replies
View Related
Jun 29, 2004
Hi,
How do I check what is the actual DB size that is currently being use?
e.g. I set 10 GB as initial size. A few days and a few transactions later, how can I know how much was used since it is still under 10GB and checking the physical DB file will not tell me anything.
View 2 Replies
View Related
Jul 25, 2007
I have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply
View 1 Replies
View Related
Jun 26, 2007
Hi
I am trying to check the size of each table in my database?
SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE
I can't for the lif of me figure out how this is done.
Any help would be greatly appreciated
Kind Regards
Carel Greaves
View 10 Replies
View Related
Sep 24, 2007
thanks
=============================
http://www.sqlserverstudy.com
View 2 Replies
View Related
Jun 19, 2002
I have to deal with an environment where the developers create and modify tables at will, and the tool they use does not check if the table created is over the 8060 limit for a table. I have a sql statement below which checks the size for me, but I can't figure out how to put a where clause on it to check for the size being over 8060. I i have over 1000 tables to check and was going to write a cursor routine to check it and print it, but I need te where clause to work.Any help appreciated
select Name=left(c.name,20),
c.prec, scale = ISNULL(c.scale,0),
t.name from syscolumns c inner join systypes
t on c.xtype = t.xtype where id in
(select id from sysobjects where name = 'mh_demographic2_')
order by c.colid COMPUTE sum(c.prec)
View 1 Replies
View Related
Oct 10, 2007
i'm not able to generate a sql script with the data in the tables! using sql express.
i need this script to ganerate the same database with its datas in a new sql server.
thanks in advance
View 2 Replies
View Related
Nov 30, 2007
HI Experts,
I am having 2 severs (SQL2000 & SQL2005), there is already one DTS package in SQL 2000, now i have migrated DTS package to SSIS package. Now the problem is DTS package is having ActiveX script and it was executing perfectly in 2000 server but after migrated to 2005 server if i am executing the coverted SSIS package it is giving one error and the error is displaying here.
Error 1 Validation error. DTSTask_DTSDataPumpTask_1: OLE DB Destination [181]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server Copy" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. SearsCDCC_Transfer (1).dtsx 0 0
and i am also displaying the ACtiveX code as well and here oma11pngrdb02 is 2000 server and sant01pngrdb is 2005 server.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim gsErrorFile
gsErrorFile = "\oma11pngrdb02App_Data_ReportingSearserrors2.txt"
Function Main()
Main = DTSTaskExecResult_Success
set objftp = CreateObject("ftpx.aspftp")
objftp.sServerName = "10.1.3.175"
objftp.sUserID = "COS.FTP"
objftp.sPassword = "SENDFILE"
if objftp.bConnect then
dim fname, lfname
fname = "$IOMH04.PRDFFTAP.ST462330"
lfname = "\oma11pngrdb02App_Data_ReportingSearscdccfile.txt"
If objFTP.bGetFile (fname, lfname ) then
Main = DTSTaskExecResult_Success
Else
logError("Error getting file: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
End If
Else
logError("Error connecting: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
end if
End Function
Function logError(sErrorMsg)
Dim objErrorFile, objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objErrorFile = objFSO.OpenTextFile(gsErrorFile, 2,True)
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.writeLine FormatDateTime(Now(),vbShortdate) & " " & FormatDateTime(Now(),vbShorttime)
objErrorFile.writeLine sErrorMsg
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.close
Set objErrorFile = Nothing
Set objFSO = Nothing
End Function
Can any one give me the solution as early as possible, and your help is soo Appreciable
View 4 Replies
View Related
Apr 10, 2014
I need find out the number of columns in flat file before i process that particular file.
I have file name in @filename variable and file path is @filepath variable.
But do not not that how i will check the column name in before i will process that file.
@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And I am using for each loop container to read the file one by one and put the file name in @filename variable.
and my file name like
Product_20120607060930.txt
Product_20130708060930.txt
My file structure is:
ID,Name,City,Country,Phone
1,Riya,Pune,India,454564
2,Jiya,New Jersey,India,454564
3,Riya,St Louis,USA,454564
4,Riya,Belleville,USA,454564
5,Riya,Miami,USA,454564
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file. if it is not there then i have to send mail to client saying that file is not valid.
Let me know how i will do it.I need to also calculate the size of flat file.
View 0 Replies
View Related
Dec 4, 2014
I found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.
I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.
View 6 Replies
View Related
Apr 29, 2015
How can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now
View 4 Replies
View Related
Mar 14, 2008
Hi,
I want to what check database integrity task does in Maintenance plan..
Anyone help.
View 3 Replies
View Related
Apr 23, 2008
I searched the forum on this topic and saw the following explanation on Check Database Integrity Task, by DarrenSQLIS.
"The Check Database integrity task will fail if the DB has a problem. The task fails; the details are raised in the error event and dumped to the log etc. You can use on Failure precedence constraints or an OnError event handler to capture that failure and do something if you wish."
Here is the link to the quote above.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2290253&SiteID=1
I would like to continue this discussion on this new thread to help my understanding.
I understand that this command does dbcc checkdb( ) command on a specified server for the databases you want to check. And if a database has a problem the task will fail and raise an OnError event. DarrenSQLIS goes on to say that the error is dumped to a log. What log is this? Where do I specifiy the location of this log?
View 7 Replies
View Related
Dec 19, 2007
Hi All,
I am using a SSIS package to import the .xml data file from FTP location and process them into SQL Server DB by using Stored Procedures. I am using FTP Task to retrieve the file from FTP location but it only works when ever internet is available otherwise it fails, I placed this package as a SQL Server Agent Job. Can anybody tell me the way to check the CONNECTION before FTP Task start its process to avoid the errors???
Please see the error below which I get from SQL Server Agent:
"Description: Failed to decrypt protected XML node "DTSroperty" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-12-19 04:00:03.66 Code: 0xC001602A Source: AA_Trans Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2007-12-19 04:00:03.67 Code: 0xC002918F Source: FTP AA Transactions Receive FTP Task Description: Unable to connect to FTP server using "FTP Connection Manag... The package execution fails"
Thanks in advance.
Zeeshan.
View 1 Replies
View Related
Apr 14, 2015
Here are my scenarios:
We have an application with replicated environment setup on sql server 2012 . Users will have a replica on their machines and they will replicate to the master database. It has 3 subscriptions subscribed to the publications on the master db.
1) We set up a replica(which uses sql server 2012) on a machine with no sql server on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 33gigs and ldf has grown to 41 gigs. I went to sql server management studion . Right click and checked the properties of the local database. over all size is around 84 gb with little empty free space available.
2) We set up a replica(which uses sql server 2012) on a machine with sql server 2008 on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
3) We set up a replica(which uses sql server 2012) on a machine with sql server 2012 on it. We have dropped the local database and recreated the local db and did the initial synchronization using replmerge tool. The mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
Why it is allocating the space differently? This is effecting our initial replica set up times.
View 0 Replies
View Related
Nov 6, 2015
I am looking to create a Stored Procedure that simply runs a Scheduled Task that they knows when the job has completed.
To call the Job I am using:
EXEC msdb.dbo.sp_start_job @job_name='MY_JOB_NAME'
As a next step is there a way to check if the Scheduled Job has completed and keep checking until the job completes via a query?
View 1 Replies
View Related
