Script Task: How To Compare Files On FTP With Existing Files In Local Folder Before Transfer!

Apr 24, 2008

In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.

I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.

Thanks for the help in advance and i apologize for the long lines of code!

example for step 1:
----------------------------------------------------------------------------------------------------------

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo

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()

Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class

example for step 2:
-------------------------------------------------------------------------------------------------------

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

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()

Try

'Create the connection to the ftp server

Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

'Set the properties like username & password

cm.Properties("ServerName").SetValue(cm, "ftp.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") 'The 0 setting will make it not timeout

cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb

cm.Properties("Retries").SetValue(cm, "1")

'create the FTP object that sends the files and pass it the connection created above.

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connects to the ftp server

ftp.Connect()

'ftp.SetWorkingDirectory("..")

ftp.SetWorkingDirectory("directoryname")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Next

Dim files(0) As String

files(0) = maxname

ftp.ReceiveFiles(files, "C: emp", True, True)

' Close the ftp connection

ftp.Close()





'Set the filename you retreive for use in data flow

Dts.Variables.Item("FILENAME").Value = maxname

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

View 16 Replies


ADVERTISEMENT

FTP Task- No Files In Folder

Dec 2, 2007

Hi,

I'm tring to copy files from FTP address, the problem is that sometimes the FTP folder is empty, and then the FTP Task is failed.
Why is it failed if there are no files? Any suggestion how to avoid the error?


Thanks,
Hadar

View 4 Replies View Related

Integration Services :: SSIS FTP Task Sending Files To Wrong Folder

Aug 4, 2015

I'm using the FTP Task in SSIS to send files. They task succeeds but the files get uploaded to the wrong folder. Instead of being sent to the cg268301 folder they are being sent to the cg268300 despite selecting /cg268301 from the remote path field in the FTP task editor.

I've tried uploading this file to the /cg268301 file using an execute process task and it works fine. I just don't know why it won't work with the FTP task.

View 3 Replies View Related

Moving Files (split From An Existing Thread-SSIS Equivalent To DTS Transform Data Task Properties)

May 7, 2007

Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.



In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.

Linda



Here is the old VBScript Code:

Public Sub Main()

Option Explicit

Function Main()

Dim MovementDataDir

Dim MovementArchiveDataDir

Dim MovementDataFile

Dim MovementArchiveDataFile

Dim FileNameRoot

Dim FileNameExtension, DecimalLocation

Dim CurMonth, CurDay

Dim FileApplicationDate

Dim fso ' File System Object

Dim folder

Dim FileCollection

Dim MovementFile

'======================================================================

'Create text strings of today's date to be appended to the archived file.

FileApplicationDate = Now

CurMonth = Month(FileApplicationDate)

CurDay = Day(FileApplicationDate)

If Len(CurMonth) = 1 Then

CurMonth = "0" & CurMonth

End If

If Len(CurDay) = 1 Then

CurDay = "0" & CurDay

End If

FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate)

'=====================================================================

' Set the movement data directory from the global variable.

MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value

MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value

fso = CreateObject("Scripting.FileSystemObject")

folder = fso.GetFolder(MovementDataDir)

FileCollection = folder.Files

' Loop through all files in the data directory.

For Each MovementFile In FileCollection

' Get the full path name of the current data file.

MovementDataFile = MovementDataDir & "" & MovementFile.Name

' Get the full path name of the archive data file.

MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name

DecimalLocation = InStr(1, MovementArchiveDataFile, ".")

FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1)

FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1)

MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension

If (fso.FileExists(MovementDataFile)) Then

fso.CopyFile(MovementDataFile, MovementArchiveDataFile)

' If the archive file was coppied, then delete the old copy.

If (fso.FileExists(MovementArchiveDataFile)) Then

fso.DeleteFile(MovementDataFile)

End If

End If

Next

fso = Nothing

folder = Nothing

FileCollection = Nothing

Main = DTSTaskExecResult_Success

End Function

View 6 Replies View Related

Task Converting Xls Files To Csv Files ?

Jan 21, 2006

Is there a way to do this with some sort of task ?

View 7 Replies View Related

Run All .sql Files In A Given Folder?

Jun 4, 2008

Hi,

say I have th following in my post-deployment script:

:r ..ScriptsFolderScript1.sql
:r ..ScriptsFolderScript2.sql
:r ..ScriptsFolderScript3.sql
...

