January 9, 2013 at 8:06 am
Hi guys,
I have 8 packages which currently have variables and I would like to convert them to XML configs as the values will change between DEV, TEST and PROD
Some values are shared across all packages (e.g. OLEDB connection strings) and some are package specific.
Some need to change as they move through the environments and some don't
I have one package where the variable needs to be passed in at run time.
I know I can share an XML config across packages, but can I have a package pick up more than one XML file? I.e. a global XML with the connection strings and a package specific XML with only the variables it needs. If not, how do I differentiate between the same variable name in different packages in a single XML config.
Can I have one XML file for more than one environment so that depending on the environment it will use a different set of values or do I need to have separate XML files for each environment.
Is there any way to pass a value to SSIS at run time (in my case a batch ID) from either a stored procedure (I can't use DTSEXEC because xp_cmdShell is not availabe in the surface area configuration) or from a SQL Agent job.
any help would be appreciated
January 9, 2013 at 8:47 am
aaron.reese (1/9/2013)
I know I can share an XML config across packages, but can I have a package pick up more than one XML file? I.e. a global XML with the connection strings and a package specific XML with only the variables it needs.
Sure. In the Package Configurations dialog you can add as many XML config files as you need. This is exactly what I do except that I store my connection strings one per file. This way, if a package needs 3 of the 4 connections in my environment I do not receive any grief from SSIS about referencing a config file with 4 connection strings but only 3 connection managers.
If I were doing this I would start with one file per connection string and one file per package with the package-specific variables in them, e.g. for variables that belong only to that package. If you want to bundle common variables into a file and reference that in all multiple packages that would work too but I never had that much overlap in package-specific variables to where I needed that.
Is there any way to pass a value to SSIS at run time (in my case a batch ID) from either a stored procedure (I can't use DTSEXEC because xp_cmdShell is not availabe in the surface area configuration) or from a SQL Agent job.
In SQL Server 2012 yes via the stored procs in SSISDB. In 2008 R2 and below, not that I know of. dtexec.exe is how you would call it, passing in your variable values on the command line. Even SSIS step types in SQL Agent do it this way. Now that I mention it, I guess you could create a new Agent Job on the fly, execute the job and then drop the job when it completes. I would categorize that as a workaround but I know of people doing stuff like that to workaround the lack of xp_cmdshell in their environment. You would still be calling dtexec.exe at the end of the day and might have some trouble with the mechanics of starting the job and polling to wait for it to complete, but it has worked for others.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 9, 2013 at 9:14 am
I have thought of a solution to the variable pass through - which is a horrible workaround...
Write the value that I want to pass through to a table. Take a lock on the table - to prevent anyone updating whilst I am running, call the SQL Agent job which calls the SSIS package to start. The SSIS package reads the variable value from the locked table (dirty read with NOLOCK???) when the package finishes, release the lock and delete the value form the table.
January 9, 2013 at 9:51 am
For that you could leverage Package Configurations SQL Server storage. Not sure about why you think you need to lock the row but maybe that's specfic to your environment. That could get nasty in a hurry. If you do that I would recommend padding the rows in your config table so that only one row fits on a page.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply