PAD function in Data Tranformation Editor

I have a value with a float datatype in Excel that is actually a date. For example, the value displays 1272006 for January 27, 2006. I am trying to convert this value to a date. I can do this in several stages using T-SQL by converting the datatype to integer, using replicate to make it eight characters and than using substrings to make it look like 01-27-2006 which SQL Server will recognize as a date.

My problem is that there is nothing similar to a PAD (replicate) function using the Data Tranformation Editor in Integration Services. There is a replicate function but it merely functions to repeat a value. It doesn't appear to perform the same PAD function as the T-SQL replicate does. Here is my formula in T-SQL that works okay.

right(replicate('0', 8) + convert(varchar, cast([Date] as varchar)), 8)

I could do this by creating a staging table but I want to handle the entire transformation process within Integration Services. Can anyone offer me some advice? Thank you.

David

I don't think we have your full statement... I'm not seeing where you convert to a date...

Never-the-less, why not substring the "date" field and then cast it to DT_DBDATETIME?|||Question, how is Jan 1st, 2006, represented?

112006? Or 1012006?|||1012006|||

I'm not able to use the substring because the values varies between seven and eight characters. For example, January 1, 2006 is 1012006 while October 1, 2006 is 10012006. I figure I need to convert the value to eight characters by padding a zero on the left. I then can use a substring to convert it to a SQL Server recognizable date like 01-01-2006.

David

|||

davidg12 wrote:

I'm not able to use the substring because the values varies between seven and eight characters. For example, January 1, 2006 is 1012006 while October 1, 2006 is 10012006. I figure I need to convert the value to eight characters by padding a zero on the left. I then can use a substring to convert it to a SQL Server recognizable date like 01-01-2006.

David

Okay, so then this should be easy... If you want to do this in one derived column (are you guaranteed that the value is a correct and verified date?), then you can do something like:

(DT_DBTIMESTAMP)(SUBSTRING(RIGHT("0" + datefield,8),5,4) + "-" + SUBSTRING(RIGHT("0" + datefield,8),1,2) + "-" + SUBSTRING(RIGHT("0" + datefield,8),3,2) + " 00:00:00")

In an upstream derived column, you could do the "RIGHT("0" + datefield,8)" calculation, and then just use that field in the above expression. It would make it easier to read.
|||

Thank you Phil. This works great.

David

Package won't run correctly when called

I have a stiuation where I need to import data from Excel and of course I'm using DTS to do so. Because the spreadsheets have a variety of datatypes in them, I have to make sure that DTS recognizes certain rows as INT so they will import properly, to do so I add 5 rows of 1's to the top lines of the individual sheets in the correct columns. After the 1's are entered I import to staging tables and then into the production tables. The package itself is run from a job and the job is called by a stored procedure running sp_start_job. Here is a sample of the ActiveX script I am using to alter the spreadsheets:

*****************************************************************
Function Main()

On Error Resume Next

Dim Excel_Application
Dim Excel_WorkBook
Dim Excel_WorkSheet

Dim sFilename
Dim sSheetName

sFilename = "\\Share\LCS Database Information - Data Test.xls"
sSheetName = "Experience Spreadsheet"
Set Excel_Application = CreateObject("Excel.Application")

Excel_Application.DisplayAlerts = False

' Open the workbook specified
Set Excel_WorkBook = Excel_Application.Workbooks.Open(sFilename)

Excel_WorkBook.Worksheets(sSheetName).Activate

FOR x = 2 TO 6 STEP 1
FOR i = 1 TO 5 STEP 1
Excel_WorkBook.WorkSheets(sSheetName).Cells(i,x).Value = 1
NEXT
NEXT
*************************************************************************

It does this to 3 sheets and then the import steps run.

Now, when I execute the package in Enterprise Manager it works exactly as I want. However when I run it through the stored procedure, the ActiveX doesn't run properly. My stored proc looks essentiall like this:

****************************************************************************
--Creat TEMP table for the DTS package to store values from the spreadsheet
EXEC pCreateTEMPTables

IF @.@.ERROR <> 0
BEGIN
SET @.Segment = 1
GOTO sqlerror
END

--Run the DTS package that will pull data into temp tables so the individual --data points can be used to search up keys
EXEC msdb.dbo.sp_start_job
@.job_name = 'LCSRunDTS'

EXEC more.procs
****************************************************************************

One thing about the spreadsheets is that they have links in them wanring messages get thrown up during the ActiveX script about updating the links. I tried to turn them off but one message, "One or more links can't be updated" which comes up if you click "Update" on the previous warning or supress warnings, just won't go away.

Any ideas? Thanks
Well, I got the first problem above figured out. When I ran the package directly from Enterprise Manager, the ActiveX object was created on my local system and used Excel there to do what it needed to. When I tried to run the package on the server which didn't have Excel installed, my script failed. My new solution (as soon as it works) is to use WshRemote to run the script to alter the spreadsheets using a computer with Excel installed. The following script works when run from my PC but fails with the message "Error Source = Microsoft VBScript runtime error, Error Description: Permission Denied" when run as part of the DTS package called from a stored procedure via dtsrun:
--
Dim Controller, RemoteScript
Set Controller = CreateObject("WSHController")
Set RemoteScript = Controller.CreateScript("\\Server\Share\ExcelAdd1s.vbs", "MyLocalPC")
RemoteScript.Execute

Being new to the DBA world, I need some help with permissions. Is it that the SQL Server account doesn't have access to the shared drive, or to my PC? Where can I look to see what user is running the DTS package? Are there any questions that I'm not asking that I should be? Any help would be appreciated.|||When yuo run the package from EM directly your logged in account needs to have permission to the share. When it runs from an agent job the SQL Agent account must have permission to do the same thing, it must be to a UNC share and not a mapped drive

Package Wont Open

I have just created a package to import data from excel files, and perform some simple sql statements. I saved the package, and when I try to re-open it, I get the following error:

"Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied."

And after I hit th "OK" button, I get the next error msg:

"The selected package cannot be opened. The DTS Designer has been closed"

Is my package completely lost? Can I open it elsewhere, or by changing some properties somewhere? Also, is there something I can do to avoid this in the future?! I hope all my work isn't lost ...

Any help would be greatly appreciated!

BrianClose Enterprise Manager, reopen it, and see if you can read the DTS package. You can still ping the server can't you?|||Thanks for the quick response.

I closed and re-opened enterprise manager, to no avail. I'm working with my localhost and there is no problem pinging. Everything else seems fine, just can't open this package. Any other thoughts?|||It sounds like it got corrupted somehow. I've never actually had this happen. Been doing DTS packages a long time. If you had saved it previously, right-click on the package and look at versions. See if you can look at any of the previous versions.|||I have no idea what's going on, craziness I say. I only had one version when I checked. I've gone through creating these packages all different ways. They save no problem, but I can't re-open them. Any other thoughts? Thanks!sql

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.

Overwrite SQL exported Excel sheet with new data

Hi,

I have set up a DTS package to export data to an excel sheet. If I schedule it the data appends to the exisitng data in the sheet.

How do I overwrite the data or update the data in sheet so that I may schedule a fresh updated copy to be sent to same file?

Thanks

Quote:

Originally Posted by daywho

Hi,

I have set up a DTS package to export data to an excel sheet. If I schedule it the data appends to the exisitng data in the sheet.

How do I overwrite the data or update the data in sheet so that I may schedule a fresh updated copy to be sent to same file?

Thanks

In the create table task, add a drop table :

DROP TABLE `myexcelsheetname`
GO

CREATE TABLE `myexcelsheetname` (
`tDescription` LongText ,
`tIdentifier` VarChar (16) ,
`tUpdateDate` DateTime
)

Overwrite Excel spreadsheet

Hi,

I have a DTS package that imports a file with data,
performs several checks,
puts all invalid rows in a table,
exports the table to a excel spreadsheet.

My problem is that it appends the data every time. I want it to overwrite the existing spreadsheet. I can't find any option for that.

I use the "Transform Data Task" to do this.

Any suggestions?Do an EXECUTE PROCESS Task and supply a DELETE Command...

But I didn't think it did an append...|||I had the same problem this week. I made a template of the excel sheet, selected the columns needed and created a named range on the excel sheet, and added it as a linked server. Once it's linked like this you can treat the named range like a table and use a delete from statement to clear the sheet.

Run this command from QA to make the excel sheet a linked server, 'excelsource' can be any name you want to give the connection.

sp_addlinkedserver 'excelsource', 'jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'c:\temp\filename.xls', null, 'excel 5.0'

If the named range on the excel sheet is also named excelsource use

delete from excelsource...excelsource