December 17, 2018 at 12:08 pm
Hello,
I created a package which extracts data form one table and loads into another.
For source I'm using SQL Command
Select col1, col2 from TEST.TABLE1
Is there a way to dynamically change TEST in one place and it changes to all the places? because my solution will have many SQL Command, and I do not want to manually change TEST with PROD, when I'm ready to migrate the code.?
Thnaks
December 17, 2018 at 12:35 pm
We use an XML Configuration File in our packages to set the database server and name. There’s probably a better way if you can use the project deployment model, but we are on SQL Server 2008. The XML configuration file is read when package execution begins, before SQL Server configurations.
The XML file is generated for you when you create the configuration using SSIS | Package configurations menu.
The XML file name is configured as e.g.: SQL_DB.dtsConfig. I keep two copies of the file, one configured for production, another for testing, e.g.:
SQL_DB.dtsConfig_prod
SQL_DB.dtsConfig_test.
To switch databases, one or the other is copied over SQL_DB.dtsConfig before executing the package.
December 17, 2018 at 12:42 pm
Sorry, I'm new to SSIS, where is this XML file?
December 17, 2018 at 1:06 pm
You select the location when you create the configuration. Configurations are tightly bound to package variables and I spent a fair amount of time getting a handle the big picture. Happily, there is lot of info on them. If you google
ssis package configurations
you'll find tutorials and videos that should help you get started.
December 17, 2018 at 1:20 pm
irehman - Monday, December 17, 2018 12:08 PMHello,
I created a package which extracts data form one table and loads into another.
For source I'm using SQL Command
Select col1, col2 from TEST.TABLE1Is there a way to dynamically change TEST in one place and it changes to all the places? because my solution will have many SQL Command, and I do not want to manually change TEST with PROD, when I'm ready to migrate the code.?
Thnaks
Sounds like you are working in an environment where production and development databases coexist on the same SQL instance. I urge you to get that changed, as soon as you can. Otherwise, it's only a matter of time before dev and prod start getting mixed up.
Select col1, col2 from TEST.TABLE1
This suggests that you have a schema called 'TEST', rather than a database.
SSIS does not have a component called 'SQL Command'. Did you mean 'OLEDB Source', perhaps?
OLEDB sources use 'Connection Managers' to control where they run. Connection Managers can be have their connection properties overridden at runtime through the use of SSISDB environments and variables. Database names should generally not be hardcoded into your SELECT queries.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 17, 2018 at 1:28 pm
Yes, its OLE DB Source, which has a SQL Command. Both Test and Production are on different server. I'm looking for a solution, so when I developed these bunch of mappings based on SQL Statement, how would I be able to change the Database name because in TEST env database name is TEST and in Production database name is PROD. When I change the connection, it looks for TEST.TABLENAME.
December 17, 2018 at 1:33 pm
irehman - Monday, December 17, 2018 1:28 PMYes, its OLE DB Source, which has a SQL Command. Both Test and Production are on different server. I'm looking for a solution, so when I developed these bunch of mappings based on SQL Statement, how would I be able to change the Database name because in TEST env database name is TEST and in Production database name is PROD. When I change the connection, it looks for TEST.TABLENAME.
I repeat,
TEST.TABLENAME
refers to a table called TABLENAME in a schema called TEST.
Database name is not a part of this query.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 17, 2018 at 4:26 pm
@ Phil
Yes TEST is a schema name not the database
Select .......... from TEST.tablename
I need to change TEST to PROD when I'm ready to move the SSIS package to Production. I do not want to change each occurrence of TEST manually.
December 17, 2018 at 4:36 pm
irehman - Monday, December 17, 2018 4:26 PM@ Phil
Yes TEST is a schema name not the database
Select .......... from TEST.tablenameI need to change TEST to PROD when I'm ready to move the SSIS package to Production. I do not want to change each occurrence of TEST manually.
Schema name isn't a parameter in SSIS. You can do a find and replace if you open the dtsx package in Notepad++ or another text editor.
Most people have the same schemas in production as test environments. Why have are you using different schemas for prod/test?
December 17, 2018 at 4:43 pm
That is the way ERP is setup.
December 18, 2018 at 8:00 am
We create a variable called Environment as a Project Param in SSIS. The values are TEST and PROD. I then create variables for the Server and Database and change them using an expression on the variable.
Then from SQL Agent I configure the Job Steps to Pass the Environment.
This is a little complicated as we also use the catalog and these variables also need to be set-up there.
Works like a charm once you have this set-up, I would document the process but I don't have time, hopefully this will point you in the right direction.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply