How? Sharing Configuration Info Among Packages
Mar 7, 2007
Ok, I understand it is possible, but I still can't quite get the mechanics of it to work.
I create a new BIDS project, and add a package to it, and add 3
connection managers, and set the server, instance, DB, etc. for SQL
connections. I change the name of each connection manager so the server
name is not there, but use something more like a generic name of what
the database is. Fine. I right click in the Control Flow area and go to
configurations. I enable configurations, and save to a common place on
the C drive. Save everything, exit, VS, fine, and I export all
connection managers properties.
Then I start Visual Studio again. Create another new BIDS project, add
a package to it, and add 3 connection managers. I don't actually
connect them, but use those same generic names from the first
iteration. I enable configurations for the package. I am somewhat
expecting to see the connections change from the same-o-default to the
data I used in the first go around.
I am obviously missing something big here.
View 3 Replies
ADVERTISEMENT
Mar 6, 2007
The scenario is an ETL that takes flat file feeds via FTP to move data into varous production SQL server databases nightly.
There are a number of packages involved, and this depends upon the type of data being sent.
There are a set number of servers and databases to receive the
transformed data. I would like to be able to define say 3 servers, and
maybe a couple of databases in each one time in the configuration. For
simplicity lets say 6 databases total. I would like a single point of
maintenance for these 6 locations. I would like all connection managers
in all packages in all solutions to share these 6 settings in all
connection managers. Is this possible? From my initial attempts, it
would appear each package gets its own independent list of connection
managers and which must be configured separately. I don't see how to
share settings, which is really where the power of SSIS configurations
would be.
Similarly, I would like to be able to locate flat files at given paths.
The package may know the name of the file it is looking for, or the
file it will create, and the folder path needs to be computed from a
configured folder root, and for the package connection manager to store
only the name of the file, which never changes.
I can not see how to set something like this up with connection
managers and configurations. Perhaps I still need a highly customized
solution to achieve this, as we did for DTS, where we would only
execute packages using our own executor, which would load the package,
search out all environment specific settings and modify them on the fly
prior to executing the package.
Thanks for any direction you can give here. The books I've read seem to
imply I might be able to do what I need here, but I can't seem to find
the mechanics of making it happen.
View 1 Replies
View Related
Mar 26, 2007
It is pretty easy to create the package configuration to the shared, never-changing path to XML file. But to get this to work, I have to add a bunch of package variables which are driven from the configuration. I then use expressions to compute actual paths to source files and packages.
This business of copying a block of variables from one package to another is extremely tedious, and at least to me, something of a maintenance issue. I have found if I get ahead of myself and forget to create the variables first, then Visual Studio is NOT very forgiving, and will complain repeatedly just prior to crashing without the offer to save anything. If I add a new variable, it is like an easter egg hunt. This does NOT feel very single point of maintenance when variables must be used in this way to really effectively apply configuration, and it is so tedious and error prone, and unforgiving. I seem to spend a great percentage of time copying the names and datatypes and values of variables ONE AT A TIME. What is that all about?
I hear people telling the praises of configurations, but I must be missing something.
With DTS I used a system where global scope settings were "injected" into packages as they ran, and I never had to worry about anything, and it was damn hard to goof it.
Please someone, evangelize me!! Are people bypassing VS and using NOTEPAD on the packages or something like that to save time?
View 2 Replies
View Related
Jan 22, 2007
Hello,
I'm using an XML file to configure my dataBase connection string. I try to deploy my package on a new server and it works perfectly.
Then I made a second package which also need a configuration for the dataBase connection string. (I made the connection with the connection manager inside packages). The configuration is the same that for my first package, so I thought to use the same configuration file.
I can use the same configuration file but the problem is when I try to generate a deployment for my solution. I got an error which tell me that the xxx.dtsConfig file already exist and can not be copy again.
When I made the configuration in the second package I said that I want to reuse the file ... and I thought that for the deployment SSIS would know that it has to include that file only once ...
Did somebody already have this problem ?
Thanks !
View 3 Replies
View Related
Jun 22, 2007
Hey guys and girls,
This seems like a no brainer, but it's driving me nuts. I want one XML file for the entire solution. There are multiple packages in the solution which have different Connections in the conntion Managers. The packages share some connection names, and some are unique to the package.
Example:
LoadData.dtsx would have a source database connection named (SourceDB_OLEDB) and a oledb connection (DataWarehouse_OLEDB).
LoadDataMart.dtsx would use the same name for the (DataWarehouse_OLEDB) connection and have another oledb connection (DataMart_OLEDB)
I want one XML config file that has all the connection strings, but the problem is that the LoadDataMart.dtsx will throw an error:
Error 1 Error loading LoadDataMart.dtsx: The connection "SourceDB_OLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found. c:ssisLoadDataMart.dtsx 1 1
Thanks,
James
View 5 Replies
View Related
May 23, 2005
I am executing a DTS package from within a java application using the xp_cmdshell procedure and dtsrun via JDBC.
My question is how do I pass back error info on the DTS package to my app?!
Any help is appreciated!
View 2 Replies
View Related
Sep 1, 2006
Hi!
I want to log package info like when the package starts and ends, and write info to a sql server table. there are of course many ways to do this. I just want some opinions from you if you have some clever ways to do this.
regards geir f
View 1 Replies
View Related
Jan 11, 2007
Hi All
€™m just starting out with SSIS and am trying to establish architecture standards that are sound and workable. We will be developing BI solutions for customers who use our banking and finance package that runs on an IBM System i. Modularity is important as all our BI solutions will have a combination of re-used functionality along with varying degrees of unique functionality for each customer. My current area of interest is in externalizing all environmental factors and ensuring such things as passwords are protected.
I understand how configuration files work and how to use environment variables to provide a layer of indirection to them. At this stage we want to avoid encryption of packages and plan to use file system based deployment. We plan to use windows security for SQL server so I have no issue in exposing connection info here but for our DB2 connections we need to include user id and passwords. As I understand it, configuration files can not be encrypted so how do I store this externally while preserving its security. Nothing I have read so far has given me exactly what I need.
View 1 Replies
View Related
Oct 27, 2006
Hi All,
I'm new to SSIS. I'm trying to build a simple package, run it on dev and migrate it to our stg and tst environments. It works on my dev environment great! It simply loads data from a flat file into a table. The trouble i'm having is exporting the connection string info to an xml file and runnign it on other environments. I am not storing the package on the db. I am saving to a file and running through SQL Server BI Development Studio.
I go to "Configuration File" and export InitialCatalog, ServerName, User, Password to the file. I can manually change the xml to the new environment but don't know how to set the password. It's not in the xml and i am constantly getting "Login failed for 'sa'" errors. Anyone know how to do this. I CAN get this whole process to work if i use Windows authentication security but this isn't an option. I need to use the db security.
Thanks,
Dave
View 4 Replies
View Related
Jul 27, 2006
I would just like to give my feedback on the configuration packages implementation in SSIS:
THIS STUFF SHOULD BE A NO BRAINER AS IT IS A COMMON REQUIREMENT ON ETL PROJECTS.
I have trolled through these forums and found that I am not alone on this issue. Note for SSIS Team: Please bring out a HOWTO on this subject that details all the little work arounds because intuitively used it does not work.
Consider this:
I develop in BIDS on my client and everything runs. I create a config package and I choose only the Name and ConnectionString Properties for each Connection Manager. (Thanks to Jamie for that tip)
I build the app and a deployment utility is created. I navigate to the deployment directory, and deploy. I point to a mapped share on the production server, change all the properties for production and continue until finished.
On the production server I run a script containing:
dtexec /FILE D:SSISSourcecodeSource.dtsx >> D:SSISSourcelogsSource.log
I get an error: Cannot find the configuration file: source.dtsConfig
I tried putting the following in the script:
dtexec /FILE D:SSISSourcecodeSource.dtsx /CONFIGFILE D:SSISSourcecodesource.dtsconfig >> D:SSISSourcelogsSource.log
Still: Cannot find the configuration file: source.dtsConfig
I have to MANUALLY edit the dtsx file to put the path in from the the name of the config file and THEN it works.
Thinking that it might not like a mapped share from desktop to deploy to, I copIED the contents of the depLoyMENT directory on my desktop to a folder on the production server and deploy from there. It should pick up the proper path from there. NOT!!!!
So it was back to editing the dtsx file.
So there you have it. I await all comments with great interest.
I hope this is all fixed in SP2, Because I don't want to waste anymore time on what should be a simple task.
Michael Morrissey.
ETL and BI Developer.
View 9 Replies
View Related
Nov 13, 2007
I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
Any suggestions? Thanks.
T
View 3 Replies
View Related
Jun 1, 2006
I have two packages share same configuration file. There are 4 connections in the first package and 2 connections in the second. Configuration file contains properties for 4 connections. First package opens in VS designer with no error. The problem with second package.
I get error that connection exist in the configuration but not in the package
and
"Could not copy file " Master_Configuration.dtsConfig" to the deployment utility output directory. Master_Configuration.dtsConfig' already exists."
Any help?
View 3 Replies
View Related
Jan 29, 2007
Hi,
At just the point at which I was going to write some verbose schpeel, I found this, which really does it all for me:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1163727&SiteID=1&mode=1
Basically it seems that whilst you can indeed share a config file, it doesn't aggregate sets of say different connection managers, into a superset - you choose to reuse, but it actually overwrites. Any ideas, anyone??
View 4 Replies
View Related
Nov 16, 2006
Hello,
I need some clarification. I am trying to utilize the XML Direct Configuration in my SSIS packages to utilize database connections in the package. I am wanting to utilize this the same way you could use UDLs in the SQL Server 2000 DTS packages.
Currently, I am creating the dtsConfig file and saving it to my desktop. I then modify it with notepad and add the following configuration where it looks like this:
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="Package.Connections[ConnectionName].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=[ServerName];Initial Catalog=[DBName];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Once this is created, I am trying to "re-use" this in SSIS packages created moving forward, where they all point to this configuration for the same database connection.
What I don't understand is when "Enabling Package Configuration" and then pointing to this dtsConfig file doesn't create a connection in "Connection Manager" NOR does it provide a way to create a blank connection and point to this configuration.
I feel like I am missing something thats so "great" about XML configuration files. Any help would be appreciated.
Thanks,
Daniel Lackey
MCSD
View 3 Replies
View Related
Oct 23, 2006
Hello,
We have been conducting some testing regarding package deployment and SQL Server based configuration. It seems there is a problem that was documented in an MS Feedback entry (
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126797
)
I searched for a viable answer, work-around, or hotfix that would address this issue but found none.
The jist is that a package that uses SQL Server based configuration must have an xml-based configuration entry to "re-point" the SQL Server based configuration connection manager to the deployment target server. We cannot use environment variable or registry as this is against internal policy. The problem is that even though the xml based config is specified first in the list of package configurations it does not get applied first at run-time. So, when the package runs from a SQL Server Agent job the package's connection manager for the configuration entries is not updated.
The package runs correctly through BIDS. You can change the connection string in the .dtsConfig file and the SQL Server based package configuration is obtained from the correct source.
Environment is SQL Server Enterprise Edition 64-bit w/ SP1, Windows Server 2003 Enterprise Ed. 64-bit.
Does anyone have any experience with this issue? Know any hotfixes, other work-arounds?
Thanks in advance!
View 9 Replies
View Related
Feb 1, 2007
I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.
Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?
Sachin
View 4 Replies
View Related
Oct 26, 2005
I have 5 packages in a solution.
View 19 Replies
View Related
Apr 16, 2007
Hi,
I am currently migrating from Oracle to MS SQL Server 2005 using SSIS. Since the new schema being used on the SQL Server is very different, we have created separate packages for each target table.
We have several different sets of data stored as separate Oracle schemas, and I would now like to change to a different schema for the OLE DB Source objects in my packages to use (Table/column names to remain the same). While I can go through all packages and search through the drop-down list to select the table of the same name from other Schema, doing so is an extremely slow process.
Is it possible to use configuration files to set a schema to be used for a number of OLE DB Sources over a number of packages?
At the moment I am doing a find and replace over the .dtsx files, but can't believe I have to resort to this for something that I imagine many people would want to do with SSIS.
Thank you,
The Captain
View 3 Replies
View Related
May 3, 2007
Hi,
I've found this problem that when I change settings in my configuration file it does not automatically apply to all child packages which uses the same configuration file if run from a job in SQL Server Agent. I need to open the package and save it again from BIDS. I use one "load group" package to execute all other packages.
Is there a way from the job configuration to set a setting so the package allways will have the newest configuration?
Regards
Simon
View 7 Replies
View Related
Aug 10, 2006
Suppose 2 environments on a single machine.
Each environment has different configuration settings....different
databases etc.
All the packages in the first environment have a hardcoded config files
referencing the local drive.
In order to create the second environment do I have to
go into each package and manually change the location of the
hardcoded config files. If I don't it will it not use the config files
from the first environment.
Thanks
View 1 Replies
View Related
Oct 3, 2007
Hi everybody,
I moved my packages to the new machine, and have problem with parent variables.
When child package tries to get parent variable value I get an error:
Information: 0x40016042 at LoopPackage: The package is attempting to configure from the parent variable "MAIN_SesId".
Warning: 0x80012028 at LoopPackage: Process configuration failed to set the destination at the package path of "Package.Variables[User::MAIN_SesId].Properties[Value]". This occurs when attempting to set the destination property or variable fails. Check the destination property or variable.
Thanks, for any information.
View 3 Replies
View Related
Dec 18, 2007
Hi,
I'm fairly new to the SSIS world, and I've recently ported a bunch of dts packages over to SSIS. I'm an ASP.NET developer so I'm very familiar with the capabilities that configuration files give you, and I attempted to set up my solution as follows:
All of my "Data Sources" are at the project level, and added (with the same name) to each package. I wanted to have a single config file that had all of the project-level settings (i.e. connection strings, data file paths, etc). I then have a config for each package with the package level settings - i.e. variables, etc.
The problem becomes that all packages do not use all data sources. This results in an error when I try to open up a package for editing, it complains that it doesn't have a reference to data source XYZ that it is seeing in the configuration file.
Is there any way that I can get around this? If I have a password to a database change, I don't want to have to look through every config file and change it in multiple places.
View 4 Replies
View Related
Jan 14, 2008
Hi,
I am using custom dll in script component in SSIS package. This dll is looking for some configuration settings and dsplays the message as "Configuration section could not be found in the configuration source" . Please tell me the configuration source it looks for.
View 3 Replies
View Related
May 7, 2007
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
ddave
View 3 Replies
View Related
Sep 17, 2004
Hey guys,
I have a quick question about SQL 2000 server data sharing.
I am currently using SQL 2000 Server Enterprise and I need to do synchronous data sharing with flat files. I have an ODBC connection to the files. I was able to easily share this data by using linking in Microsoft Access, however, for the life of me I cannot figure out how to do the same in SQL. We are in the process of converting legacy code. The old code will of course be communicating with the flat files and the new code will be talking to SQL server. So it is vital that the 2 access methods are both pulling the same data. Does anyone have any ideas???
Help would be greatly appreciated
Thanks
Dax
View 3 Replies
View Related
Jan 21, 2008
hello sir
i have sql sever 2000 installed on my PC which i would like to share on network.
when i am trying to create ODBC connection from other pcs on network i am getting error -- specified sql server not found.
pls help me
gayatri
View 4 Replies
View Related
Jul 11, 2006
Can you use SQL Express remotely instead of locally.
I have been told that I might be able to use SQL Express as my network server for sharing files. Is this possible, or do I need a different version of SQL to do this?
I have been reading the VBE forums and from what I gathered was that SQL Express is only for a local table only, is this true?
If not, what is the work around, and how do I go to another pc to find out if it is sharable?
I would hate to get several months in my app design and learn that I was headed in the wrong direction.
Thanks
David
View 8 Replies
View Related
Feb 6, 2004
How can I share stored procedures so I can call them form different databases ?
Basically, all databases have identical table names and structure but containing different data and I don't want to replicate all stored procedures on every database. There are too many and will also be very unpractical to maintain the code.
Thanks,
Moshe
View 4 Replies
View Related
Jan 2, 2006
Hi :)
I would like to know if it is possible to link two databases together
(in my case ASPNETDB, and another mdf database) so that I can run
queries on those shared tables. For example, I would like to use the
uniqueidentifiers from the ASPNETDB tables in my own tables.
Thank you!
(I do use the latest version of Visual Web Developer).
View 3 Replies
View Related
Apr 17, 2007
I am writing a DB-driven application, using Visual Studio. I have a database that i want to be able to move from one computer to another, and still be able to access it. It's probably something simple, but I must be missing out something.
The problem is that if i choose windows authentication, then the DB cannot be used on another computer. I also had no luck when using SQL Authentication.
How can this be done? What form of authentication should be used and how, so that I can just copy my DB from one computer to another, then log into the DB using a username and password?
*Any* help would be greatly appreciated!
View 3 Replies
View Related
Jul 20, 2005
Hi:I'm trying to setup a MS Cluster but I don't know if it is feasible toconfigure it in the way I think.I have two machines with win2k server and MSSQL-2000 one of them iscurrently performing as the production database and the other one isthe backup. The secondary one is keeping updated via the "LogShipping" technic.We almost covered all the other possible failures of the othercomponents (ie: network, power, application servers, etc), the data ismaintaned in a raid which is ok but we want to cover also thepossibility of that failure too (yes, you can call us paranoids!! ).The thing is we want to create a cluster that do not share the data,but each machine of the cluster have thei own set of the same data.My intention is to configure the cluster to detect a failure of onemachine and initiate the failover to the remaing one using theappropiate scripts related to the promotion of a secondary serverkeeped updateusing "log shipping".Have anyone some experience with that kind of solution ??Thanks in advanceLeonardo
View 2 Replies
View Related
Oct 30, 2007
Hi,
Can we share and schedule whole report folder(not just one report) by using management studio?
Thanks
View 1 Replies
View Related
Sep 22, 2007
I am getting following error when trying to install SQL express 2005 on XPSP2.
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine SIGMA-805539A79 Error:2147944122 (0x800706ba).
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=70342
I tied re-installing WMI using http://blogs.msdn.com/jpapiez/archive/2004/12/09/279041.aspx link but could not get it working.
Do i need IIS installed? Its not installed on this box...
please suggest something... i am stuck...
Thanks,
View 3 Replies
View Related