SQL SERVER: Execute SSIS Package Using Store Procedure

Originally, I used 'Microsoft.SqlServer.Dts.Runtime'to execute local SSIS package under asp.net Web application. Under client's environment, SQL Server and web application are hosted on different Windows servers. Now, I use store procedure to execute SSIS package placed in DB server.

 
set @params = '/conn \"a\";"\"' + @a+ '\"" /set \package.variables[b].Value;"\"' + @b+ '\""'
set @cmd = 'C:\dtexec.exe /f ' + @PackagePath + ' ' + @params
EXEC xp_cmdshell @cmd

Dont forget to enable 'xp_cmdshell' option first!

I had to used 32 bit version of dtexec.exe as my package need OLEDB provider Jet 4.0. The provider is not available under 32 bit environment. As a result, I use the x86 version to handle this issue.

Errors when executing SQL Server SSIS Package From Stored Procedure
How to Call SSIS Package from the Stored Procedure

SSIS: DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

I create an SSIS project using SQL Server Business Intelligence Development Studio under Windows Server 2008 R2. When I run the project, something is wrong and an error message is shown below:

"[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER."

I don't get any idea from above message. After I disable the Run64BitRunTime option, everything work fine.

SQL Server: TCP Connection Opening

In order to remote access a SQL Server, I need to open a TCP port for listening.There are several steps are needed to prepared.

(1) Go to "Sql Server Configuration Manager" and expand "SQL Server Network Configuration"

(2) Click the designated instance "SQLEXPRESS"

(3) Right Click "TCP/IP" and then select "Enable"

(4) Right Click "TCP/IP" and then select "Properties"

(5) Select tab "IP Address"

(6) Under "IPALL", enter port "1433" in "TCP Port"

(7) Click button "OK" 

(8) Under "SQL Server Services", right click the designated instance and then select "Restart"

SQL Server: Add a Read-Only Account using SQL authentication

I need to create a read-only account using SQL authentication to login SQL Server. There are several steps are needed to prepared and they are shown below:

1. Ensure the authentication of SQL Server

    (1) Right click the SQL Server and select "Properties"

    (2) Under page "Security", click "SQL Server and Windows Authentication mode" and then click button "OK"

2. Create a read-only account

    (1) Right click folder "Security" and select create a new login

    (2) Under page "General", type login name, password for SQL authentication and default database

    (3) Under page "User Mapping", click the designated database and set db_datareader in its role membership

    (4) Click button "OK"

SQL Server: Connection to target machine could not be made in a timely fashion

I cannot start SQL Server Configuration Manager suddenly and a message box is shown below:

The thing I need :  restart the computer or SQL service. 


When I update the account password which used to run this service, this error will be occurred. Then, go to SQL Server Configuration Manager and update password of the account. After restart the service, everything works fine.