SSIS Variable Expressions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS Variable Expressions

15 posts, Page 1 of 2

1 2 »»

SSIS Variable Expressions

Rate Topic

Display Mode

Topic Options

Author

Message

Carol Adams

Posted Tuesday, April 21, 2009 6:19 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 7:10 AM
Points: 60, Visits: 284

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

Phil Parkin

Posted Tuesday, April 21, 2009 6:34 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 4,234, Visits: 9,469

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #701981

Carol Adams

Posted Tuesday, April 21, 2009 6:51 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 7:10 AM
Points: 60, Visits: 284

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

Phil Parkin

Posted Tuesday, April 21, 2009 7:02 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 4,234, Visits: 9,469

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #701989

Carol Adams

Posted Tuesday, April 21, 2009 7:20 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 7:10 AM
Points: 60, Visits: 284

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

Phil Parkin

Posted Tuesday, April 21, 2009 7:24 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 4,234, Visits: 9,469

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #701992

Carol Adams

Posted Tuesday, April 21, 2009 7:44 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 7:10 AM
Points: 60, Visits: 284

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

Tim Mitchell

Posted Tuesday, April 21, 2009 8:34 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:11 PM
Points: 1,033, Visits: 2,593

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
www.TimMitchell.net
twitter.com/Tim_Mitchell

Post #702008

Carol Adams

Posted Tuesday, April 21, 2009 8:39 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 7:10 AM
Points: 60, Visits: 284

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

Tim Mitchell

Posted Tuesday, April 21, 2009 8:45 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:11 PM
Points: 1,033, Visits: 2,593

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
www.TimMitchell.net
twitter.com/Tim_Mitchell

Post #702013

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions