Post reply

SSIS

  • July 18, 2011 at 1:45 pm

    #244468

    Hello,

    We are migrating from 2000 to 2008. We have written an SSIS package which will export data from an table to a flat file. But now what we want to do is export it only for a specific condition.

    EG .. SELECT * FROM EMP WHERE DEPT=10.

    Please advice how can this be done?

    thanks,

    aman

  • July 18, 2011 at 1:51 pm

    #1354475

    In the OLEDB source you can use a query instead of a tablefeed. Do so, and put your query there.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 18, 2011 at 2:11 pm

    #1354486

    Hey, thanks for the reply.

    The issue is that dept is dynamic, the user can pass value for deptno. depends on what user passes we need to get data for that dept.

  • July 18, 2011 at 2:22 pm

    #1354495

    How are you calling the package?

    You could call it with DTEXEC and use the SET option to dynamically set a variable within your package.

  • July 18, 2011 at 2:33 pm

    #1354504

    hey do u have an example for the same how to do it I am new to ssis pls

  • July 18, 2011 at 2:43 pm

    #1354509

    Some examples below.

    First create the variable in your package and have your select statement reference the variable. then look at using DTEXEC to set the variable at runtime.

    http://weblogs.asp.net/rrobbins/archive/2009/01/15/ssis-package-user-variables.aspx

    http://consultingblogs.emc.com/jamiethomson/archive/2004/12/13/SSIS_3A00_-Passing-parameters-on-the-command-line-to-an-SSIS-package.aspx

    http://www.sqldev.org/sql-server-integration-services/how-to-pass-variablesparameters-in-xpcmdshell-dtexec-to-an-ssis-2996.shtml

  • July 18, 2011 at 3:10 pm

    #1354524

    Hi I have follwed from the first link provided by you.

    The third step is to add this variable to the package configuration so you can easily change the value in an XML configuration file.

    I cannot see the edit button not sure what to do.

    please advice?

  • July 18, 2011 at 3:19 pm

    #1354529

    The third step is not applicable to using DTEXEC. The links were not intended to be a step- by step solution, just some building blocks you can use as you create your solution. The XML configuration file could be a an alternate method to using DTEXEC.

    If you can run your package successfully by manually manipulating the variable in the package next then look at calling your package from DTEXEC using the SET option.

    The next two links will help with DTEXEC.

  • July 18, 2011 at 3:21 pm

    #1354531

    but can we pass the value in the variable dynamically?

  • July 18, 2011 at 3:33 pm

    #1354535

    amanmeet.mehta 46966 (7/18/2011)

    but can we pass the value in the variable dynamically?

    Yes, using xp_cmdshell and dtexec.exe. the /set option will allow you to set a package variable, which can then be used in the package as a parameter to the SQL Query in your OLEDB source.

    Otherwise... no, not really. SSIS wasn't really meant to be modified by user detection. You might look into the BCP utility.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 18, 2011 at 3:34 pm

    #1354536

    Here you pass a parameter to a stored procedure. The procedure then executes the package using the current value.

    The variable in the package is called ID.

    CREATE PROCEDURE [dbo].[uspExport]

    ID decimal (10,0)

    AS

    DECLARE @Package varchar(170)

    DECLARE @PackageString varchar(200)

    DECLARE @PackageStatus int--The success/failure status of the order export package

    --Identify the location of the package

    SET @Package = '\\server\Export\Export.dtsx'

    --create the "dtexec" SQL string from dynamic values

    --Pass in the @ID parameter to the package

    set @PackageString = 'dtexec /f ' + @Package + ' /Set \package.variables[ID].Value;' + CONVERT(varchar (10),@ID)

    PRINT @PackageString

    --now execute dynamic SQL by using EXEC.

    EXEC @PackageStatus = xp_cmdshell @PackageString

    GO

  • July 18, 2011 at 4:18 pm

    #1354558

    I may be wrong but I have used the below logic

    set @PackageString = 'dtexec /f ' + @Package + 'SELECT * FROM ENTITYHD WHERE ENTITYID = '+' /Set \package.variables[ID].Value;' + CONVERT(varchar (10),@ID)

    please correct me if wrong.

    how do i used dtexec , have never used it before

  • July 18, 2011 at 4:28 pm

    #1354561

    Read this http://msdn.microsoft.com/en-us/library/ms162810.aspx

    Can you use xp_cmdshell?

    Where is you package saved as a file or on the server?

    What is your variable in the package called?

    Whats the datatype of the EntityID field in the table EntityID?

    Can you manually edit the value of the variable in BIDS and then can you run the package and get dynamic results?

    Probably something like this

    set @PackageString = 'dtexec /D MyPackage ' + = '+' /Set \package.variables[EntityID].Value;' + CONVERT(varchar (10),@ID)

Viewing 13 posts - 1 through 12 (of 12 total)

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