How can I do the equivalent of

:r ..ScriptsFolder*.sql
??

I've tried the above and the syntax is not supported.

Your help is much appreciated! =)

View 3 Replies View Related

Get Files In Different Folder Using Sql

Aug 9, 2006

Hi All,I have a multiple files, but they are store in different directory onthe server. I want open those files and insert it into the databaseusing bcp.Example files structure dir:\xyz123abc ext1.txt\xyz123abc ext2.txt\zyz123999 ext2.txtbcp "dabase" in \xyz123abc ext1.txt -c -S"servername' -Usa-Ppassword -T".is there away to loop througth each dir, get the files, excecute thebcp, then go to next folder.Please help. Thanks in advance.Ted Lee

View 2 Replies View Related

Is There A Way To Save All Files In A Folder

Jan 11, 2007

to utf8 format through BCP or throuhg the dos command prompt?

Cheers

View 3 Replies View Related

Import All Xml-Files From A Folder Into SQL

Jun 14, 2006

Hello

I'm just starting with SQL and BIDS. I have created a xml-Import to SQL Server which works without any problem.

Now I like to import (daily) all xml-files of an appropriate Folder at once to the DB. The files will have different names each day.

I could not find any help in the internet to solve this problem.

Can someone give me an Idea how to import many xml-files out of the same folder to SQL?

Thanks & regards

Chaepp





View 4 Replies View Related

SQL 2012 :: Converting XML Files To PDF And Zip In Folder

Jun 16, 2015

Convert 100 xml files individually to pdf's and zip them in a folder along with the source files.

Can it be possible in SQL server BI world?

If possible make this an automated process for every 100 files.

View 3 Replies View Related

SSIS Processes All Files In A FTP Folder

Jan 2, 2007

Hello everyone - wonder if you can help.

How do you process all files in an FTP directory - similar to the for each loop for files.

I need to be able to download each file and then move it to an archive folder on the ftp site.
Might also want to do some things in between.

I have a feeling it means getting the directory listing into a recordset or variable then enumerating that.

Sounds like a common requirement and would be quite easy by other means. Can't help feeling that SSIS has built in tasks to do it.

View 2 Replies View Related

Accessing Files On A Shared Folder

Jan 7, 2008

can sql server 2005 access files on a shred folder (which sql 2000 was not able)?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

View 3 Replies View Related

Why Have SQLCE... Files Under Temp Folder

Mar 14, 2007

I am developing application with SQLCE2.0, NETCF1.0,Sp1,VS2003.

I found there is some files are created under "Temp" folder by the system with size "0B".

Why/when these file are created? Do I need to clean it periodicly? If not, will this cause exception like "Not enough storage is available to complete this operation"?



Thanks.

View 10 Replies View Related

Files Inside RSTempFiles Folder

Oct 15, 2007



Hello;

I started to get out of space on the server C: drive.

I checked and I was using file system storage on the C drive in:

C:Program FilesMicrosoft SQL ServerMSSQL.xReporting ServicessRSTempFiles

I checked the folder and it is 1 GB.

My questions or doubts;

1) I read that the path can be changed without any problem, in the same server we have a D drive with 40GB of space, so I am planning to change the path to: D:RSTempFiles. Any experience on changing that path?

2) If the change of path is successfull, how I can delete the files on the RSTempFiles on the C: drive. Can I directly delete the files? Ther just previous snapshots or I will lose something? Any experience with this will be great.

Thanks everybody and nice weekend.
Luis

View 1 Replies View Related

SSAS 64 Bit Not In Progam Files (x86) Folder

Jan 9, 2008

I have installed SSAS on a 64 bit machine and am trying to connect to Oracle but having little success.
Some of the solutions I've read indicate to uninstall SSAS and then reinstall, making sure that the reinstall does not use the Program Files (x86) folder since Oracle has a problem with the parentheses.
Here's my question... How do you NOT install in the Program Files (x86) folder. It seems SQL Server always wants to put a small part in there.
Thanks in advance and sorry if this is a dumb question.

View 2 Replies View Related

Transact SQL :: Check If There Are Files In Folder

Jul 20, 2015

I'm wondering if its possible to return the number of files in a folder suing something like DIR command.I'm wanting to do something like, If count(DIR) >0 then do something

else End
exec master.dbo.xp_cmdshell
'dir C:Test'

