I would need to make a SSIS package which must to watch all the day long if a file come and then save into a variable that path+file.
FileWatcher task do that for me or better totally focusing this requirement in a conventional .NET Script using IO classes and all that sort of stuff?
OK, what about this here: http://www.sqlis.com/default.aspx?23HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||anyone got any sample on how to use the File Watcher Task component?
Hi,
I am getting below error while importing data in SQL 2005 Express:
"error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.HistoryLog'.'PK_HistoryLog' in database 'HistoryData' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".
"
I have select :
Enable Autogrownth = Yes
Filegrowth = 1 MB
Maximum File Size = Unrestricted File growth
I don't know what else I am missing?
Please help
thanks
AA
How much empty space was on the disk when you received the error message.
Check the Log files.
|||there is only one Log file by the name of "HistoryData_log"
Initial size is: 1,747
Enable Autogrownth = Yes
Filegrowth = 10%
Maximum File Size = Unrestricted File growth
Empty Space on C: is 149 GB
Total database Size : 5843.00 MB
Space Available : 128.21 MB
Please note:
I just noticed when I was increasing initial database size:
"Create Database Or Alter DataBase failied becasue the resulting cumulative database size would exceed your licence limit of 4096 MB Per Database"
Please advsie is this the licencing issue?
Thanks
|||I meant to look in the computer application logs. However, you 'stumbled' upon an explanition.
Yep, You have SQL Server Express, and SQL Server Express is limited to a maximum 4 GB database size.
|||Is there any way to increase it?
Please advise which version is good for unlimited data base space?
Thanks for your help
Amir
|||Any Edition you purchase, from Workgroup to Enterprise, has 'unlimited' database size.
Check here for the best Edition for your needs.
SQL Server 2005 Features, Version Comparison
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
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
Check out http://www.sqlis.com/default.aspx?23
Thanks,
Loonysan
Hi
Thanks for that. We decided to take a different approach though.
Ive downloaded another file watcher task from Konesans. Konesans.Dts.FilewatcherTask.FileWatcherTask. I have added it into my SSIS package and it is working like a dream!
However, when to Add it to the SQL Server Agent, I get a strange error message (Package Execution Progress):
- -> Wait for File to Download
Validation has started
Error: The task has failed to load. The contact information for this task is "".
Error: There were errors during the task validation
Validation is completed
Any ideas of how to resolve this?
Thanking you in advance
David
|||
Konesans file watcher is the same one as http://www.sqlis.com/default.aspx?23
Please check if this component is installed in the machine where you are trying to execute the package via SQL Agent. It looks like the SSIS Package is not able to load the FileWatcher Task at runtime.
If the problem persists - contact http://www.konesans.com/contact.aspx
Thanks,
Loonysan
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
Check out http://www.sqlis.com/default.aspx?23
Thanks,
Loonysan
Hi
Thanks for that. We decided to take a different approach though.
Ive downloaded another file watcher task from Konesans. Konesans.Dts.FilewatcherTask.FileWatcherTask. I have added it into my SSIS package and it is working like a dream!
However, when to Add it to the SQL Server Agent, I get a strange error message (Package Execution Progress):
- -> Wait for File to Download
Validation has started
Error: The task has failed to load. The contact information for this task is "".
Error: There were errors during the task validation
Validation is completed
Any ideas of how to resolve this?
Thanking you in advance
David
|||
Konesans file watcher is the same one as http://www.sqlis.com/default.aspx?23
Please check if this component is installed in the machine where you are trying to execute the package via SQL Agent. It looks like the SSIS Package is not able to load the FileWatcher Task at runtime.
If the problem persists - contact http://www.konesans.com/contact.aspx
Thanks,
Loonysan
Hello,
I am currently just starting to learn SSIS and I have a question on creating a directory using the File System Task.
Would I be able to create a directory where the directory name is generated and stored in a SSIS variable?
Any help is appreciated and thank you in advance for the assistance.
Eric
In tinkering around with SSIS Designer I found a way to create the directory in question.
|||For info and anyone else wondering, open the task and follow steps below
1 Change Operation to Create directory.
2 Change IsSourcePathVariable to True.
3 Change SourceVariable to the variable you wish to use, or select <New variable> to create one now.
To generate the folder name you can use an expression on the variable. Close the File System Task editor, and setup the expression.
1 Open the SSIS Variables window.
2 Select the variable you wish to work with.
3 Select or Open the Visual Studio Properties window
4 In the Properties window, change the EvaluateAsExpression property of the variable to True.
5 Set the Expression property. This is generates the directory name for you. (SP1 and above will show an ellipsis button against this property which will invoke the expression editor dialog.) For example a folder name of C:\MyFolderyyyymmdd would use the following expression-
"C:\\MyFolder" + (DT_WSTR,4)YEAR(GETDATE())
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2)
6 Check the Value property of the variable to see the evaluated expression result which will be used by the task.
Wanting to use File System Task in SSIS to move files from
one location to another for archiving. I can't seem to figure out how to use a
wild card for the file name. It seems that I must specify the actual file name
which is a problem because only the first 4 letters in the file name remain a
constant.
Does anyone know how to use a wild card or a way to work
this in?
Darrin Turner wrote:
Wanting to use File System Task in SSIS to move files from one location to another for archiving. I can't seem to figure out how to use a wild card for the file name. It seems that I must specify the actual file name which is a problem because only the first 4 letters in the file name remain a constant.
Does anyone know how to use a wild card or a way to work this in?
Yes. use a ForEach loop to loop over the files (which you can specify with a wildcard) and then use the FileSystem task on each iterated file.
-Jamie
|||Ok, thank you. I will give that a try.
Hi All,
I've been doing some further testing on SSIS and come across what I consider strange behaviour of the FileSystemTask.
This is set to delete a file, using the file name input from a variable.
If the file doesn't exists I would expect the task to fail, but instead it does not. It succeeds ! I would expect this to fail if the file it attempts to delete is not found.
Can anyone duplicate this strange behaviour ?
Regards,
P R W.
I concur. It does not fail if the file does not exist, and I think it should, or rather it shoudl have the option to do so. I can see why it would also be very desirable to not fail, so for me an option to determine failure on file not found.
If you need it to fail, then a quick workaround for your package would be to add a second File task, and choose the Set attributes mode. That will fail if the file is not found.
|||
Hi All,
I've been doing some further testing on SSIS and come across what I consider strange behaviour of the FileSystemTask.
This is set to delete a file, using the file name input from a variable.
If the file doesn't exists I would expect the task to fail, but instead it does not. It succeeds ! I would expect this to fail if the file it attempts to delete is not found.
Can anyone duplicate this strange behaviour ?
Regards,
P R W.
Perhaps submit it as a bug at connect.microsoft.com?
-Jamie
|||
Hi Jamie,
I did a bit of testing with the FST.
If a file does not exist when copying, moving or renaming a file. The FST fails as expected with 'Could not find file....'
When deleting a file with FST though, if the file does not exist, the task succeeds.
This doesn't appear consistent to me and I have submitted as a bug at your suggestion.
Regards,
P R W.
|||https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=250600
Voted and Validated.
|||Can I add to the strange behaviour of FST? I have the exact same task I added to a Foreach Loop and it just won't execute. I have it on Success for one task and on Success and Expresssion on another. The task is enabled. There are also no errors. The Foreach loop continues without the task running? I don't get it.
Cory
Hi All,
I've been doing some further testing on SSIS and come across what I consider strange behaviour of the FileSystemTask.
This is set to delete a file, using the file name input from a variable.
If the file doesn't exists I would expect the task to fail, but instead it does not. It succeeds ! I would expect this to fail if the file it attempts to delete is not found.
Can anyone duplicate this strange behaviour ?
Regards,
P R W.
I concur. It does not fail if the file does not exist, and I think it should, or rather it shoudl have the option to do so. I can see why it would also be very desirable to not fail, so for me an option to determine failure on file not found.
If you need it to fail, then a quick workaround for your package would be to add a second File task, and choose the Set attributes mode. That will fail if the file is not found.
|||P R W wrote:
Hi All,
I've been doing some further testing on SSIS and come across what I consider strange behaviour of the FileSystemTask.
This is set to delete a file, using the file name input from a variable.
If the file doesn't exists I would expect the task to fail, but instead it does not. It succeeds ! I would expect this to fail if the file it attempts to delete is not found.
Can anyone duplicate this strange behaviour ?
Regards,
P R W.
Perhaps submit it as a bug at connect.microsoft.com?
-Jamie
|||Hi Jamie,
I did a bit of testing with the FST.
If a file does not exist when copying, moving or renaming a file. The FST fails as expected with 'Could not find file....'
When deleting a file with FST though, if the file does not exist, the task succeeds.
This doesn't appear consistent to me and I have submitted as a bug at your suggestion.
Regards,
P R W.
|||https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=250600
Voted and Validated.
|||Can I add to the strange behaviour of FST? I have the exact same task I added to a Foreach Loop and it just won't execute. I have it on Success for one task and on Success and Expresssion on another. The task is enabled. There are also no errors. The Foreach loop continues without the task running? I don't get it.
Cory
Hi,
I want to move all files of one directory/folder into another usng SSIS, like in DOS Prompt we use the command,
Move d:\ftpSource\*.* d:\ETLSource
I have tried by creating a file system task, but there is no option for Move Directory Contents. Move File is not accepting wild cards.
Regards,
Imran.
Hi,
U can use 'move directory'. But both source and destination should have same root and if the destination folder already exists. Also it will work as a rename folder. So either the destination folder should not exist or u should set the 'overwrite destination' property to true which will delete the destination folder before renaming the source. U better do it thru 'File' object in Script task.
|||Hi,
I want to move all files of one directory/folder into another usng SSIS, like in DOS Prompt we use the command,
Move d:\ftpSource\*.* d:\ETLSource
I have tried by creating a file system task, but there is no option
for Move Directory Contents. Move File is not accepting wild cards.
Regards,
Imran.
I struggled with this myself, and in the end I had to do 2 file system operations:
(1) copy directory - from source to destination
(2) delete directory contents - from source
HTH|||
Hi,
Sorry I cant move directory, because it is published over ftp. The way I already have done is (foreach loop over directory...Move file), but it is slow. Thanks for the hint, Now I will try file Object in Script task.
Thanks
Imran.
I have a problem with my SSIS. I have a data flow with a file source in csv, but itself has 140 000 rows, so when I execute the date flow, I have a error who say that the data exceed the temp of I/O (sorry for the translate, but I have the message in french).
I test to pass the DefaultBufferMaxRow to 140000 but I have always the problem.
If we can help me, thank you.
You might try setting the BufferTempStoragePath property on the data flow to point to a drive with plenty of free space. If you search on the forum for that property, you'll find more information.|||Ok, I go to search it.
Thank you for your answer.
I have a problem with my SSIS. I have a data flow with a file source in csv, but itself has 140 000 rows, so when I execute the date flow, I have a error who say that the data exceed the temp of I/O (sorry for the translate, but I have the message in french).
I test to pass the DefaultBufferMaxRow to 140000 but I have always the problem.
If we can help me, thank you.
You might try setting the BufferTempStoragePath property on the data flow to point to a drive with plenty of free space. If you search on the forum for that property, you'll find more information.|||Ok, I go to search it.
Thank you for your answer.
