Post reply

can we declare variables in Dataflow ?

  • 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

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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...

  • 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

  • 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

  • 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

  • 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

  • March 31, 2011 at 11:36 am

    #1306135

    x

  • 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

  • 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

  • April 1, 2011 at 9:08 am

    #1306551

    Thanks Sam It's Working 🙂

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply