SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Variable Expressions


SSIS Variable Expressions

Author
Message
Carol Adams
Carol Adams
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 342
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.
Phil Parkin
Phil Parkin
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26058 Visits: 20679
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Carol Adams
Carol Adams
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 342
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?
Phil Parkin
Phil Parkin
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26058 Visits: 20679
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Carol Adams
Carol Adams
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 342
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
Phil Parkin
Phil Parkin
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26058 Visits: 20679
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Carol Adams
Carol Adams
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 342
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]
Tim Mitchell
Tim Mitchell
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2205 Visits: 3009
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


Carol Adams
Carol Adams
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 342
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?
Tim Mitchell
Tim Mitchell
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2205 Visits: 3009
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search