Using a DTS package I am working on the ability for a client to upload a file to the web server. From the SQL server the DTS package will FTP the file from the web server and then pump the text file data into a table that will be used to verify data in another database table.
I am having difficulty setting the SourceFileName property for the FTP task in a DTS package. I believe I am using the correct format to set the property. Below is the SQL querry used in setting a global variable inside of a SQL Execute Task (SourceFileName).
SELECT TOP 1 OrgID,
''''+VerifyFileName+''''+';''e:\webs\dev.10kaday.com\admin\VerificationFiles\'';''50''' as SourceFileName,
'e:\files\10kaday\VerificationFiles\'+VerifyFileName as DestinationFileName,
'e:\files\10kaday\VerificationFiles' as DestinationDirName
WHERE Complete <> 1
The global variable is then used in a Dynamic Properties task to set the SourceFileName property of the FTP Task. All Tasks are serially connected with On Completion Work Flow type.
The FTP task completes but there is no file. When viewing the property it appears to be set correctly after executing the package. The next Task fails which is a Transform Data Task that uses the Data Pump to pump data from the FTP'd text file to a db table.