can we declare variables in Dataflow ?

• Steve Dell

  SSCarpal Tunnel

  Points: 4623

  More actions

  March 21, 2011 at 8:07 am

  #248519

  Hi I am using the following script to run on my source server and i am not able to capture the columns in destination ,So can we declare variables in dataflow task? if so can some one guide me with the following script.

  Script

  DECLARE @GetInstances TABLE

  (Value VARCHAR(30), InstanceNames VARCHAR(20), Data VARCHAR(30))

  INSERT INTO @GetInstances

  EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

  @value_name = 'InstalledInstances'

  SELECT CAST(serverproperty('ComputerNamePhysicalNetBIOS')AS VARCHAR(10)) AS ServerName,

  CASE

  WHEN InstanceNames = 'MSSQLSERVER'

  THEN CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(10))

  ELSE (CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(10))+'\'+ InstanceNames)

  END AS InstanceName from @GetInstances

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 22, 2011 at 5:10 am

  #1301793

  I think I'd do it like this:

  (1) Create an Execute SQL task that dumps the results of xp_regread into a temp table. (You may wish to experiment with the RetainSameConnection property of the connection manager before deciding whether to use a local or a global temp table)

  (2) In your data flow, choose SQL Command from the Data access mode drop-down menu, and put your SELECT statement in there, substituting the name of your temp table for @GetInstances

  Good luck - please let us know how you get on.

  John

• Pouliot Philippe

  SSCommitted

  Points: 1912

  More actions

  March 22, 2011 at 8:22 am

  #1301900

  Data flow need metadata to work. If #temp table doesn't work try with a normal table

• Steve Dell

  SSCarpal Tunnel

  Points: 4623

  More actions

  March 30, 2011 at 11:53 am

  #1305592

  I Have even attempted by replacing data flow with execute sql Task and tried to insert the data to table in one server but i was getting error as below can some one help me in approaching my task ????? Thanks in advance

  [Execute SQL Task] Error: Executing the query "DECLARE @GetInstances TABLE

  (Value VARCHAR(30), In..." failed with the following error: "EXECUTE cannot be used as a source when inserting into a table variable.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 31, 2011 at 1:48 am

  #1305831

  Temp table is necessary, not table variable. A table variable only lives for as long as the batch that creates it.

  John

• sam 55243

  SSCrazy

  Points: 2697

  More actions

  March 31, 2011 at 2:46 am

  #1305849

  Hi,

  You have to use temp table as table variable will not work in your case.

  First use a Execute SQL Task write you script here

  SCRIPT

  CREATE TABLE #GetInstances

  (Value VARCHAR(30), InstanceNames VARCHAR(20), Data VARCHAR(30))

  INSERT INTO #GetInstances

  EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

  @value_name = 'InstalledInstances'

  Next Use a DataFlow Task but before that set RetainSameconnection to true in your connection manager.Add a OLEDB Source set ValidateExternalMetaData properties to false , thn use your query

  SELECT CAST(serverproperty('ComputerNamePhysicalNetBIOS')AS VARCHAR(10)) AS ServerName,

  CASE

  WHEN InstanceNames = 'MSSQLSERVER'

  THEN CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(10))

  ELSE (CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(10))+'\'+ InstanceNames)

  END AS InstanceName from #GetInstances

  If you want to use columns downstream then you have to manually add those in Input/Output Properties of OLEDB Source advanced properties.

  Thanks

  Sam

• Steve Dell

  SSCarpal Tunnel

  Points: 4623

  More actions

  March 31, 2011 at 9:19 am

  #1306047

  I did the following steps but its failing stating that invalid object #GetInstances in the dataflow task while configuring Source ODBC connection using SQL Command ..Any help over here ..???? Thanks in advance...

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 31, 2011 at 9:27 am

  #1306053

  As I said in an earlier comment, and as Sam said, check the RetainSameConnection property of the task. If that doesn't work, use a global temp table (but don't forget to drop it when done) or a permanent table. Beware of concurrency issues if you end up having to do that.

  John

• sam 55243

  SSCrazy

  Points: 2697

  More actions

  March 31, 2011 at 10:30 am

  #1306096

  Try the following step:

  Select your DF task in Control Flow go to properties set DelayValidation to 'True'

  Select Your OLEDB Source Component From Properties window select AccessMode to Sql Command

  Set Your SqlCommand to the required sql that you want.

  Also set ValidateExternalMetadata to False

  PS: Do the above steps for OLEDB Source Component in properties window only don't edit it.

  Thanks

  Sam

• Steve Dell

  SSCarpal Tunnel

  Points: 4623

  More actions

  March 31, 2011 at 11:04 am

  #1306117

  Hi Sam I tried doing like what you said as below but no luck any other process or query i can try to get instances running on a machine and insert into table ...as i need to loop to all machines list i have in a table and get the instance names for each host names... Thanks in advance..

  My Steps:

  -->Execute SQL Task :ResultSet-None,Connection:local host ,RetainSameconnection to true

  Query:

  CREATE TABLE #GetInstances

  (Value VARCHAR(30), InstanceNames VARCHAR(20), Data VARCHAR(30))

  INSERT INTO #GetInstances

  EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

  @value_name = 'InstalledInstances'

  -->DF:DelayValidation to 'True', ODBC Source :-ValidateExternalMetaData properties to false

  Getting error while placing the Query as below as Invalid Object #GetInstances

  Query:

  SELECT CAST(serverproperty('ComputerNamePhysicalNetBIOS')AS VARCHAR(10)) AS ServerName,

  CASE

  WHEN InstanceNames = 'MSSQLSERVER'

  THEN CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(10))

  ELSE (CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(10))+'\'+ InstanceNames)

  END AS InstanceName from #GetInstances

• sam 55243

  SSCrazy

  Points: 2697

  More actions

  March 31, 2011 at 11:30 am

  #1306133

  Please try to place ur Query for OLEDB source from properties windows itself. Don't try to edit the OLEDB source in this way you will by pass the error that your are getting.

  I created a snapshot of what i'm trying to say please have look into it..

  [/url]

  Then if you want to use those columns (ServerName,InstanceName) downstream u need to dd those manually in Input/Output properties of ur OLEDB source

• sam 55243

  SSCrazy

  Points: 2697

  More actions

  March 31, 2011 at 11:36 am

  #1306135

  x

• Steve Dell

  SSCarpal Tunnel

  Points: 4623

  More actions

  March 31, 2011 at 1:12 pm

  #1306171

  Hi Sam !

  Could you also please share snapshot of Input/OutPut properties configuration of advanced editor ??? As i was not able to configure them correctly ...

  Thanks a lot

• sam 55243

  SSCrazy

  Points: 2697

  More actions

  March 31, 2011 at 11:38 pm

  #1306292

  Hi,

  Please find the snap shot for input/output properties of OLEDB source after you configure the SQL query

  http://www.flickr.com/photos/48301078@N03/5578387805/

  You need to add your column in both External and Output columns thru Add Column button

  Thanks

  Sam

• Steve Dell

  SSCarpal Tunnel

  Points: 4623

  More actions

  April 1, 2011 at 9:08 am

  #1306551

  Thanks Sam It's Working 🙂