Sql-server – Can SSIS packages call other SSIS packages in the connection string

connection-stringpasswordsql-server-2008ssis

I am running an SSIS package that transfers data from one (SQL2008) server to another (SQL2000). However after P2V conversion the SQL2008 server cannot execute an SSIS package due to a user authentication error.

Lets say the package is called "Transfer-Go". In the connection string of that package can it call another SSIS package? In the SSIS library there is another package with the same that appears in the string (called Transfer-Now) name The string is below:

Data Source=<IP>;User ID=<user>;Initial Catalog=<db_name>;Provider=SQLNCLI10.1;Persist Security Info=True;OLE DB Services=-13;Auto Translate=False;Application Name=SSIS-<Transfer-Now-name of other SSIS package>-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}<IP.db_name.user>;

So is this SSIS package connection string calling that package?

And if the credentials are wrong in that package could that be why I am unable to authenticate?

Thanks for any input, not a DB/SQL guy at all so I apologize if I sound green here.

Best Answer

The connection string is not calling another SSIS Package. The property Application Name is optional filler text so you can differentiate one connection from another.

Application Name=SSIS-<Transfer-Now-name of other SSIS package>-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}<IP.db_name.user>

If Transfer-Go has the a reference to Transfer-Now in the connection string, the probable cause is that the developer created Transfer-Now and then copy/pasted either the package in whole or at least the Connection Manager between the packages.

The mechanisms for one package to run another package are

  • Execute Package Task {either as an in-process (synchronous) or out of process (asynchronous)}
  • Execute SQL Task
  • Execute Process Task

The later two options will have you either running a predefined Agent Job or building out the appropriate commands to dtexec

Related Question