ssis deployment

Question

Hi,

I have 10 lower enviroment and every package connected with linked server database, so whenever I deployed the package I need to change the database name.

Now the requirement is I need to remove hardcoded database name from the ssis package. so that whenever any deployment happen at that time I don't need to change the database name.

Can someone help me on this.

Answer 1

Hi @deepika omer ,

You probably using 4 part <server>.<database>.<schema>.<table> way while using linked servers in your queries.

It is possible to switch to the following format:

SELECT *  
FROM OPENQUERY ( LINKED_SERVER, 'select * from PERSONNEL');  

This way database name is not mentioned. You just need to keep linked server names consistent across all the environments. Additionally, you will gain some performance benefits.

Answer 2

Hm. OPENQUERY presumes that the linked server has been set up with the default database to be desired database. Else you have to specify the database in the query inside OPENQUERY.

This is certainly a workable solution as a linked server is essentially an alias. But it does require server-level configuration, which involves someone with server-level permissions. Which can add complexity. Or be perfectly trivial.

In any case, it does not address issues with database.dbo.tbl, that is cross-database queries on the same instance.

I would say that from a T-SQL perspective, synonyms are better. You can say:

CREATE SYNONYM dbo.myremotetable FOR LINKEDSERVER.db.dbo.tbl
CREATE SYNONYM dbo.otherdbtable FOR db.dbo.tbl

There is still an issue with setting up the synonyms, which you may have to do dynamically for each downlevel environment.

It is possible that there is a better solution inside SSIS, but I don't know SSIS, so I cannot answer that part.

of

Answer 3

Hi @deepika omer ,

Try to parameterize the Server Name and Initial Catalog (database name) in a connection string for packages in a project.

Details you may refer:

parameterizing-database-connection-sql-server-integration-services

Regards，

Zoe

---

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

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October