SQL Server Performance

execute ssis in sql

Discussion in 'SQL Server 2005 Integration Services' started by fmardani, Jul 30, 2007.

  1. fmardani New Member

    Hi,
    Using SSIS, how is it possible to execute another ssis package and pass a parameter to it?
    For example, I would like to have a sql code that runs the package say "d:sysapplCEMSSISCSA.dtsx" and pass a parameter of fileName because the CSA.dtsx package requires a filename.
    Something like:
    execute "d:sysapplCEMSSISCSA.dtsx", varfileName
    Thanks
  2. satya Moderator

    Nigel's tip should get you what you want.
  3. dineshasanka Moderator

  4. fmardani New Member

    This is what I am running now:
    Please see the error at the bottom.declare @returncode int
    declare
    @cmd varchar(1000)declare
    @FileName varchar(500)set
    @FileName = 'EqRonnieHK.csv'set
    @cmd = 'dtexec.exe /f "d:sysapplCEMSSISImportsTradesCounterPartyExposureEquitiesImport.dtsx' + @FileName + '"'exec
    @returncode = master..xp_cmdshell @cmd
    Could not load package "d:sysapplCEMSSISImportsTradesCounterPartyExposureEquitiesImport.dtsxEqRonnieHK.csv" because of error 0xC0011002.
    Description: Failed to open package file "d:sysapplCEMSSISImportsTradesCounterPartyExposureEquitiesImport.dtsxEqRonnieHK.csv" due to error 0x80070003 "The system cannot find the path specified.". This happens when loading a package and the file ca
    nnot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
  5. satya Moderator

    Have you checked whether the specified path exists and the calling account do have relevant privileges to access it.
    You might try mapping the same share before calling the package.
  6. dineshasanka Moderator

    this is the syntax for it,
    dtexec /f "d:sysapplCEMSSISImportsTradesCounterPartyExposureEquitiesImport.dtsx" /set Package.Variables[User::File].Properties[Value];@FileName
  7. fmardani New Member

    Hi,
    Not sure exactly where to place your code in my sql.
    The variable is called @FileName which is used in the ssis package.
    This is what I have so far:set
    @FileName = 'EqRonnieHK.csv'set
    @cmd = 'dtexec.exe /f "d:sysapplCEMSSISImportsTradesCounterPartyExposureEquitiesImport.dtsx' + @FileName + '"'dtexec
    /f "d:sysapplCEMSSISImportsTradesCounterPartyExposureEquitiesImport.dtsx" /set Package.Variables[User::File].Properties[Value];@FileNameexec
    @returncode = master..xp_cmdshell @cmd
  8. dineshasanka Moderator

    Try this
    set @cmd = 'dtexec /f "d:sysapplCEMSSISImportsTradesCounterPartyExposureEquitiesImport.dtsx" /set Package.Variables[User::File].Properties[Value];EqRonnieHK.csv'
  9. fmardani New Member

    The variable is called @FileName which is used in the ssis package.
    Should [User::File] be changed to [User::FileName] ?
    And how does the system know where the file is coming from i.e. path?
  10. dineshasanka Moderator

    Yes it should be FileName (Sorry about it)
    you need to include the the path to filename (C:aaaaaaFilename.cscv')
    or else you can give path in another variable and pass it
  11. fmardani New Member

    What do you think about this?
    The error is: at the bottom. Thanks
    Please note this package runs fine if it is run manually.declare @returncode int
    declare
    @cmd varchar(1000)declare
    @FileName varchar(500)set
    @FileName = 'd:ApplDataCEMWorkingTempEquitiesEqRonnieHK.csv'set
    @cmd = 'dtexec /f "d:sysapplCEMSSISImportsTradesCounterPartyExposureEquitiesImport.dtsx" /set Package.Variables[User::FileName].Properties[Value];' + @FileName + '"'exec
    @returncode = master..xp_cmdshell @cmd
    Microsoft (R) SQL Server Execute Package Utility
    Version 9.00.3042.00 for 32-bit
    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
    NULL
    Started: 15:17:35
    Progress: 2007-07-30 15:17:36.15
    Source: Data Flow Task
    Validating: 0% complete
    End Progress
    Progress: 2007-07-30 15:17:36.15
    Source: Data Flow Task
    Validating: 33% complete
    End Progress
    Warning: 2007-07-30 15:17:36.20
    Code: 0x800470C8
    Source: Data Flow Task OLE DB Destination [848]
    Description: The external metadata column collection is out of synchronization with the data source columns. The column "SYS@DATE" needs to be added to the external metadata column collection.
    The column "F_D2" needs to be added to the external metadata column collection.
    The column "TP_CNTRPLB" needs to be added to the external metadata column collection.
    The column "NB" needs to be added to the external metadata column collection.
    The column "NB_ORG" needs to be added to the external metadata column collection.
    The column "NB_EXT" needs to be added to the external metadata column collection.
    The column "TP_DTETRN" needs to be added to the external metadata column collection.
    The column "TRN_GRP" needs to be added to the external metadata column collection.
    The column "F_BS" needs to be added to the external metadata column collection.
    The column "F_CMLDIR0" needs to be added to the external metadata column collection.
    The column "INDEX0" needs to be added to the external metadata column collection.
    The column "TP_CMLQC0" needs to be added to the external metadata column collection.
    The column "TP_CMLQU0" needs to be added to the external metadata column collection.
    The column "F_CMLDIR1" needs to be added to the external metadata column collection.
    The column "INDEX1" needs to be added to the external metadata column collection.
    The column "WAP" needs to be added to the external metadata column collection.
    The column "TP_CMLQC1" needs to be added to the external metadata column collection.
    The column "TP_CMLQU1" needs to be added to the external metadata column collection.
    The column "TP_RTDCC02" needs to be added to the external metadata column collection.
    The column "F_CE" needs to be added to the external metadata column collection.
    The column "NOM" needs to be added to the external metadata column collection.
    The column "TP_CMLDU0" needs to be added to the external metadata column collection.
    The column "FMV" needs to be added to the external metadata column collection.
    The column "TP_PFOLIO" needs to be added to the external metadata column collection.
    The column "RXMCODE" needs to be added to the external metadata column collection.
    The column "MAGR_FLAG" needs to be added to the external metadata column collection.
    The column "COUNT" needs to be added to the external metadata column collection.
    The column "TP_CNTRP" needs to be added to the external metadata column collection.
    End Warning
    Progress: 2007-07-30 15:17:36.20
    Source: Data Flow Task
    Validating: 66% complete
    End Progress
    Progress: 2007-07-30 15:17:36.21
    Source: Data Flow Task
    Validating: 100% complete
    End Progress
    Warning: 2007-07-30 15:17:36.32
    Code: 0x8001C004
    Source: Iterate through Files
    Description: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.
    End Warning
    DTExec: The package execution returned DTSER_SUCCESS (0).
    Started: 15:17:35
    Finished: 15:17:36
    Elapsed: 0.563 seconds
    NULL
  12. satya Moderator

    Are you saying it is failing when executed as a job?
    If so check the permissions to the SQLserver account in this case, as explained previously.
  13. fmardani New Member

    I have now made the ssis package simpler by removing the variable and just have a flatfile connection called InputFileName.
    Not sure if I still require the variable in the lines below since I now have a flatfile connection which I would like to pass the file name to.
    Thanksdeclare @returncode int
    declare
    @cmd varchar(1000)declare
    @FileName varchar(500)set
    @FileName = 'd:ApplDataCEMWorkingTempStaticSBUconversion.csv'
    set @cmd = 'dtexec /f "d:sysapplCEMSSISStaticSBUconversion.dtsx" /set Package.Variables[User::File].Properties[Value];' + @FileName + '"'exec
    @returncode = master..xp_cmdshell @cmd
  14. fmardani New Member

    I have this now:declare @returncode int
    declare
    @cmd varchar(1000)declare
    @FileName varchar(500)set
    @FileName = 'd:ApplDataCEMWorkingTempEquitiesEqRonnieHK.csv'set
    @cmd = 'dtexec /f "d:sysapplCEMSSISImportsTradesCounterPartyExposureEquitiesImport.dtsx" /set Package.Variables[User::FileName].Properties[Value];EqRonnieHK.csv'exec
    @returncode = master..xp_cmdshell @cmd
    error is:
    DTExec: Could not set Package.Variables[User::File].Properties[Value] value to EqRonnieHK.csv.
  15. dineshasanka Moderator

    set @FileName = 'd:ApplDataCEMWorkingTempEquitiesEqRonnieHK.csv'set
    @cmd = 'dtexec /f "d:sysapplCEMSSISImportsTradesCounterPartyExposureEquitiesImport.dtsx" /set Package.Variables[User::FileName].Properties[Value];'+ @FileName
    What is the datatype of the FileName Variable
  16. fmardani New Member

    At present there is no variable. There is only a flat file connection manager.
  17. dineshasanka Moderator

    Then it should be this istead of variable
    Package.Connections[ConnectionManagerName].Properties[ConnectionString]

Share This Page