Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

overwriting a database

I am very new to SQL and I hope I am in the right place for this question. I have a database (I believe it is just one table) that needs to be overwritten with an import of a text file every night. I know there is a very easy way to do it, I just don't have a clue what it might be.

The database is IPTVprogramsSQL and the table is programs. I am pretty sure the fields names are matched. Again, I am very new and have been thrown in way over my head.

Thanks for any help I can get.

What tools are you using? If you can execute SQL, just run:

TRUNCATE tablename

Or

DELETE tableName --if truncate won't work

to remove the data. Then just reload it however you are now.

|||if ur using DTS/ssis to do this..simply select the option to delete existing records...befor inserting from txt file.|||

first question - what tool will you use to import the data?

If you're on SQL2005 you should probably look at using SQL Server Integration Services (SSIS) to perform this if it's installed on your server. For SQL2000, you can do it with DTS pretty easily.

There should be an Import Data wizard in Enterprise Manager (or SQL Server Management Studio if you're on 05) that can help you get started. From SSMS, right click the DB you want to target and select Import Data from the Tasks sub-menu item.

Your question is a bit beyond the scope of this group - but hopefully this will get you started in the right direction.

If you want to overwrite the data, you can use the facility of the tool to specify this, or if you're doing it from a stored procedure or T-SQL batch, you can use the truncate table command to empty the table before you load it.

|||I would like to have this as a stored process that is executed by the server agent. I am using 2005 w/SP1. I tried to just import the data and save that procedure. I assume I would do the same thing with deleting or truncating command?|||

create procedure usp_truncate_tablename

as
truncate <table name>

go

Allen_Iowa wrote:

I would like to have this as a stored process that is executed by the server agent. I am using 2005 w/SP1. I tried to just import the data and save that procedure. I assume I would do the same thing with deleting or truncating command?

OverWrite SqlExpress .LDF file ??

Hi :)

I have a website that uses SqlExpress ...on it i have a database that was working ok ...until i made a few modifications to the database (had a few rows).

I have upload the database (only the .mdf file) to the app_data folder ...but now i get this message :

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Cannot open database "ArtWork" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Log file 'c:\xxxx\xxx\xxxx\xxxx\xxxx\App_Data\ArtWork_log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

I try to delete the .LDF file but it gives me access denied ...i cheched the permissions for the file and everything is ok

How can i solve this??

Thanks and Cheers

Have you moved both the .mdf file and .ldf file to new location (c:\xxxx\xxx\xxxx\xxxx\xxxx\App_Data\)?