Help Please (Check File Exists/ Archive File/ Check If File Empty)

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


ADVERTISEMENT

Check If A File Exists Using Sql

Oct 7, 2003

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

View 2 Replies View Related

How To Check If A File Exists

Jul 25, 2006

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.

View 6 Replies View Related

Check If File Exists

Aug 21, 2006

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

View 13 Replies View Related

Check If A File (pattern) Exists Using A Script Task

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

Transact SQL :: Check If UNC Path Exists (It Is Folder Not File)

Oct 29, 2015

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.

View 8 Replies View Related

SQL Server 2008 :: Check If File Exists Using Script Task

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

SQL Server Admin 2014 :: Check If UNC Path Exists (It Is Folder - Not File)

Oct 29, 2015

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.

View 6 Replies View Related

Data Access :: How To Check All Connection Automatically During Routine Check By Using Batch File

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

Check File For Being Used By Some Other Process When Using Flat File Source

Feb 14, 2007

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,

View 3 Replies View Related

Check Db Size Data File And Log File

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

Check File Date And Copy File

Apr 23, 2007

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,

View 4 Replies View Related

Script Task To Compare File From FTP With File On Local Archive Behaved Strangely!

May 9, 2008

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

ftp.Close()
End Sub
End Class

View 3 Replies View Related

The Database File May Be Corrupted. Run The Repair Utility To Check The Database File. [ Database Name = SDMMC Storage Cardwinp

Oct 9, 2007

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 Related

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.

Sep 2, 2007

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
 

View 3 Replies View Related

Check Whether Or Not A File Is A New One

May 21, 2007

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.



View 1 Replies View Related

Check If File Exist ?

Mar 31, 2008

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 ?

View 10 Replies View Related

How To Check Log File Size?

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

Check If File Is Already Open

Aug 27, 2007

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.

View 1 Replies View Related

Check FTP File Date

Jan 3, 2007

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??

View 5 Replies View Related

Check File Status With Datetime?

Mar 25, 2008

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

How Can I Check And Create The Variable File Name?

May 15, 2008



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

View 6 Replies View Related

Check To See If File Exist On Server

Nov 9, 2007

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,

View 7 Replies View Related

Check All Databases For File Space Used

Aug 29, 2006

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

View 4 Replies View Related

Shrink File Tool From EM Took All The Empty Space Allocated To Data File

Jul 20, 2005

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

View 2 Replies View Related

Flat File Connector Stops Processing File On Empty Row And Generates Fatal Error

Dec 27, 2007

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.

View 13 Replies View Related

Save The Bcp Output In A Text File To Check It Later

Jan 27, 2000

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)

View 2 Replies View Related

Check Directory To Make Jpg File Exist

Mar 6, 2012

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]....

View 7 Replies View Related

How To Check For A File Existence In A Folder In SSIS

Oct 26, 2007

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.

View 13 Replies View Related

Deploy Excel File That Already Exists On Server - File Isn't Replaced

Jan 3, 2007

Dear all,



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


Public Sub Main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

'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

Try
rs.CreateDataSource(name, parentPath, False, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", name)

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

Try
rs.CreateResource(reportName + ".xlsx", parentPath, True, definition, "application/x-excel", Nothing)

Catch e As Exception
Console.WriteLine(e.Message)
Console.WriteLine("Failed to publish report")
End Try
End Sub
--------------------------------------------------------------------------------------------------------------------

Any thoughts? Many thanks,

Pedro Martins

Portugal

View 3 Replies View Related

SQL 2012 :: DBCC Shrinkfile Empty File Not Distributing Data Evenly In Primary File Group With Multiple Files

Apr 29, 2014

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.

View 3 Replies View Related

SSIS Script Task To Check For File Existance

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

SSIS Script Task To Check For File Existance

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







Copyrights 2005-15 www.BigResource.com, All rights reserved