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 17 total)
You must be logged in to reply to this topic. Login to reply