August 20, 2011 at 1:25 pm
Hi !
I Have 2 variable that contain the Path to database files locations at destination.I am copying the Database to multiple locations using Transfer database task. can some one help in building the expression for destination database files expression . please find my expression below that i was getting the error .thanks in advance 🙂
"TestData.mdf"," @[User::V_DataFile] ","";"Test_log.LDF"," @[User::V_LogFile]",""
August 21, 2011 at 12:24 pm
Please find the error message as below that i was getting ....
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
------------------------------
ADDITIONAL INFORMATION:
Attempt to parse the expression ""TestData.mdf","@[User::V_DataFile] ","";"TestLog_log.LDF","@[User::V_LogFile]",""" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
(Microsoft.DataTransformationServices.Controls)
------------------------------
BUTTONS:
OK
------------------------------
August 21, 2011 at 4:32 pm
The Data File and Log File should be separate variables.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 21, 2011 at 5:37 pm
ok.. I do have 2 variables one for data file and log file as @[User::V_DataFile], @[User::V_LogFile]. .So i want to define the files in run time and make them as dynamic.. So in expression for property "destination database file" what would be the expression ???? Thanks in advance
August 21, 2011 at 5:58 pm
Before you dynamically set the flat file you need a script task to identify the correct Data and Log File using VB.NET or C#, etc and set the variable to the file names.
The following list an example of a dynamic flat file connection
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 21, 2011 at 7:07 pm
yeah i have query that pulls the data and log files along with path and i am assigning them with variables. Now the Question is how to have the expression for destination database files in property dialog for the "Tansfer Database Task" So i am looking for expression in "Transfer Database task" and not for flat file connections...
August 21, 2011 at 10:04 pm
I thought that you might take the backup and apply to the destination backup.
If your using the Database Transfer Task you will need a variable for the Source and the Destination Databases.
You will need to assign values to the variables.
I'm not sure how you you were trying to assign values to the expression but that is not the right syntax.
You simply double click on the Transfer Database Task (Source).
Then click on the expressions node.
Click on the Property Drop Down and select Source Database Name.
Click on the Expression Drop Down and specify the Source Database Variable.
Then double click on the destination Database Transfer Task.
Click on the expressions node.
Click on the Property Drop Down and select Destination Database Name.
Click on the Expression Drop Down and specify the Destination Database Variable.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 22, 2011 at 1:35 pm
Hi !
I still Getting the error in expression for destination database files ... can some one shade some light on expression as i need to make the path for the database files dynamic in transfer database task ....
August 22, 2011 at 3:05 pm
RamSteve (8/22/2011)
Hi !I still Getting the error in expression for destination database files ... can some one shade some light on expression as i need to make the path for the database files dynamic in transfer database task ....
How are you assigning the value to the expression?
Can you post your code?
What error are you getting?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 22, 2011 at 3:24 pm
Now i am having the following expression for destination database files as below..and the error that i was getting is
Variables declared:-
I run a query at destination and get the path for data and log file and assign with variables as below .
V_Datafilepath =select SubString(Physical_name,1,CHARINDEX(N'MediSpan_log.ldf',Lower(Physical_Name))-2) As Physical_Name from sys.database_files where type=1
V_Logfilepath =select SubString(Physical_name,1,CHARINDEX(N'MediSpan_log.ldf',Lower(Physical_Name))-2) from sys.database_files where type=1
V1="
V2="
V_Datafile="MediSpan.mdf"
V_Logfile="MediSpan_log.ldf"
Expression :-for Destination Database files
@[User::V_Datafile]+","+@[User::V1]+@[User::V_Datafilepath]+@[User::V2]+","+@[User::V1]+@[User::V2]+
";"+@[User::V_Logfile]+","+@[User::V1]+@[User::V_Logfilepath]+@[User::V2]+","+@[User::V1]+@[User::V2]
Error Message i was getting now as below
Error: The Execute method on the task returned error code 0x80131500 (Drop failed for Database 'MediSpan'. ). The Execute method must succeed, and indicate the result using an "out" parameter.
August 22, 2011 at 3:44 pm
Other people reported this error and it may be a bug.
What SP do you have?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 22, 2011 at 4:01 pm
I am using SQL Server 2008 R2
August 22, 2011 at 4:46 pm
Did you check the value of variable at runtime?
Place a breakpoint and verify that you have the correct syntax.
http://msdn.microsoft.com/en-us/library/ms141204.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 22, 2011 at 5:29 pm
Why don't you start out but setting the properties manually without injecting the dynamic connection properties.
Enter the connection properties to mimic what you are attempting to do dynamically.
Once you have that working compare the dynamic string with the hard coded string.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply