Renaming a File to include Date and Time

Hi

I am trying to move a flat file to an archive folder and then rename the file to include the date and time of runtime.

I have set up a File System Task to move the file which works fine and a second File System Task to rename. The IsDestinationPathVariable is set to TRUE and the DestinationVariable is called User::Error_Log_File_Rename. There is an expression as follows:

@.[User::Error_Log_File_Rename] = "Y:\\SSIS_PUD_Upload\\Error_Log_Archive\\Update_Error_Messages - " +REPLACE((DT_WSTR, 30)(DT_DBDATE)GETDATE() + "-" + (DT_WSTR, 30)(DT_DBTIME)GETDATE(),":","-") +".txt"

which is the required file name.

If I go into the expression and evaluate it it will give me the current date and time. However when I run it as part of the package say 10 minutes later the file is renamed using the same date and time from 10 minutes before when I evaluated the expression. If I wait another 10 minutes and run the package again it still retains the original date and time.

Is there a way to get the expression evaluated with the current date and time during execution of the package?

Thanks in advance

Scott

Are you using the expression section of the properties for the variable, User::Error_Log_File_Rename? And you have EvaluateAsExpression set to true?|||

Hi Phil

I have the variable in the expression section in the properties but I cannot see a 'EvaluateAsExpression' setting that I can set to true?

Where would i see this?

Thanks

Scott

|||

Racsco wrote:

Hi Phil

I have the variable in the expression section in the properties but I cannot see a 'EvaluateAsExpression' setting that I can set to true?

Where would i see this?

Thanks

Scott

Scott,

Select your variable in the variables panel and press F4. This will bring up the properties panel, displaying the properties of the variable. One of the properties is called EvaluateAsExpression. Set it to TRUE.

-Jamie

Removing protected beta files

I cannot install SQL SErver 2005 because of files still on my hard disk from beta version. These include sqlmgmprovider.dll and svrenumapi.dll. The uninstall program is no longer available. How do I get rid of whatever remains of the old beta installation so that the released version of SQL Server 2005 will install?

There are a few different threads on this, so I'm copy/pasting a couple of the best:

1. Uninstall all visible Beta components in Add/Remove Programes.

2. Detect the Beta components infromation in the the log file located in %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup*_%machinename%_Core(Local).log. * stands for a number. Check the latest log file.

3. Search the whole registry keys, remove those registy keys related to those Beta components. This is time-consuming.

4. Kill all Beta SQL Services (including SQL, OLAP, RS, SQLBROWSER, SQLAGENT, etc.) if they are existing with command sc.

5. Reboot the machine.

6. Start Setup again.

If these don't work, check out this thread using some of the zap tools:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89919&SiteID=1

Thanks,
Sam Lester (MSFT)

|||

Thanks, Sam,

The zap tool worked and I was able to get past the objection to the old beta files. However, the install was not completely successful. In fact the main part of it did not install successfully. Do you have any hints on how to diagnose this problem?

Allen

(moontube)

|||

What SKU are you trying to install and on what OS? If you're going through a command line install, can you supply the command line string? If you're going through UI, where is it failing?

Go ahead and search your install logs in %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG for the text string "value 3" and then copy/paste the 10-20 or so lines above that message. This should give us a more descript error message.

Thanks,
Sam

|||

Hi Sam,

Here is the LOG information you asked for.

MSI (s) (B0:04) [22:58:52:948]: Executing op: UpgradeCodePublish(UpgradeCode={929C9FEC-8873-4A1A-A209-9AF432E8E1D1})
MSI (s) (B0:04) [22:58:52:948]: Executing op: SourceListPublish(,,DiskPromptTemplate=Please insert next disk,PackagePath=\Setup\,NumberOfDisks=1)
MSI (s) (B0:04) [22:58:52:948]: Note: 1: 1402 2: UNKNOWN\Installer\Products\1EB3A031CC585314E87AA527E46EECC2\SourceList 3: 2
MSI (s) (B0:04) [22:58:52:968]: Executing op: ProductPublishClient(,,)
MSI (s) (B0:04) [22:58:52:968]: Executing op: SourceListRegisterLastUsed(SourceProduct={130A3BE1-85CC-4135-8EA7-5A724EE6CE2C},LastUsedSource=F:\Setup\)
MSI (s) (B0:04) [22:58:52:968]: Entering CMsiConfigurationManager::SetLastUsedSource.
MSI (s) (B0:04) [22:58:52:968]: Machine policy value 'AllowLockdownMedia' is 0
MSI (s) (B0:04) [22:58:52:978]: Specifed source is already in a list.
MSI (s) (B0:04) [22:58:52:978]: User policy value 'SearchOrder' is 'nmu'
MSI (s) (B0:04) [22:58:52:978]: Machine policy value 'DisableBrowse' is 0
MSI (s) (B0:04) [22:58:52:978]: Machine policy value 'AllowLockdownBrowse' is 0
MSI (s) (B0:04) [22:58:52:978]: Adding new sources is allowed.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedSource to: F:\Setup\.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedType to: m.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedIndex to: 1.
MSI (s) (B0:04) [22:58:52:978]: Executing op: ActionStart(Name=CreateShortcuts,Description=Creating shortcuts,Template=Shortcut: [1])
MSI (s) (B0:04) [22:58:52:978]: Executing op: SetTargetFolder(Folder=23\Microsoft SQL Server 2005\Configuration Tools\)
MSI (s) (B0:04) [22:58:52:978]: SHELL32::SHGetFolderPath returned: E:\Documents and Settings\All Users\Start Menu\Programs
MSI (s) (B0:04) [22:58:52:998]: Executing op: ShortcutCreate(Name=SqlSAC|SQL Server Surface Area Configuration,,,FileName=E:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe,,,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:038]: Verifying accessibility of file: SQL Server Surface Area Configuration.lnk
MSI (s) (B0:04) [22:58:53:178]: Executing op: ShortcutCreate(Name=SqlWtsn|SQL Server Error and Usage Reporting,,,FileName=E:\Program Files\Microsoft SQL Server\90\Shared\SqlWtsn.exe,,,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:188]: Verifying accessibility of file: SQL Server Error and Usage Reporting.lnk
MSI (s) (B0:04) [22:58:53:218]: Executing op: ShortcutCreate(Name=SqlConfM|SQL Server Configuration Manager,,,FileName=E:\WINDOWS\system32\mmc.exe,Arguments=/32 "E:\WINDOWS\system32\SQLServerManager.msc",,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:228]: Verifying accessibility of file: SQL Server Configuration Manager.lnk
MSI (s) (B0:04) [22:58:53:248]: Executing op: ActionStart(Name=Set_CommitFlag.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Completing Commit,)
MSI (s) (B0:04) [22:58:53:609]: Executing op: CustomActionSchedule(Action=Set_CommitFlag.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1537,Source=BinaryData,Target=Set_CommitFlag,CustomActionData=010Completing Commit500001)
MSI (s) (B0:04) [22:58:53:669]: Executing op: End(Checksum=0,ProgressTotalHDWord=0,ProgressTotalLDWord=360632758)
MSI (s) (B0:04) [22:58:54:009]: Assembly Error:The process cannot access the file because it is being used by another process.

MSI (s) (B0:04) [22:58:54:019]: Note: 1: 1935 2: {4C466AC6-57EA-4944-A4CB-795A9E01FA61} 3: 0x80070020 4: IAssemblyCacheItem 5: Commit 6: Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"
MSI (s) (B0:04) [23:06:15:815]: Product: Microsoft SQL Server 2005 -- Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x80070020. assembly interface: IAssemblyCacheItem, function: Commit, component: {4C466AC6-57EA-4944-A4CB-795A9E01FA61}

Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x80070020. assembly interface: IAssemblyCacheItem, function: Commit, component: {4C466AC6-57EA-4944-A4CB-795A9E01FA61}
MSI (s) (B0:04) [23:06:15:865]: User policy value 'DisableRollback' is 0
MSI (s) (B0:04) [23:06:15:865]: Machine policy value 'DisableRollback' is 0
Action ended 23:06:15: InstallFinalize. Return value 3.

|||

So the error is:

Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,

Typically this is a failure when trying to install a file to the GAC. Did you remove the beta version of SQL Native Client? That sometimes causes the problem. If you didn't remove it, can you uninstall it and try the install again?

Thanks,
Sam

|||

Hi Sam,

Thanks. your suggestion did the trick. I unistalled the corrupted version of SQL Server that I had just installed, rebooted, and installed it again. This time it worked.

Allen

|||Great to hear and thanks for replying back! For future reference, was it the SQL Native Client uninstall that fixed it for you?

Thanks,
Sam|||

Hi Sam,

I uninstalled everything related it SQL Server, including the Native Client. The only thing I didn't unistall is the books, because I could not uninstall them once SQL Server had been uninstalled.

Allen

Removing protected beta files

I cannot install SQL SErver 2005 because of files still on my hard disk from beta version. These include sqlmgmprovider.dll and svrenumapi.dll. The uninstall program is no longer available. How do I get rid of whatever remains of the old beta installation so that the released version of SQL Server 2005 will install?

There are a few different threads on this, so I'm copy/pasting a couple of the best:

1. Uninstall all visible Beta components in Add/Remove Programes.

2. Detect the Beta components infromation in the the log file located in %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup*_%machinename%_Core(Local).log. * stands for a number. Check the latest log file.

3. Search the whole registry keys, remove those registy keys related to those Beta components. This is time-consuming.

4. Kill all Beta SQL Services (including SQL, OLAP, RS, SQLBROWSER, SQLAGENT, etc.) if they are existing with command sc.

5. Reboot the machine.

6. Start Setup again.

If these don't work, check out this thread using some of the zap tools:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89919&SiteID=1

Thanks,
Sam Lester (MSFT)

|||

Thanks, Sam,

The zap tool worked and I was able to get past the objection to the old beta files. However, the install was not completely successful. In fact the main part of it did not install successfully. Do you have any hints on how to diagnose this problem?

Allen

(moontube)

|||

What SKU are you trying to install and on what OS? If you're going through a command line install, can you supply the command line string? If you're going through UI, where is it failing?

Go ahead and search your install logs in %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG for the text string "value 3" and then copy/paste the 10-20 or so lines above that message. This should give us a more descript error message.

Thanks,
Sam

|||

Hi Sam,

Here is the LOG information you asked for.

MSI (s) (B0:04) [22:58:52:948]: Executing op: UpgradeCodePublish(UpgradeCode={929C9FEC-8873-4A1A-A209-9AF432E8E1D1})
MSI (s) (B0:04) [22:58:52:948]: Executing op: SourceListPublish(,,DiskPromptTemplate=Please insert next disk,PackagePath=\Setup\,NumberOfDisks=1)
MSI (s) (B0:04) [22:58:52:948]: Note: 1: 1402 2: UNKNOWN\Installer\Products\1EB3A031CC585314E87AA527E46EECC2\SourceList 3: 2
MSI (s) (B0:04) [22:58:52:968]: Executing op: ProductPublishClient(,,)
MSI (s) (B0:04) [22:58:52:968]: Executing op: SourceListRegisterLastUsed(SourceProduct={130A3BE1-85CC-4135-8EA7-5A724EE6CE2C},LastUsedSource=F:\Setup\)
MSI (s) (B0:04) [22:58:52:968]: Entering CMsiConfigurationManager::SetLastUsedSource.
MSI (s) (B0:04) [22:58:52:968]: Machine policy value 'AllowLockdownMedia' is 0
MSI (s) (B0:04) [22:58:52:978]: Specifed source is already in a list.
MSI (s) (B0:04) [22:58:52:978]: User policy value 'SearchOrder' is 'nmu'
MSI (s) (B0:04) [22:58:52:978]: Machine policy value 'DisableBrowse' is 0
MSI (s) (B0:04) [22:58:52:978]: Machine policy value 'AllowLockdownBrowse' is 0
MSI (s) (B0:04) [22:58:52:978]: Adding new sources is allowed.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedSource to: F:\Setup\.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedType to: m.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedIndex to: 1.
MSI (s) (B0:04) [22:58:52:978]: Executing op: ActionStart(Name=CreateShortcuts,Description=Creating shortcuts,Template=Shortcut: [1])
MSI (s) (B0:04) [22:58:52:978]: Executing op: SetTargetFolder(Folder=23\Microsoft SQL Server 2005\Configuration Tools\)
MSI (s) (B0:04) [22:58:52:978]: SHELL32::SHGetFolderPath returned: E:\Documents and Settings\All Users\Start Menu\Programs
MSI (s) (B0:04) [22:58:52:998]: Executing op: ShortcutCreate(Name=SqlSAC|SQL Server Surface Area Configuration,,,FileName=E:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe,,,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:038]: Verifying accessibility of file: SQL Server Surface Area Configuration.lnk
MSI (s) (B0:04) [22:58:53:178]: Executing op: ShortcutCreate(Name=SqlWtsn|SQL Server Error and Usage Reporting,,,FileName=E:\Program Files\Microsoft SQL Server\90\Shared\SqlWtsn.exe,,,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:188]: Verifying accessibility of file: SQL Server Error and Usage Reporting.lnk
MSI (s) (B0:04) [22:58:53:218]: Executing op: ShortcutCreate(Name=SqlConfM|SQL Server Configuration Manager,,,FileName=E:\WINDOWS\system32\mmc.exe,Arguments=/32 "E:\WINDOWS\system32\SQLServerManager.msc",,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:228]: Verifying accessibility of file: SQL Server Configuration Manager.lnk
MSI (s) (B0:04) [22:58:53:248]: Executing op: ActionStart(Name=Set_CommitFlag.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Completing Commit,)
MSI (s) (B0:04) [22:58:53:609]: Executing op: CustomActionSchedule(Action=Set_CommitFlag.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1537,Source=BinaryData,Target=Set_CommitFlag,CustomActionData=010Completing Commit500001)
MSI (s) (B0:04) [22:58:53:669]: Executing op: End(Checksum=0,ProgressTotalHDWord=0,ProgressTotalLDWord=360632758)
MSI (s) (B0:04) [22:58:54:009]: Assembly Error:The process cannot access the file because it is being used by another process.

MSI (s) (B0:04) [22:58:54:019]: Note: 1: 1935 2: {4C466AC6-57EA-4944-A4CB-795A9E01FA61} 3: 0x80070020 4: IAssemblyCacheItem 5: Commit 6: Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"
MSI (s) (B0:04) [23:06:15:815]: Product: Microsoft SQL Server 2005 -- Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x80070020. assembly interface: IAssemblyCacheItem, function: Commit, component: {4C466AC6-57EA-4944-A4CB-795A9E01FA61}

Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x80070020. assembly interface: IAssemblyCacheItem, function: Commit, component: {4C466AC6-57EA-4944-A4CB-795A9E01FA61}
MSI (s) (B0:04) [23:06:15:865]: User policy value 'DisableRollback' is 0
MSI (s) (B0:04) [23:06:15:865]: Machine policy value 'DisableRollback' is 0
Action ended 23:06:15: InstallFinalize. Return value 3.

|||

So the error is:

Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,

Typically this is a failure when trying to install a file to the GAC. Did you remove the beta version of SQL Native Client? That sometimes causes the problem. If you didn't remove it, can you uninstall it and try the install again?

Thanks,
Sam

|||

Hi Sam,

Thanks. your suggestion did the trick. I unistalled the corrupted version of SQL Server that I had just installed, rebooted, and installed it again. This time it worked.

Allen

|||Great to hear and thanks for replying back! For future reference, was it the SQL Native Client uninstall that fixed it for you?

Thanks,
Sam|||

Hi Sam,

I uninstalled everything related it SQL Server, including the Native Client. The only thing I didn't unistall is the books, because I could not uninstall them once SQL Server had been uninstalled.

Allen

Removing Columns and/or Rows on Export

I have a Report I need to hide subtotals and total and include detail only when I am exporting to excel. So, is there a way to capture that it is exporting to Excel? Possibly a way to capture the rs:Format=Excel?

Hello,

I don't think you'll be able to do this, but you could always create a report parameter that would hold the RenderMethod. Then, you can change the formatting of your report based on this value to display how you want.

Jarret

|||True, but I was hoping I might be able to accomplish it without having to have a user go though the step of chosing, since they are already choosing to export to excel.|||

YOu can′t change the output based on a different rendering format, as already mentioned you would have to make a parametrized report, discarding some fields in the case of a specific parameters OR design another report which has the wanted output format.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

That was what I was affraid of, just wanted more experienced minds to confirm it for me.

Thanks for the help.