Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Making the source dynamic

Answered Making the source dynamic

  • Thursday, May 17, 2012 7:44 PM
     
     
    Sign In to Vote
    0

    We have requirement to move the data from Oracle to Sql server.

    Inside the package we have a dataflowtask to pull the data from oracle (development server) and will be loaded to SQL server. This works fine when i run on development server.

    Please let me know how to make the oracle source dynamic so that it will be pointed towards the oracle production server when i run the package on Prod.

    Thanks

     

All Replies

  • Thursday, May 17, 2012 7:51 PM
    Moderator
     
     Answered
    Sign In to Vote
    0
    It is very easy to introduce package configurations which brings tremendous flexibility to your packages. With a package configuration you can edit your package properties, variables, connections and the properties of your control flow tasks (termed executables) at run-time. Note that you cannot edit the properties of your data flow components.
    • On the menu bar, point to SSIS, Package Configurations… or right-click on the control flow design surface and select Package Configurations….
    • In the Package Configurations Organizer click Enable package configurations and click Add…
    • Click through the welcome screen and in the Configuration Type combo select XML Configuration File.
    • In the space for Configuration file name type C:\PackageConfigurations\Environment.dtsConfig and click Next
    • In the object tree browse to Connections.Destination.Properties and check the InitialCatalog & ServerName properties. Click Next
    • Give your configuration a name and click Finish

    Arthur My Blog

    • Marked As Answer by SSISNewbie345 Thursday, May 17, 2012 9:40 PM
    •  
     
  • Thursday, May 17, 2012 7:58 PM
     
     
    Sign In to Vote
    0

    This is an existing package which we are modifying....

    we are using the package configurations as Environment variables and variables. I have actually created a variable @varsource = select * from Development

    and updated the @varSource = select * from Prod at table level in the Prod.

    I have return an expression @data flow level. Let me know where to write the expression and which property to use.

    Thanks alot ....


     
  • Thursday, May 17, 2012 9:21 PM
     
     
    Sign In to Vote
    0

    If I understood your question correctly, this might help you:

    You can use Host_Name for your server name. 

    DROP an Execute SQL task to the control flow, and then Create a variable, say, User::SrvrNm

    Now In the Exec SQL task use the following code:

    DECLARE @SrvrName VARCHAR(100)

    SELECT @SrvrName = Host_Name()

    SET ? = @SrvrName 

    Under the variable mapping of Exec Sql task, map it as Output to the SSIS variable SrvrNm

    Now, create another SSIS variable and say User::SrcQry (set evaluate as expression to True)

    Now under the expression set it to: 

    "SELECT * FROM " + <Drag the SrvrNm variable here> + "DbNm.SchemaNm.TableNm "

    This should solve your problem.

    Also, remember to set a dummy value, since, DFTs need to be mapped upfront.

    However, like ArthurZ mentioned above, your best bet/way would be to use Config files.

    Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.