Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS Variable Expressions Expand / Collapse
Author
Message
Posted Tuesday, April 21, 2009 6:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 25, 2013 1:42 PM
Points: 60, Visits: 287
I created a transfer database task...I want to use a variable I created "InDatabase" as the database name for my source database.
How do I link the variable to the property for SOURCEDATABASENAME?
I entered User::Indatabase, but when I ran the job I got an error saying the task needed a source database name. THen I opened the properties and the User::Indatabase was gone.

Help please.
Post #701978
Posted Tuesday, April 21, 2009 6:34 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 4,832, Visits: 11,200
Edit the task. Choose 'Expressions' and create a new one by clicking on the ellipsis, then select SourceDatabaseName as your property and enter the variable expression under 'Expression' ...


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #701981
Posted Tuesday, April 21, 2009 6:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 25, 2013 1:42 PM
Points: 60, Visits: 287
ok. i did that, i entered @[User::InDatabase]

but it is still aborting on me. how can a debug it to see what my sqltask is popluating the variable with?
Post #701987
Posted Tuesday, April 21, 2009 7:02 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 4,832, Visits: 11,200
Add a Script task after your SQL task.

Click on Script and In ReadOnlyVariables, enter User::InDatabase


Click on Design Script and enter just one line in Main() to make it look like this:

	Public Sub Main()
'
' Add your code here
'
MsgBox(Dts.Variables("InDatabase").Value)
Dts.TaskResult = Dts.Results.Success
End Sub

Then try running again - a message box containing the contents of the variable should pop up.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #701989
Posted Tuesday, April 21, 2009 7:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 25, 2013 1:42 PM
Points: 60, Visits: 287
I am missing something. It is throwing an exception when i added the msgbox.
THe code has the following files included:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime


This is the error....

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_9d230469898049ae9e322c79be925a0d.ScriptMain.Main

Post #701991
Posted Tuesday, April 21, 2009 7:24 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 4,832, Visits: 11,200
See my preceding post - the line in bold. It was not showing correctly before, so I just edited it. Maybe that was your problem.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #701992
Posted Tuesday, April 21, 2009 7:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 25, 2013 1:42 PM
Points: 60, Visits: 287
ok, that worked.

it has my database name in the msgbox

still saying i need a sourcedatabasename

when i edit the task, sourcedatabasename is empty

expressions has SourceDatabaseName with @[User::Indatabase]



Post #701997
Posted Tuesday, April 21, 2009 8:34 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:43 AM
Points: 1,042, Visits: 2,708
Check the case - you had previously noted the name of the variable was InDatabase, but your most recent post shows it as Indatabase. SSIS is case sensitive for variable names.



Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #702008
Posted Tuesday, April 21, 2009 8:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 25, 2013 1:42 PM
Points: 60, Visits: 287
yeah the case and spelling are correct...
am i right and the SourceDatabaseName property should be blank since I have an expression set up pointing to my variable?
Post #702009
Posted Tuesday, April 21, 2009 8:45 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:43 AM
Points: 1,042, Visits: 2,708
Yes, you are correct that the database name would be left blank if you're using a variable.

Can you check the SourceConnection to make sure you have a valid source specified?




Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #702013
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse