InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 05:18:07 GMT20http://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxMy 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::InDatabaseNothing under the expressions properties window. Wed, 22 Apr 2009 07:07:52 GMTCarol Adamshttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxOK, that means there's something in your Execute SQL Task that is not working properly. Can you post details about that task?Wed, 22 Apr 2009 06:54:44 GMTTim Mitchellhttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxIt 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::InDatabaseNothing 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------------------------------Wed, 22 Apr 2009 05:51:24 GMTCarol Adamshttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxIt 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?Tue, 21 Apr 2009 21:03:53 GMTTim Mitchellhttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxi have vadb15\vabiv... this is a sql instance on the vadb15 server.Tue, 21 Apr 2009 20:49:05 GMTCarol Adamshttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxYes, 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?Tue, 21 Apr 2009 20:45:47 GMTTim Mitchellhttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxyeah 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?Tue, 21 Apr 2009 20:39:12 GMTCarol Adamshttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxCheck 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.Tue, 21 Apr 2009 20:34:10 GMTTim Mitchellhttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxok, that worked.it has my database name in the msgboxstill saying i need a sourcedatabasenamewhen i edit the task, sourcedatabasename is emptyexpressions has SourceDatabaseName with @[User::Indatabase]Tue, 21 Apr 2009 19:44:34 GMTCarol Adamshttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxSee my preceding post - the line in bold. It was not showing correctly before, so I just edited it. Maybe that was your problem.Tue, 21 Apr 2009 19:24:56 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxI am missing something. It is throwing an exception when i added the msgbox.THe code has the following files included:Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeThis is the error.... at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index) at ScriptTask_9d230469898049ae9e322c79be925a0d.ScriptMain.MainTue, 21 Apr 2009 19:20:42 GMTCarol Adamshttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxAdd a Script task after your SQL task.[b]Click on Script and In ReadOnlyVariables, enter User::InDatabase[/b]Click on Design Script and enter just one line in Main() to make it look like this:[code] Public Sub Main() ' ' Add your code here ' MsgBox(Dts.Variables("InDatabase").Value) Dts.TaskResult = Dts.Results.Success End Sub[/code]Then try running again - a message box containing the contents of the variable should pop up.Tue, 21 Apr 2009 19:02:40 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxok. 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?Tue, 21 Apr 2009 18:51:32 GMTCarol Adamshttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxEdit 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' ...Tue, 21 Apr 2009 18:34:25 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic701978-148-1.aspxI 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.Tue, 21 Apr 2009 18:19:59 GMTCarol Adams