Share via

Using Powershell run SSIS Package(.dtsx file)

Jerry_2424 0 Reputation points
2024-02-07T07:31:30.2633333+00:00

I am trying below code(in .dtsx) :- Clear-Host # Define the path to the 32-bit dtexec.exe $dtexecPath = "C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\dtexec.exe" # Define the path to the SSIS package (.dtsx file) $packagePath = "E:\Practice_SSIS\All_Package\Demo\Demo\Demo\Demo\Demo.dtsx" # Build the command to execute the SSIS package $command = "& '$dtexecPath' '/FILE $packagePath /SET \Package.Variables[User::Variable1].Properties[Value];"1" /SET \Package.Variables[User::Variable2].Properties[ConnectionString];"Data Source=Demo;Initial Catalog=Demo;Integrated Security=SSPI;Auto Translate=True;" ' "  # Execute the command Invoke-Expression $command | Out-Null

How can I connect SSMS server with this code it is possible to make connection for one or more server?? Output :-- Initial : The term 'Initial' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,462 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,396 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 41,006 Reputation points
    2024-02-07T07:44:31.19+00:00

    s[ConnectionString];"Data Source=Demo;Initial Catalog=Demo;Integrated Security=SSPI;Auto Translate=True;"

    If you want to define a string within a string, then you have to quote the quotation marks; you can already see it on the syntax higligthning that it is wrong: User's image

    $command = "...
  2. ZoeHui-MSFT 33,386 Reputation points
    2024-02-08T01:39:22.5366667+00:00

    Hi @Jerry_2424,

    Not familiar with Powershell, you may take a reference to this thread. Regards， Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.