April 21, 2009 at 6:19 pm
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.
April 21, 2009 at 6:34 pm
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' ...
April 21, 2009 at 6:51 pm
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?
April 21, 2009 at 7:02 pm
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.
April 21, 2009 at 7:20 pm
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
April 21, 2009 at 7:24 pm
See my preceding post - the line in bold. It was not showing correctly before, so I just edited it. Maybe that was your problem.
April 21, 2009 at 7:44 pm
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]
April 21, 2009 at 8:34 pm
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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
April 21, 2009 at 8:39 pm
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?
April 21, 2009 at 8:45 pm
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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
April 21, 2009 at 8:49 pm
i have vadb15\vabiv... this is a sql instance on the vadb15 server.
April 21, 2009 at 9:03 pm
It sounds like you're covering all your bases... not sure what's going on here.
How are you setting your variable value - is it set on creation (in the Variables window on the left) or are you setting it within the flow of your program, such as a ForEach loop or similar?
Check the scope of your variable - is it set at the Package level?
Can you post the exact error message you're receiving?
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
April 22, 2009 at 5:51 am
It is defined on the left side of the screen as a "Package" variable.
I have an "Execute Sql Task" that populates the variable.
My Execute SQL Task General Tab has a ResultSet of SingleRow, Connection Type OLE DB, Connection of CCXXSDDB010.ietenterprise, SQL SourceType of Direct Input,
and just to test I have a SQL Statement of "SELECT 'rePORTAL_OLD' AS databasename"
Within the Result Set properties I have RESULT NAME set to databasename and VARIABLE NAME set to User::InDatabase
Nothing under the expressions properties window.
When I do set a VALUE on the left window for the variable and disable the EXECUTE SQL TASK, the stream runs.
WHen I clear out the VALUE on the variable and enable the EXECUTE SQL TASK, before it even gets to the 2nd step which is to popup a msgbox with the database name, it aborts with the following message:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Transfer Database Task [Transfer Database Task]: A source database must be specified.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
April 22, 2009 at 6:54 am
OK, that means there's something in your Execute SQL Task that is not working properly. Can you post details about that task?
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
April 22, 2009 at 7:07 am
My Execute SQL Task General Tab has a ResultSet of SingleRow, Connection Type OLE DB, Connection of CCXXSDDB010.ietenterprise, SQL SourceType of Direct Input,
and just to test I have a SQL Statement of "SELECT 'rePORTAL_OLD' AS databasename"
Within the Result Set properties I have RESULT NAME set to databasename and VARIABLE NAME set to User::InDatabase
Nothing under the expressions properties window.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy