FTP Task Not Working As Expected - File Not Downloading
Feb 11, 2008
I have a package with an FTP task in the Control Flow. Nothing complicated, its configured to download a file from FTP with overwrite true, and the get and download paths in variables. Once this step completes it goes to a sequence container that does stuff with the file, that part works fine.
The problem is if i run the package in debug mode using Visual Studio, everything works perfectly (even if run over and over). The problem occurs if i use my "driver" application to try and execute the package. All my driver does is use C# code to create an Application object, set the PackagePassword, LoadPackage based on the path and then .Execute().
Here is when the strangeness begins:. If the file exists in the destination path, then the FTP task fires. If the file does not exist (lets say i manually delete it after its being downloaded before using the IDE) the FTP task dose not fire and my Sequence Container fails to use the fail, since it is of course missing.
Monitoring the FTP server that i am running locally i can see commands firing when the file exists, but when the file is killed it never even tries to connect to my server. Very odd.
Am i missing something? Why would this work from the IDE but never from my driver?
Any help would be appreciated. Please let me know if any additional detail is required.
Dmitry
http://blog.lyalin.com
View 6 Replies
ADVERTISEMENT
Sep 14, 2007
Hi Guys,
I have two tables called table1 and table2.
table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.
table1
----------------------
-searchword- column name
--------------------------------------------
Learn more about melons row0
--------------------------------------------
%.txt row1
-------------------------------------------
table2
------------------------------
-testname- column name
--------------------------------------------
FKOV43C6.EXE
-------------------------------------------
frusdr.txt
-------------------------------------------
FRUSDR.TXT
------------------------------------------
SPGP_FWPkg_66G.zip
------------------------------------------
readme.txt
-----------------------------------------
README.TXT
----------------------------------------
watermelon.exe
----------------------------------------
Learn more about melons read me.txt
-------------------------------------------------------
Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.
===============================================================================
select * from @table2 t2 where t2.[testname] in (
SELECT tb.[testname] FROM @table1 ta
JOIN @table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'
group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @table1)
)
===============================================================================
script to create tables
============================================================================
DECLARE @table1 TABLE (
searchword VARCHAR(255)
)
INSERT INTO @table1 (
searchword
) VALUES ( 'Learn more about melons' )
INSERT INTO @table1 (
searchword
) VALUES ( '%.txt' )
DECLARE @table2 TABLE (
testname VARCHAR(255)
)
INSERT INTO @table2 (
testname
) VALUES ( 'FKOV43C6.EXE' )
INSERT INTO @table2 (
testname
) VALUES ('frusdr.txt' )
INSERT INTO @table2 (
testname
) VALUES ('FRUSDR.TXT' )
INSERT INTO @table2 ( testname
) VALUES ( 'SPGP_FWPkg_66G.zip' )
INSERT INTO @table2 (
testname
) VALUES ( 'readme.txt' )
INSERT INTO @table2 (testname
) VALUES ('README.TXT' )
INSERT INTO @table2 (testname) VALUES (
'watermelon.exe' )
INSERT INTO @table2 (
testname
) VALUES ('Learn more about melons read me.txt' )
SELECT * FROM @table2
DECLARE @table3 TABLE (
testname VARCHAR(255)
)
INSERT INTO @table2 (
testname
) VALUES ('Melon release NOTES 321.xls' )
===================================================================================
View 4 Replies
View Related
Oct 18, 2007
Do you see anything wrong with this? The first select works and finds rows the second one does not. I have opened the Key since the first query does find rows.
select *
from [dbo].[dmTable]
WHERE cast(decryptByKey(field) as varchar(50)) = 'Value'
select *
from [dbo].[dmTable]
where field = EncryptByKey(Key_GUID('CLTCadminKey'),'Value')
View 1 Replies
View Related
Oct 9, 2006
Hi,
In SQL 2005 SP1 - In my transactional replication RMO C# script, I want my snapshot job schedule to run daily at 2:58 AM.
Instead it runs hourly in the 58th minute. Sample code below shows I use the value 025800. That should be interpretted as AM. The frequencytype is daily. The frequency interval is 1. There is no subday frequency. Yet the job runs hourly and disregards the specified hour.
Is there something missing in this code? Is this a SQL Server bug?
// Set the required properties for the trans publication snapshot job.
TransPublication tpublication = new TransPublication();
tpublication.ConnectionContext = conn;
tpublication.Name = publicationName;
tpublication.DatabaseName = publicationDbName;
tpublication.SnapshotSchedule.FrequencyType = ScheduleFrequencyType.Daily;
tpublication.SnapshotSchedule.FrequencyInterval = Convert.ToInt32(0x0001);
tpublication.SnapshotSchedule.ActiveStartDate = 20051101;
string newString = "025800";
tpublication.SnapshotSchedule.ActiveStartTime = Convert.ToInt32(newString);
tpublication.Create();
View 3 Replies
View Related
Feb 25, 2006
I was hoping that the Trim function inside the update command, cmdUpdate.Parameters.Add("@doc_num", Trim(txtDocNum.Text))
, would deal with any leading and trailing spaces, but it does not seem
to be doing anything at all. The value from the textbox still arrives
in the database table with leading spaces!!
Am I doing something wrong?
View 7 Replies
View Related
May 2, 2007
I have a package that has a container containing multiple DF Tasks.
The container is set to be Transacted, such that should any of the DF tasks fail the data inserted in any of the previous tasks rolls back.
This works as expected.
However, this container is part of a larger package and so I wanted to have a checkpoint on it, so that should any of the tasks within it fail, the package could be restarted from this container.
However, I would expect the functionality to be that on failure, the checkpoint would cause the whole container to be started again (because the container is transacted all DF task info would be rolled back) so we would expect it to start at task 1 again.
This is not the functionality I see. The package restarts from the failed task within the container every time.
According to the book Prof SSIS, it should start again from the first task and as explained this makes sense on a Transacted container as you would want this to happen.
A previous forum message encountered the same issue it appears:
See SSIS Checkpoints 04 Dec 2006.
This is an extract from it:
"I only experimented a little but my experience was that when I have a transacted container with multiple tasks that are checkpointed, SSIS would try to restart from the task that failed rather than from the first task in the container. The transaction was being rolled back correctly though.
In short, I felt that check points were not aware of transactions.
So, I ended up with this setting and it works for me:
Container is checkpointed and trasacted.
Tasks within the container are not checkpointed.
'FailParentOnFailure' property set to True on the tasks.
That way, if a task failed, it would fail the container and a checkpoint would be created at that level. Transaction would be rolled back as usual."
While this makes sense to me it is not the same properties that the SSIS book has that work.
Additionally, this didn't work for me either !!
I have tried every combination of FailPackageOnProperty and FailParentOnProperty that makes sense but every time the package restarts from the failed container within the task.
The transaction is rolled back correctly every time, but it seems the checkpoint that is created is not used correctly when dealing with transactions within containers.
View 1 Replies
View Related
Nov 20, 2007
Hi all,
Heres the setup...
We have an asp application that runs the reportserver URL for the selected report, passing it through parameters. This opens the report viewer and the report runs.
The problem im having is that one report is not working as expected. When the report is run from report manager, everything works fine. The links do what they're meant to (they link to other reports passing through parameters). When the report is run from our asp application with the report viewer, the links fail.... they dont pass through the correct values or sometimes dont pass through a value at all.
Were the report viewer and report manager applications developed seperately?
Any help would be appreciated.
Thanks.
View 5 Replies
View Related
Dec 18, 2007
Has anyone had experience of using Parent/Child packages while enlisting them in Transactions.
I tested this on a small sample and thought that I had got it to work, but in my real-world package it does not.
The parent package essentially calls three child packages.
In each child package there are multiple DFT's that import and transform data into SQL Server.
All data must be imported or not at all.
Therefore I created a FELC container into which three Exec child package tasks were placed.
The FELC is set to Trans Option 'Required' and the Exec child package tasks to supported.
Unfortunately upon failure of one of the DFT's in the child the data was not rolled back.
So initially we had in terms of container hierarchy for the Trans Option property:
Parent package Supported
FELC for calling child packages Required
Task execute child package Supported
Child package Suppored
Tasks Suppored
Looking at this more closely we thought that we would need
Parent package Supported
FELC for calling child packages Required
Task execute child package Required
Child package Required
Tasks Suppored
for it to work.
However, the latter now gives us failures with error messages on the tasks on the child packages.
[Execute SQL Task] Error: Failed to acquire connection "Conn ECARS1CEDImport". Connection may not be configured correctly or you may not have the right permissions on this connection.
Even more strange the first couple of tasks in the child pkg complete successfully even though they use the same connection listed in the error.
These tasks also have Event handlers.
View 7 Replies
View Related
Mar 30, 2007
Hi,
I have a ForEach Loop that has 3 script tasks in it.
I have them set up so that they execute in order, such as:
script1 ---> script2 ---> script3
script1 creates a file
script2 creates a file
script3 compares the files using a diff command
Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.
The error is "The system cannot find the file specified".
Thanks
View 2 Replies
View Related
Mar 5, 2008
what data type am i going to put to my uploadedFiles column in my database... uploaded files are in document format or .txt
also.. how can i make those files converted into pdf files.. also enable users to download it..
tnx!!!
forums.asp.net = "great help"
View 7 Replies
View Related
Nov 6, 2015
I had a small requirement i had a table with embedded attachments and attachments are uploaded to the table and saved in varbinary field from front end. Want i want is i want to download particular embedded files in that table within SQL Server itself. I have tried googling but none of seems to be within SQl Server T-SQL. all are linked to Asp.NET how i can do?
View 6 Replies
View Related
May 28, 2007
Hi,
I'm trying to work on a database when I'm not connected. I can't figure out how to get a local copy of a database.mdf that I created on the server, onto my hard drive, using SQL Management Studio Express. Does anyone have any suggestions? I'd be forever in your debt. Thanks
View 1 Replies
View Related
Jan 28, 2008
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
View 2 Replies
View Related
Mar 11, 2008
Dear list
Im designing a package that uses Microsofts preplog.exe to prepare web log files to be imported into SQL Server
What Im trying to do is convert this cmd that works into an execute process task
D:SSIS ProcessPrepweblogProcessLoad>preplog ex.log > out.log
the above dos cmd works 100%
However when I use the Execute Process Task I get this error
[Execute Process Task] Error: In Executing "D:SSIS ProcessPrepweblogProcessLoadpreplog.exe" "" at "D:SSIS ProcessPrepweblogProcessLoad", The process exit code was "-1" while the expected was "0".
There are two package varaibles
User::gsPreplogInput = ex.log
User::gsPreplogOutput = out.log
Here are the task properties
RequireFullFileName = True
Executable = D:SSIS ProcessPrepweblogProcessLoadpreplog.exe
Arguments =
WorkingDirectory = D:SSIS ProcessPrepweblogProcessLoad
StandardInputVariable = User::gsPreplogInput
StandardOutputVariable = User::gsPreplogOutput
StandardErrorVariable =
FailTaskIfReturnCodeIsNotSuccessValue = True
SuccessValue = 0
TimeOut = 0
thanks in advance
Dave
View 1 Replies
View Related
Jan 30, 2007
How do I use the execute process task? I am trying to unzip the file using the freeware PZUnzip.exe and I tried to place the entire command in a batch file and specified the working directory as the location of the batch file, but the task fails with the error:
SSIS package "IngramWeeklyPOS.dtsx" starting.
Error: 0xC0029151 at Unzip download file, Execute Process Task: In Executing "C:ETLPOSDataIngramWeeklyUnzip.bat" "" at "C:ETLPOSDataIngramWeekly", The process exit code was "1" while the expected was "0".
Task failed: Unzip download file
SSIS package "IngramWeeklyPOS.dtsx" finished: Success.
Then I tried to specify the exe directly in the Executable property and the agruments as the location of the zip file and the directory to unzip the files in, but this time it fails with the following message:
SSIS package "IngramWeeklyPOS.dtsx" starting.
Error: 0xC002F304 at Unzip download file, Execute Process Task: An error occurred with the following error message: "%1 is not a valid Win32 application".
Task failed: Unzip download file
SSIS package "IngramWeeklyPOS.dtsx" finished: Success.
The command in the batch file when run from the command line works perfectly and unzips the file, so there is absolutely no problem with the command, I believe it is just the set up of the variables on the execute process task editor under Process. Any input on resolving this will be much appreciated.
Thanks,
Monisha
View 1 Replies
View Related
Mar 20, 2008
I am designing a utility which will keep two similar databases in sync. In other words, copying the new data from db1 to db2 and updating the old data from db1 to db2.
For this I am making use of the 'Tablediff' utility which when provided with server name, database, table info will generate .sql file which can be used to keep the target table in sync with the source table.
I am using the Execute Process Task and the process parameters I am providing are:
WorkingDirectory : C:Program Files (x86)Microsoft SQL Server90COM
Executable : C:SQL_bat_FilesSQL5TC_CTIcustomer.bat
The customer.bat file will have the following code:
tablediff -sourceserver "LV-SQL5" -sourcedatabase "TC_CTI" -sourcetable "CUSTOMER_1" -destinationserver "LV-SQL2" -destinationdatabase "TC_CTI" -destinationtable "CUSTOMER" -f "c:SQL_bat_Filessql5TC_CTIsql_filescustomer1"
the .sql file will be generated at: C:SQL_bat_Filessql5TC_CTIsql_filescustomer1.
The Problem:
The Execute Process Task is working fine, ie., the tables are being compared correctly and the .SQL file is being generated as desired. But the task as such is reporting faliure with the following error :
[Execute Process Task] Error: In Executing "C:SQL_bat_FilesSQL5TC_CTIpackage_occurrence.bat" "" at "C:Program Files (x86)Microsoft SQL Server90COM", The process exit code was "2" while the expected was "0". ]
Some of you may suggest to just set the ForceExecutionResult = Success (infact this is what I am doing now just to get the program working), but, this is not what I desire.
Can anyone help ?
View 9 Replies
View Related
Apr 3, 2008
Hi,
I set up a sql mail task. Getting this error at runtime:
[Send Mail Task] Error: An error occurred with the following error message: "Mailbox unavailable. The server response was: 5.7.1 Unable to relay for me@my.com".
Has anyone seen this? For the SMTP Server in the smtp connection, I am simply using the mail server name as was set up in db mail.
Help!
View 7 Replies
View Related
Mar 28, 2007
In the Control flow tab, I have an Execute SQL Task that outputs full Result set into a variable of an object type. Now how can I write the contents of the Full Result Set into a text file using Script Task. I also want to format the following way while I output into a file:
Column Name 1 : Column Value
Column Name 2: Column Value and so on
I tried writing the contents of the Object Variable into a file, but the file had an output of single word: System.__ComObject.
Code for Writing the Full Result Set into a Text File
Dim RSsqloutput as String = Dts.Variables("objVariable").Value.ToString
Dim strVal as String = "File completed on " & Now() & vbCrLf & "------------------------------------------------------" & vbCrLf
oLogFile.WriteAllText("C:MyFile.txt", strValue)
oLogFile.WriteAllText("C:MyFile.txt", rsSQLOutput)
I went through this link that explains how to write XML Result Set into a File, But this doesn't help as it writes in XML format.
Would you please give me a hint of code how I can go upon.
View 7 Replies
View Related
Dec 6, 2007
I am assigning package string variable to the StandardOuputVariable of a Process Task, and setting the TimeOut to 3, TerminateProcessAfterTimeOut to True, plus the Executable name. The program is executing about 40 seconds, and it seems not to terminate after the 3 seconds has passed.
When I execute the same package but without the StandardOuputVariable the task terminates after the 3 seconds.
Is this a bug or I shouldn't expect the task to terminate when the StandardOuputVariable is set???
View 3 Replies
View Related
Dec 8, 2006
Hi
I'm using an FTP task in a loop to download files from a WAN based FTP server. I've got a script to generate the name of the file I require and place into a variable. Problem is sometimes some of the remote files haven't been published so the FTP component errors and the package fails.
I tried setting StopOnFailure to False - all I get is the following errors[FTP Task] Error: File represented by "User::RemotePath" does not exist.
[FTP Task] Error: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.
What I want to do is ignore the error and perform the next loop iteration
Is this a bug, or am I missing something
Dave
View 7 Replies
View Related
Dec 6, 2007
HI, I need to trigger some packages upon existance of specific files in a particular directory. Sound lkike the file watcher task (from SQLIS) would do the job but I am wondering what is the difference of using this tool instead of a for each loop container. I mean, If a file exists in a directory, the for each loop container will detect it. Since the file watcher is not a service, the package containing it needs to ne scheduled on a regular basis for the filewatcher to detect the file, right? So, a for each loop container would do the job? So, waht wouldbe the advantage of using the file watcher task?
Thank you,
Ccote
View 11 Replies
View Related
Nov 7, 2007
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
http://www.pragmaticworks.com/filepropertiestask.htm
View 5 Replies
View Related
Oct 5, 2007
Hi,
I have a FOR EACH LOOP that loops through a bunch of folders in search of particular filename (including path). That part works fine.
Then, INSIDE the FOR EACH LOOP, I have a script task that evaluates the filename and grabs the date portion of the filename. That part works fine too.
Now, here's my dilemma. If the date portion of the file name IS GREATER THAN the the value of another date in a variable, I want it to load the file into a table using a data flow task.
So, this is what my package looks like:
[ FOR EACH LOOP (Script task --- evaluate the date using an expression (see below) ---> Data flow task) ]
My expression looks like this:
@[User::fileDate] > @[User::maxLoadDate]
You think that this would work, right??
However, after the VERY FIRST iteration of the loop, the script task turns green, then it goes right to the data flow task, which then says that the file name is "", and can't be loaded. (I'm not sure why this is occuring)
My question is, is my logic correct? Can I use an expression inside the for each loop and have it work?
View 1 Replies
View Related
Aug 5, 2006
I have a script that changes the name of a file after a data upload. The script works fine if I execute the package in Visual Studio but when I run the file package from a SQL server job it does not rename the file. The data does get uploaded it just does not run the final script.
Any help would be appreciated.
Steve
View 1 Replies
View Related
Nov 6, 2007
Hi ,
Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine
Now the procedure is changed to Oracle.So I have changed it to "BEGIN PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.
Executing 'execute BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.
Please help me.. thanks in advance
Regards,
GK
View 5 Replies
View Related
Sep 17, 2007
I'm unable to copy my Stored Procedures from one database to another. I'm using mixed mode authentication. I have set CopyAllStoredProcedures to True, DropObjectsFirst to True and CopySchema to True.
Nothing gets copied. I have followed many web sites that say Transfer SQL Server Objects Task is broken. Is this true and I should give up?
Also, I'm on SQL 2005 SP2 which appears to be the latest and I assume is the update for SSIS? yes ?
Thanks for any help
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "CREATE PROCEDURE [dbo].[del_Admin_RemoveContractorFromContract] @ContractID int, @ContractorID int AS DELETE FROM CONTRACTOR_CONTRACTS WHERE CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID DELETE FROM CONTRACTOR_USER_CONTRACTS WHERE CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID " failed with the following error: "There is already an object named 'del_Admin_RemoveContractorFromContract' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
View 4 Replies
View Related
May 19, 2006
I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave
View 6 Replies
View Related
May 14, 2015
I'm copying files to a folder with the naming convention as follows in the source folder:
CM_ABC_MY_TEST.txt
In the destination folder, this filename needs to appear as:
CM_XYZ_MY_TEST.txt
In my File System Task, I'm pretty sure I'm going to need an expression with a replace, substring, etc. But am having a hard time nailing down the exact syntax.
View 10 Replies
View Related
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
Aug 18, 2006
Does anyone know how to do this using variables? Everytime I try it, I get the
Error: Failed to lock variable for read access with error 0xc00100001.
I also tried it writing a script and still the same error. If I hard code the values into the variables it works fine but I will be running this everday so that it will pull in the current date along with the filename. So the value of the variables will change everyday. Here is my expression:
@[User::Variable] +(DT_WSTR,4) YEAR( GETDATE() )+"0"+(DT_WSTR,2) MONTH( GETDATE() ) + (DT_WSTR,2) DAY( GETDATE() )
The result:
C:Documents and SettingsmroushDesktopOSU20060818
the 20060818 part will change everyday ie.(tomorrow will be 20060819, next day 20060820 and so on.)
View 6 Replies
View Related
Oct 9, 2006
I am having an issue with the File System Task.
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have:
FileDestinationPath variable - set to C:TestFiles
FileSourcePath variable - set to C:TestFiles
FileNameAndLocation variable - set to blank
For Each Loop Container €“ Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) €“ Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(€œUser::FileDestinationPath€?).Value = dts.Variables(€œUser::FileDestinationPath€?).Value & €œ€? Month & €œ_€? & Day & €œ_€? & Year & €œ€?) which gives me €œC:TestFiles 9_14_2006€?.
File System Task (within For Each Loop, second step) €“ This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that day€™s date. Basically, how do I get this to work since I can€™t hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the €œVariable €œFileNameAndLocation€? is used as a source or destination and is empty.€?
Let me know if I need to clarify further€¦...I may be missing something very simple. Any help would be greatly appreciated!
View 10 Replies
View Related
Jul 7, 2006
Hey there
Ive built an SSIS package which generates a file from a legacy system and then downloads the file into a designated folder on the server. I need the file watcher task to wait for a the file to completely finish loading before it says it is complete. Currently, as soon as the file is created, the WMI step finishes.
Any help would be greatly appreciated!
Kind Regards
David
View 4 Replies
View Related
Jun 8, 2007
I am able to run SSIS packages as SQL Server Agent jobs with a Control Flow items "File system task", if I move a file (test.txt) from a drive (c on the server (where SQL Agent jobs run) to a subdirectory on the same drive. But, if I try to move a file on a network drive, the package fail.
What I can do to solve this issue.
Bye!
Daniel
View 1 Replies
View Related
