Filter duplicate records

Hi

Have been given the task of trying to write reports in Crystal after someone found out I new one of Access from the other.

The report is taking data from SAGE database with custom written tables. The problem I have is that one of the custom table has not been well written allowing multiple identical entries.

The table in question holds data for each delivery note posted on SAGE. In this table are field I need relating to Product Number, Quantity and Nett Weight. Due to the way data has been stored I can have up to 16 records (effectively duplicates - same part number, quantity, nett weight) when I only want one record displayed.

Is it possible to write a filter that basically says that if for a given delivery number there is more than 1 record with the same Product Number, Quantity then filter the number of records down to one? If this is not possible, would it be possible to do the same but specifying to pick the record with the highest nett weight? assuming that if there were two identical records for all of the aforementioned fields that it could still bring this down to one?You can do it in your Query:

SELECT ProductNumber, Quantity, 'NettWeight' = MAX(NettWeight)
FROM TableName
GROUP BY ProductNumber, Quantity

(I use SQL Server 7, so you may need to adjust the syntax a bit to work in your database.)

- or -

You can do it in Crystal Reports. There should be a property called SuppressDuplicates (or something similar, I don't have CR in front of me). If you set it to True, Crystal should display only unique records. I think that property is available for fields as well as sections, but I'm not sure. Also, I use CR 8.5, so if you're using a different version, SuppressDuplicates may not exist at all or it may be called something different.

FileWatcher Task or System.IO?

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

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||anyone got any sample on how to use the File Watcher Task component?

Filegroup is Full - During Import Data

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

|||Thanks for hep Arnie.

File Watcher Task, where?

Where the earth is this task? I am not be able to see it anywhere!!

Thanks for any input,

It's already solved. I've donwloaded MSI file for that from www.sqlis.com

Thanks anyway

File Watcher Task doesnt wait for the entire file to be completely finish before it completes

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

File Watcher Task doesnt wait for the entire file to be completely finish before it complete

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

File Watcher task

In the file watcher task in the filter section does it look for exsiting file name which we have mentioned or is it possible to give first few letters of the file name like

pat*.txt

Please let me know

Did you just download the task and not read its documentation?

http://www.sqlis.com/23.aspx

You can use pat*.txt, if you wish.

File Task Moving with variables...

I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.

Source Path & Filename:

@.[User:: DataSourceFolder]+"\\"+ @.[User::CD_PaidClaimSource]

Outputs:

\\umrdwh2\FTP\CLAIMDAILY.DATA

Destination Path Now: (Filename not specified per another thread.)

@.[User::WorkingFolder]

Original Destination Path: (Which did not work.)

@.[User::WorkingFolder]+"\\"+ @.[User::CD_PaidClaimSource]

I receive validation errors:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

How do I overcome this without hard coding the path in the task?

You should be editing the file connection managers (source and destination) via variable expressions, not the file system task, I believe.|||

CaptainMyCaptain wrote:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

That error is showing you the value of your variable, not it's name. Not sure how that could have happened, but hopefully it helps you figure out what is wrong.
|||

JayH wrote:

CaptainMyCaptain wrote:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

That error is showing you the value of your variable, not it's name. Not sure how that could have happened, but hopefully it helps you figure out what is wrong.

Oh, I think I get it. Did you specify that expression as the Source Path in the File System Task? I didn't think the UI allowed that. If so, you need to create a new variable, set it EvaluateAsExpression=true and put the expression there. Then reference that variable from your File System Task's SourceVariable property.
|||An example to illustrate what we're saying:

http://www.ssistalk.com/file_system_task.jpg|||

The picture helped immensely, thank you. I was trying to establish the source and destination on the expressions screen as well as the general screen which was causing some of the error. Once I removed the destination and source from the expressions screen it worked better.

I am having another issue now, however. Following the syntax in the picture exactly I am getting an error because the filename is being considered a part of the folder path in addition to the filename. I have as an expression:

My expression (like your picture):

"\\\\Apsw9026g1\\Admin\\Logs\\CLAIMDAILY.DATA"

Which outputs:

\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA

But this is the error on execution:

Information: 0xC002F30E at CLAIMDAILY Move, File System Task: File or directory "\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA" was deleted.

Error: 0xC002F304 at CLAIMDAILY Move, File System Task: An error occurred with the following error message: "Could not find a part of the path '\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA'.".

Why is it duplicating the filename as part of the path? Did I miss a setting?

Roger

|||Try deleting the file system task and starting over.|||

Deleting everything variable, task, container, etc and recreating all of it results in the same error. Ideas? The funny part is the system loves the 'Source'; there are no errors, and it is the same syntax. It is only the 'destination' that is having trouble.

Rog

|||

CaptainMyCaptain wrote:

The picture helped immensely, thank you. I was trying to establish the source and destination on the expressions screen as well as the general screen which was causing some of the error. Once I removed the destination and source from the expressions screen it worked better.

I am having another issue now, however. Following the syntax in the picture exactly I am getting an error because the filename is being considered a part of the folder path in addition to the filename. I have as an expression:

My expression (like your picture):

"\\\\Apsw9026g1\\Admin\\Logs\\CLAIMDAILY.DATA"

Which outputs:

\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA

But this is the error on execution:

Information: 0xC002F30E at CLAIMDAILY Move, File System Task: File or directory "\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA" was deleted.

Error: 0xC002F304 at CLAIMDAILY Move, File System Task: An error occurred with the following error message: "Could not find a part of the path '\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA'.".

Why is it duplicating the filename as part of the path? Did I miss a setting?

Roger

Roger,

I have an example that uses the file system task to move/rename files in case you are interested: http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

If I recall correctly, when using variables in the file system task, IsDestiantionPathVariable and IsSourcePathVarible, and depending on the operation used; some times the task expect only the path that points to the file and some times it expects the path AND the file name. In your case it looks like it only expects the path; so remove the file name from the expression. Do not assume both variables need path and file name.

|||

That was the answer. Thank you all for helping this greenhorn to get his job done!

Roger

File System Task: Move/Rename

I can use the File System Task to Move a file from one directory to another and to rename a file, but is there a way to do this in one step, i.e., move file named 'c:\foo.txt' to 'c:\backup\foo_bkp.txt'?You should be able to accomplish this in an expr.|||

OK, maybe I'm being dense ... but how? I have both Source and Destination set to variables. The "Move File" facility only accepts a directory as the Destination because when I coded it with a new file name, I got an error due to the destination file name being ["DestVariable"]\["SourceFileName"] so it looked like:

c:\backup\foo_bkp.txt\foo.txt

|||Raul,
You can use a script task to do this. See here: http://blogs.conchango.com/jamiethomson/archive/2005/09/14/2149.aspx

-Jamie

File System Task Question

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.

File System Task Problem

I'm trying to realize a file system task that rename files from a
foreach loop container. So that means the task have a variable in the
source connection. This variable got the value (as an expression) of
"c:\\.....\\"+@.[User::ForeachloopVar].
But an error message appears when i run it.The message is

File System Task: An error occurred with the following error message:
"The given path's format is not supported."

When i don′t use the variable in the source connection it works fine.
Anyone knows what might be the problem?
Thanks.

Escape the slashes to be "\\"

BTW - you may find this blog post from Kirk Haselden useful ... http://sqljunkies.com/WebLog/knight_reign/archive/2005/02/12/7750.aspx

Donald

|||I′m sory but that′s not the problem.Because i′ve just wrote the example whitout the slashes but they′re there. And i′ve already looked at that blog. It was helpfull to create my package. But thats the thing in his case the value of the source variable from the foreach loop appears in the value of the expression, mine doesn′t.|||

I see that you are creating a path and appending the source variable in an expression. However, the ForEach loop includes the option to return the fully qualified name of the file. Is there a reason you're not using that?

Donald

|||I′m using the fully qualified name option that's not the problem.|||

It′s working now i did some changes like using only name and extension option on the foreach an voilá.

thanks.

|||

That link was quite a nice example, but I seem to be having a bit of a novice problem. I've followed the blog virtually to the letter, even changing to the variable names used, but on the File System Task I get an error saying that ' FileSourcePath is used as a source or destination and is empty '.

I created the ForEach loop container as was described:

On the collection I have set the path (browsed to the path, so it does exist). I've specified files as '*.txt' - there will be 2 files in the directory by the time the container executes. In the variable mappings I have specified the user::FileSourcePath variable - it has an index value of 0. The variables have a scope of the entire package. I feel I've missed something obvious here, but somehow my container does not update the values for that variable - any help?

|||

Look i was having that same problem before.I did just like the blog shows and it alway fails. I don't know why because it seems to work in the blog. But i don't know if my solution was the best one but i created another variable to contain the flat file source path and and in the foreach loop i've chose the option on the collection pane on the retrieve file name -Name and extension. That means i'm olnly getting the *.txt for the variable @.[user::FileSourcePath]. And then i've put the new variable whit the @.[user::FileSourcePath] in the Connection Manager connectionstring. So then i've just wrote the following on the evaluate as expression of the user::FileDestinationPath

@.[User::FileDestinationFolder] + "\\" +(DT_WSTR, 10)(DT_DBdate)GETDATE()+ @.[User::FileDestinationPath]

and it rename the file just like i wanted and put the date before the old name.

Look i'm new at this thing to so i don't know if i've explained well. But i hope that you can use something.

|||

If you receive an error when the package starts saying that a source or destination placeholder variable is empty you can typically fix it this way: simply ensure that the default (initial) value of the variable is set to a filename.

Setting DelayedValidation on the loop may also help in your case.

Donald

File System Task in SSIS

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.

File System Task exhibits strange behaviour ?

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

File System Task exhibits strange behaviour ?

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

File System Task Error - Process in use

Hi,

In the integration services package i'm working on i connect to an excel database and have two data flows which deal with two work sheets in the excel spreadsheet. The data flows do some transformation and then write the data out to a SQL database (two differnet tables). This is all working great however what i want to do once both data flows are complete is to move the directory which contains the current file that is being looped through. At present there is a foreach loop going through a number of directories. I have tried to implement the File System Task object to move the directory but get the following error:

[File System Task] Error: An error occurred with the following error message: "The process cannot access the file because it is being used by another process.".

I've read a similar post where by the problem was due to not closing the excel connection before doing the File System Task. I cannot seem to find where i would be able to do this.

If anyone has any ideas i'm looking forward to hearing them.

Many thanks in advance,

Grant

i'm not sure what's causing your problem. however, my hunch is that a data flow task is still executing when control is passed to the file system task. you need make sure that both data flow tasks have completed before control is passed to the next task. one way to do this is to place both data flow tasks within a single sequence container, and then link this container to the next task. that way, control won't proceed to the next task until both data flow tasks have completed.

i hope this helps.

|||

Hi Duane,

Thanks for the response. I had the same thought that the task hadn't released the file when it came to the moving of the directory. I have tried the suggestion of using the sequence container but i still get the same result. The move directory task still thinks it is being used by another process.

Any further suggestion would be welcomed. I thinking there must be a way to get round this or i'm sure it could be a common problem.

Many Thanks,

Grant

|||Hi Duane,

Further to my last post. After some debugging i have found that it is the directory itself that seems to be in use by another process. I can remove the contents of the file no proble at all. Does this shed any more light on the matter?

Cheers,

Grant|||Hi,

I have found what i believe to be my problem and can't believe i didn't see it earlier. I'm doing all of my file and directory processing within a foreach loop. Hence when i come to moving the current directory i'm sure the foreach loop is still using the folder as it enumerated value. I need to be able to somehow call the move directory task outwith the loop so that it doesn't have control of the process. Could anyone suggest a way of doing this?

Thanks in advance.

Grant|||

if i understand you correctly, you want both data flows to finish before control proceeds to moving the files. also, you want this process to repeat once for each file that you have. i'm also assuming that you have two seperate directories of files that you want to process.

if my understanding above is correct, then you need to do the following: place two sequence containters inside a foreach loop container. connect an on success constraint from one sequence container to the other. there should be two seperate data flow tasks inside the first sequence container. only when this sequence container succeeds will control flow to the second sequence container. the second sequence container should contain two seperate file system tasks. only when this sequence container succeeds will control flow to the next iteration of the foreach loop container.

i hope this helps.

|||Hi,

I'll clarify what i have set up at present.

There is a root directory in which a number of directories are stored. Within each of these directories are two files (one CSV and one XLS). We can ignore the CSV file just now. I have two data flow tasks, one which extracts transforms and loads data from one worksheet into a SQL table and another which does a similar process but from a different worksheet and to a different SQL table. Upon completion of both these tasks (which are now in a Sequence container) i want to move the directory containing both the files to an archive location. regardless of whether the file system tasks are in a sequence container there is still an error relating to the directory move. It still believes that the process is in use.
I still think that because the foreach loop is looping through the directories looking for each XLS file it is taking a hold on the directory itself and will not release it until the current directory until the next iteration of the loop.

With this thought in mind i thought that i could do something at the beginning of the loop to remove the directory that was referenced in the proevious iteration (possibly set a variable at the end of each iteration). The only problem i encouter when i try this is that because the variable has to be used as a source/destination value i cannot have this as a blank value when starting i get errors when this is attempted. I need to really skip the move directory on the first iteration and add an additional one at the end of the foreach loop.

My other thought is to just copy the directories during the loop and delete them after the loop has completed. I am aware however that using this method i run the risk that if something causes the package to crashh mid way through it might duplicate directories between the processing and archive directories.

Does this make any more sense.

Cheers,

Grant|||I have the problem resolved.

I have added the move directory task to the start of the foreach loop. A script bypasses the task on the first loop as there isn't a previous folder to archive. The archive folder path is assigned to the variable later in the loop. The next iteration the loop then has a folder to move in the variable which works no problem. After the loop is finished one final move task is added to deal with the final processed folder.

The reason it wouldn't allow an empty string as a connection source to begin with was because the "Delay Validation" property was set to false. If this is set to true the problem doesn't exist.

Thanks for all your help on this matter.

Grant

File System Task Error

Hi,
I am using the 'File System Task ' to create a directory structure (e.g ..\DB; ..\DB\LOG; ..\DB\BACKUP; )
I set following properties for the single tasks: UseDirectoryIfExists = True; Operation = Create Directory;

The task works fine before installing SP1 on the server. Now it creates an ERROR if the directory already exists and it is not empty.

SSIS package "testcreatedirectory.dtsx" starting.

Warning: 0xC002915A at Create DB Directory, File System Task: The Directory already exists.

Error: 0xC002F304 at Create DB Directory, File System Task: An error occurred with the following error message: "Das Verzeichnis ist nicht leer.". (The Directory is not empty.)

Task failed: Create DB Directory

Warning: 0x80019002 at Create Directorys: 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.

Warning: 0x80019002 at testcreatedirectory: 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.

SSIS package "testcreatedirectory.dtsx" finished: Failure.

Does anyone know if this is a known bug in SP1 or maybe its a feature and if there already exists a solution (maybe I have to set additional properties I have not discovered as yet).

Thanks in advance
Holger

I have just ran into the same problem after applying Service Pack 1. Have you resolved it yet?|||i don't know if this will help with your particular situation, but hotfix packages for sql server 2005 sp1 were recently released: http://support.microsoft.com/kb/918222/en-us|||I installed the SSIS hotfix but I still get the same results. I guess I need to check on Microsoft Bug list site to see if it's been posted.|||I also tried the hotfix (last week) - with the same result . Have you checked the Microsoft Bug list?|||

I don't know if there is a proper fix for this but you might want to use a item loop container to loop over the paths that you want created and then just have two tasks in there. One task would be a script task to check the existance and the other to create if it does not.

This would achieve the same result as the former single task.

Fred

|||

Has anyone been able to solve this problem without an unofficial workaround?
All my packages uses this task to ease logging and currently they are all failing.

|||

No, I have not resolved the issue yet. The only soloution I have are work arounds.

|||

Could some body help me with this same issue. How do we do the work around method.

The first time i run it works fine and creates some file inside but when i execute the pacakge for the second time. It throughs up error saying that the directory is not empty. If i go and delete the files manually it write the files corretly. i have the File system task with UseIFDirectoryExists = True and operation = create directory.

Any help,

Thanks,

JA

|||I had the same issue, but realized that within the constraints of the task there was no "real" solution. I set the "Force Execution Result" property of the File Systme Object (FSO) Task to "Success". This solved the issue. It would still create a directory if it didn't exist and leave it if it didn't. Then I used another FSO task to move the files I wanted to rename to the new directory. So in this case I used the move option to move and rename. I often use the move option to rename things, if the rename won't work. To tell you the truth the move option might do what you wan't as well. I can't tell because I used both, first the create, then the move.|||

So you had the same the FSO with ForceExecutionResult = success and usedirectoryif exists = true and operation = create Directory. It does not work for me. Even if i use next step to move files to a new directory. you have to create the directory in first place and then move and then rename or delete. this is what i get.

Source: Creating Directoy Folder
Description: The Directory already exists.
End Warning
Error: 2006-09-08 12:21:31.59
Code: 0xC002F304
Source: Creating Directoy Folder
Description: An error occurred with the following error message: "The directory is not empty.

Any idea about this error

Thanks,

JA

|||Like I said, there is no "real" solution with the task. It just doesn't do what it should. What I offered was a temporary fix to have the task move on regardless. The only other fix, as others have mentioned is doing it in a script, utilizing the File System Object. It seems to be a bug.

File System Task Error

Hi,
I am using the 'File System Task ' to create a directory structure (e.g ..\DB; ..\DB\LOG; ..\DB\BACKUP; )
I set following properties for the single tasks: UseDirectoryIfExists = True; Operation = Create Directory;

The task works fine before installing SP1 on the server. Now it creates an ERROR if the directory already exists and it is not empty.

SSIS package "testcreatedirectory.dtsx" starting.

Warning: 0xC002915A at Create DB Directory, File System Task: The Directory already exists.

Error: 0xC002F304 at Create DB Directory, File System Task: An error occurred with the following error message: "Das Verzeichnis ist nicht leer.". (The Directory is not empty.)

Task failed: Create DB Directory

Warning: 0x80019002 at Create Directorys: 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.

Warning: 0x80019002 at testcreatedirectory: 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.

SSIS package "testcreatedirectory.dtsx" finished: Failure.

Does anyone know if this is a known bug in SP1 or maybe its a feature and if there already exists a solution (maybe I have to set additional properties I have not discovered as yet).

Thanks in advance
Holger

I have just ran into the same problem after applying Service Pack 1. Have you resolved it yet?|||i don't know if this will help with your particular situation, but hotfix packages for sql server 2005 sp1 were recently released: http://support.microsoft.com/kb/918222/en-us|||I installed the SSIS hotfix but I still get the same results. I guess I need to check on Microsoft Bug list site to see if it's been posted.|||I also tried the hotfix (last week) - with the same result . Have you checked the Microsoft Bug list?|||

I don't know if there is a proper fix for this but you might want to use a item loop container to loop over the paths that you want created and then just have two tasks in there. One task would be a script task to check the existance and the other to create if it does not.

This would achieve the same result as the former single task.

Fred

|||

Has anyone been able to solve this problem without an unofficial workaround?
All my packages uses this task to ease logging and currently they are all failing.

|||

No, I have not resolved the issue yet. The only soloution I have are work arounds.

|||

Could some body help me with this same issue. How do we do the work around method.

The first time i run it works fine and creates some file inside but when i execute the pacakge for the second time. It throughs up error saying that the directory is not empty. If i go and delete the files manually it write the files corretly. i have the File system task with UseIFDirectoryExists = True and operation = create directory.

Any help,

Thanks,

JA

|||I had the same issue, but realized that within the constraints of the task there was no "real" solution. I set the "Force Execution Result" property of the File Systme Object (FSO) Task to "Success". This solved the issue. It would still create a directory if it didn't exist and leave it if it didn't. Then I used another FSO task to move the files I wanted to rename to the new directory. So in this case I used the move option to move and rename. I often use the move option to rename things, if the rename won't work. To tell you the truth the move option might do what you wan't as well. I can't tell because I used both, first the create, then the move.|||

So you had the same the FSO with ForceExecutionResult = success and usedirectoryif exists = true and operation = create Directory. It does not work for me. Even if i use next step to move files to a new directory. you have to create the directory in first place and then move and then rename or delete. this is what i get.

Source: Creating Directoy Folder
Description: The Directory already exists.
End Warning
Error: 2006-09-08 12:21:31.59
Code: 0xC002F304
Source: Creating Directoy Folder
Description: An error occurred with the following error message: "The directory is not empty.

Any idea about this error

Thanks,

JA

|||Like I said, there is no "real" solution with the task. It just doesn't do what it should. What I offered was a temporary fix to have the task move on regardless. The only other fix, as others have mentioned is doing it in a script, utilizing the File System Object. It seems to be a bug.

File System Task Error

Hi,
I am using the 'File System Task ' to create a directory structure (e.g ..\DB; ..\DB\LOG; ..\DB\BACKUP; )
I set following properties for the single tasks: UseDirectoryIfExists = True; Operation = Create Directory;

The task works fine before installing SP1 on the server. Now it creates an ERROR if the directory already exists and it is not empty.

SSIS package "testcreatedirectory.dtsx" starting.

Warning: 0xC002915A at Create DB Directory, File System Task: The Directory already exists.

Error: 0xC002F304 at Create DB Directory, File System Task: An error occurred with the following error message: "Das Verzeichnis ist nicht leer.". (The Directory is not empty.)

Task failed: Create DB Directory

Warning: 0x80019002 at Create Directorys: 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.

Warning: 0x80019002 at testcreatedirectory: 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.

SSIS package "testcreatedirectory.dtsx" finished: Failure.

Does anyone know if this is a known bug in SP1 or maybe its a feature and if there already exists a solution (maybe I have to set additional properties I have not discovered as yet).

Thanks in advance
Holger

I have just ran into the same problem after applying Service Pack 1. Have you resolved it yet?|||i don't know if this will help with your particular situation, but hotfix packages for sql server 2005 sp1 were recently released: http://support.microsoft.com/kb/918222/en-us|||I installed the SSIS hotfix but I still get the same results. I guess I need to check on Microsoft Bug list site to see if it's been posted.|||I also tried the hotfix (last week) - with the same result . Have you checked the Microsoft Bug list?|||

I don't know if there is a proper fix for this but you might want to use a item loop container to loop over the paths that you want created and then just have two tasks in there. One task would be a script task to check the existance and the other to create if it does not.

This would achieve the same result as the former single task.

Fred

|||

Has anyone been able to solve this problem without an unofficial workaround?
All my packages uses this task to ease logging and currently they are all failing.

|||

No, I have not resolved the issue yet. The only soloution I have are work arounds.

|||

Could some body help me with this same issue. How do we do the work around method.

The first time i run it works fine and creates some file inside but when i execute the pacakge for the second time. It throughs up error saying that the directory is not empty. If i go and delete the files manually it write the files corretly. i have the File system task with UseIFDirectoryExists = True and operation = create directory.

Any help,

Thanks,

JA

|||I had the same issue, but realized that within the constraints of the task there was no "real" solution. I set the "Force Execution Result" property of the File Systme Object (FSO) Task to "Success". This solved the issue. It would still create a directory if it didn't exist and leave it if it didn't. Then I used another FSO task to move the files I wanted to rename to the new directory. So in this case I used the move option to move and rename. I often use the move option to rename things, if the rename won't work. To tell you the truth the move option might do what you wan't as well. I can't tell because I used both, first the create, then the move.|||

So you had the same the FSO with ForceExecutionResult = success and usedirectoryif exists = true and operation = create Directory. It does not work for me. Even if i use next step to move files to a new directory. you have to create the directory in first place and then move and then rename or delete. this is what i get.

Source: Creating Directoy Folder
Description: The Directory already exists.
End Warning
Error: 2006-09-08 12:21:31.59
Code: 0xC002F304
Source: Creating Directoy Folder
Description: An error occurred with the following error message: "The directory is not empty.

Any idea about this error

Thanks,

JA

|||Like I said, there is no "real" solution with the task. It just doesn't do what it should. What I offered was a temporary fix to have the task move on regardless. The only other fix, as others have mentioned is doing it in a script, utilizing the File System Object. It seems to be a bug.

File system Task -Delete Directory Content

This delete's the directory content as well as sub directory content! Is their a way i can just set it to delete the files in the said directory and not sub directories?Do you mean the FIle System Task? File System Object was a feature of WSH and used a lot in DTS, not SSIS.|||Yes i do mean the file system task in ssis. It has an option to delete directory content that deletes subdirevtory content as well. I just want to delete the files in the directory and not the sub directory. Can this task do this or do i have to revert to writing script using the File System Object to do this?|||You could use a For Each Loop to iterate the files in the directory and then the File System Task to delete them individually.
|||

Mike Tomkies wrote:

Yes i do mean the file system task in ssis. It has an option to delete directory content that deletes subdirevtory content as well. I just want to delete the files in the directory and not the sub directory. Can this task do this or do i have to revert to writing script using the File System Object to do this?

Use a foreach loop to spin through the directory of your choosing. This will allow you to use a wildcard to get at the files you are specifically interested in deleting (even if it's *.*). Then inside the foreach loop, use a File System Task set to "Delete File". Pass in the file name from the foreach loop and you should be set.|||This for each loo how will it read the file names to pass to the task to delete?|||

Mike Tomkies wrote:

This for each loo how will it read the file names to pass to the task to delete?

It's built to do that. Click on the Collection tab and the enumerator should be set to "Foreach File Enumerator". From here, just give it a base directory and let it go to work.|||Thanks that is what i need.|||Think it would of been easy if The File system Task had the Traverse SubFolders option as in the ForEachLoop Container.

File System Task - relative location ?

hi,

I am using "File System Task" to copy files from one pre-defined location to another.

Is there a way to provide a "relative path" for the corresponding connections? If yes, how to set it up for deployment?

If not, I guess the option will be to update the "connection string" property for the file connections using package configurations.

thanks,
NiteshAnother option is to store the "root" location in a folder (it can be populated from a configuration) and you can then use property expressions on your file connection manager connection strings to use that variable.

-Jamie