View 2 Replies View Related

Using DTS For Reading Dynamic .CSV Files From A Folder

Sep 20, 2006



Hi All,

I have a challenge i am trying to overcome, hopefully soneone would have come across this issue before..

I am creating a DTS package that will be scheduled to run at a certain time everyday. A source folder exists that get a set of new files everyday.The DTS Package will then read each file and copy the data into a load table in my database the challenge is this:

I am trying to load files from a source folder into my load table, Within each file, the entires are in a specific format using pipes to seperate the data that goes into which column e.g

example of a file entry:

column1 | column2 | column3

data1 | data2 | data3

data1 | data2 | data3

data1 | data2 | data3

And now i am using DTS to specify the file format and map the cloumns as apprporiate to my table...all this is well and good, but my problem is each file has a different name as well as being timestamped, now how do i use DTS to specify the source folder, open each file sequentially and read (or more appropriate, copy the entries into my table, inserting new data from each file into my load table as well as overwriting old data in the load table from the files in the folder ?) is there a way in specifying your source folder in DTS rather than specifying the file in the Menu options (in the transformation data task properties )given, and or do i need to write a script for this(reading the file?)

can someone please give me a solution and how to approach this?

thanks in advance

View 4 Replies View Related

Stored Procedure To Return All Files In A Folder?

Jul 1, 2006

Hi, I am wondering how can we return each file in a folder ? I am trying to get each file in a folder to perform other job.  Below is the description ...
while folder is not empty
        foreach file
            run the job
        end for
end while
 

View 1 Replies View Related

OSQL Command - Execute All Files In A Folder;

Aug 8, 2006

Hi,

How do I execute all the .sql scripts in a folder with OSQL command?

Thanks in advance,

Hari Haran Arulmozhi

View 4 Replies View Related

Rename All Files Located In Folder But Not Working?

Apr 24, 2015

SET nocount ON
DECLARE @fileNameNew VARCHAR(1024) -- filename of new error report
DECLARE @fileNameOld VARCHAR(1024) -- filename of old error report
DECLARE @out_fileDate CHAR(1024)
DECLARE @fileDate DATETIME -- used for file name

[code].....

View 0 Replies View Related

64Bit - Creates Program Files Folder - Why?

Aug 30, 2007

We noticed SQL Server 2005 is creating Program FilesCommon FilesMicrosoft SharedDW on our largest drive for each 64-bit installation. Does anyone know what this is? It appears there is no Microsoft documentation regarding this installation and if we need to keep it. It may be .NET related, but I have no idea why it is needed.

Dave

View 5 Replies View Related

Send E-mail Attach All Files In A Folder

Dec 8, 2006

Is there a way to send an e-mail from SSIS that attaches all the files in a specified folder?

View 5 Replies View Related

Truncate All Raw Files In A Given Folder (Repost As Requested)

Mar 30, 2007



I am looking for a way to truncate raw files without losing the metadata. The metadata of the raw file should be automatically detected at run-time. The result will be a raw file with the same metadata as the original file, but no data in it.



There are two reasons I would like such a tool. First, I want to erase the potentially sensitive data stored in the raw files. Second, I want to keep Validation enabled so that development is simpler.



Is anyone aware of such a tool?



Thanks,

View 4 Replies View Related

Attach Db With Same Files As Existing Db

Jun 3, 2004

In SQL Server 2000, I would like to attach a Database that is the file of the Database on another system. I made a copy of my Client's Database at his site. Now I would like to attach it to my server and then copy the data from my Client's DB to mine.

The only problem is they have the same file names, so when I try to attach the copy I get an error message. Is there any way to change the name of the data file and log file that a database points to so that I can attach it to my Server?

Thanks in advance for your help!

View 1 Replies View Related

Move The Source Excel Files To Archieve Folder

Apr 15, 2008

Using the below script task I am checking for the excel file existence and upon file existence using the data flow task will load the excel data into sql table. After the data is loaded from one file or however number of excel files present, I want to move those into a archieve folder with datetimestamp to the filenames,please let me know how I can move those files with datetimestamp to the filenames, any help is greatly appreciated. Thanks!!

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists(ReadVariable("FileNameVariable").ToString()) Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

'From Daniel Read's Blog - http://www.developerdotstar.com/community/node/512/
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class

View 8 Replies View Related

How To Create Dynamic Folder Which Contains All The Files Which Are Used By All The Packages In A Project

Mar 19, 2008

Hi,
My Problem goes Like this....

I have a folder which contains all the flat files which are used by all the packages(ex--flat file connection managers) in my project.
If we want to change the name of the folder,have to change in every package( in all connection managers) manually.It looks hardcoding and timetaking.

Is there any way to change in one place(xml,file,variable) so that it should be affected in all the packages.

one more doubt is..

If we configure the flat file connection manager in package configurations,configuration file (ex-xml)will be created (we can make changes in that file regarding that connecion mgr only.)

But i want one configuration file (ex--xml) so that i should configure the details of all the connection managers used in all packages.

View 5 Replies View Related

Move The Source Excel Files To Archieve Folder

Apr 15, 2008

Using the below script task I am checking for the excel file existence and upon file existence
using the data flow task will load the excel data into sql table. After the data is loaded from one file or
however number of excel files present, I want to move those excel files into a archieve folder with date&timestamp to the filenames, please let me know how I can move those files with datetimestamp to the filename,
any help is greatly appreciated. Thanks!!
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists(ReadVariable("FileNameVariable").ToString()) Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
'From Daniel Read's Blog - http://www.developerdotstar.com/community/node/512/
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class

View 6 Replies View Related

SQL Tools :: Compare Two Text Files?

Mar 13, 2006

What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily.

View 11 Replies View Related

SQL Server 2012 :: How To Fetch PDF Files From A Folder And Saving It In The Database

Jul 23, 2013

I have a small project to be done in which I need to fetch the pdf file from a my system and save it in database and also fetch the name of it and save it in the database.

View 9 Replies View Related

Integration Services :: How To Traverse Files Under Subfolder Using Folder Option

Apr 29, 2015

My files are stored as Newborn.txt under each year's subfolder, How to implement this.

Eg: C:RaviN90Newborn.txt
  C:RaviN91Newborn.txt
C:RaviN92Newborn.txt
C:RaviN93Newborn.txt

How to read the files using the for each loop container"

View 5 Replies View Related

SQL Server 2008 :: Checking Files In A Shared Path If Folder Or File?

Jun 3, 2015

I wrote the below script to print all folders and files located in the share path. How to extend my script to mention by adding another column whether the file is a folder/file , sort of 0 or 1.

declare @chkdirectory1 varchar(4000) = 'shared_pathfolder';
declare @finalserver3 varchar(4000);
create table #tmp (directory_name varchar(4000))
SET @finalserver3 = '''"DIR ' + @chkdirectory1 + ' /B"''';
--select @finalserver3
--SELECT @finalServer
DECLARE @ExecCmd varchar(100)
--SELECT @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + char(50) + 'mkdir D:'+ CONVERT(varchar(8), getdate(), 112) + '' + char(50)
SET @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + @finalserver3
--SELECT @ExecCmd
exec(@ExecCmd)
drop table #tmp

View 0 Replies View Related

SQL Server 2008 :: Use Folder Name As Database From Batch File To Execute Files

Jul 17, 2015

I've been struggling with this issue,

1) Test--FolderName (This Test folder name should use as a database name for below sub folders)

a)Create--Sub Foldername
i)create.sql
b)Alter---Sub FolderName
i)Alter.sql
c)Insert---Sub FolderName
i)Insert.sql

[Code] .....

The scripts need to be run in order. So script one needs to run first folder in that sub folders after that next second folders etc..

Is there a way to create a bat file that automatically runs all these scripts, in order against, the databases they need to?

The databases that they need to run against have the name of the database at the beginning of the name of the folder.

View 0 Replies View Related

Compare Fields-two Flat Files-load Data

Jul 13, 2007

Hi All,



I am totally new to SSIS and im in the learing phase. I have a requirement as below,

I have two flat files (mainframe files), the structure i have given below,

File1:



070113

12345johnk

23456james



1st row is header record which has got date in YYMMDD format and remaining rows have emp no and emp name



File2:

070113

070113

070113

070113



contains 4 records which are dates.



The requirement is to compare the header date in file1 with the 4 dates in file2, if they are equal then it should load all the records in file1 except the header into a table and if they donot match then it should log an err msg. Please could someone provide a lead on this.



The files have same record length and fixed field delimited.



Thanks in advance

raj

View 1 Replies View Related







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