Deploying Packages With SQL Server Based Configuration
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
ADVERTISEMENT
Jan 4, 2007
Hi Guys!
I have created a big list of packages, some calling others. They all work fine from my computer using Visual Studio.
When I try to deploy them (building them with deployment turned on and running them either directly from Management Studio or as a job) I get the errors with the password of connection strings. From what I read so far its the encryption process that kills it.
I have tried to add a password to some packages, but it still didnt work (only when run directly on my computer in management studio after deploying to SQL Server, but not as a job).
I have tried to change ProtectionLevel to SecurityStorage, wouldnt let me save in Visual Studio (I understand it is ot allowed in VS because you are saving to File System, how the hell am I supposed to save it to anything else? why is it showing there if its not even valid?).
If anyone can please give me the steps to doing it properly, that would be awesome. I simply need to run the packages from SQL Server! thats all! I have no idea why it has to be soooo difficult :/
View 8 Replies
View Related
May 1, 2006
Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.
WTF????????? ·%$·&$%%& i`m sure my connections are ok,, cause i prevoiusly checked them, the problem occurs when deploying the package.. it seems to be an error when connecting to the source but whyy?????, and what about the package configurations ?? should i use them,, and how????
View 4 Replies
View Related
Feb 15, 2008
I am deploying SSIS packages using the deployment utility. We are starting to use configuration files for storing connection information. I have the configuration files in a directory on the server I am deploying the packages to, I want all new packages deployed to use these files. When I deploy a new package it wants to overwrite the config files on the server with the ones in the package. I have tried to remove the .dtsconfig file from the package deployment folder but then the package will not use the config files on the server. How do you deploy a package and get it to use the .dtsconfig files that are already on the server?
View 4 Replies
View Related
Jan 31, 2008
Hi
I am fairly new to SSIS and I have a number of questions concerning deploying SSIS packages and Configuration Files
The SSIS packages will be deployed to three environments (DEV, TEST,PROD - this is a clustered environment) and will be executed by SQL Server Agent Jobs. There will be a need for different configuration values for each environment (file import directory, database server connectivity) , the configurations will be reused by mulitple packages.
I have decided to deploy the packages and configuration files to a file directory in the format
<<DRIVE LETTER>>SSIS PackagesPackages
<<DRIVE LETTER>>SSIS PackagesConfiguration Files
The questions are:
1) I storing the packages/config files to a file directory the best approach, or should I be deploying the packages to SQL or file ( C:Program FilesMicrosoft SQL Server 90DTSPackages)
2) Is it best to have different configuration files for each environment (dDBConn_DEV.dtsconfig, dDBConn_TEST) or have the same config file and then change the values during deployment (via scripts).
3) What is the best way to deploy the packages and config files to the different environments (rather do it via scripts than the deployment utility)
4) Where is the best place to store the config files (I have one VS 2005 project per package, the confi files are used by multiple packages), TFS is our source control software
5) Does any one know of a good website to look at for best practice when deploying packages and config files
Regards
Tim
View 5 Replies
View Related
Apr 27, 2008
Hi
I need to move my packages from development to production.
In the development the database name is dev and in producton it is prod.
The username passwords are also different .
When i deploy my packages in production i dont think so it will work any pointers on how to make these things configurable like when the person deploying this package runs it he is prompted for the connection string and username and password
regards
Hrishy
View 2 Replies
View Related
Jan 25, 2008
I am currently in the process of trying to migrate from DTS to SSIS. SSIS is totally new to me and I am trying to put together a mechanism for easily moving packages between our Development, Test, and Production environments. I have done some investigating into using Configurations to accomplish this. What I basically need to do it update my Connection objects when moving between environments without having to recompile. The optimal solution is that the Connection objects dynamically update, but I'm not sure this is possible. I think some of this could be done with Configurations, but one catch is that I need to have the packages running on both environments.I have been able to put this all together to figure out a good solution for this and I was hoping some of you on the forums could give me some ideas.
View 5 Replies
View Related
Jan 11, 2007
I am hoping someone with more experiencing deploying packages from dev to other environments can help me out here. When developing in BIDS everything works fine. When I deploy I want to use a configuration file. This does seem to work fine by specifying the configuration file on the DTEXEC command. However, if it is specified incorrectly the package STILL executes using the information it was created with. I am attempting to control things like connection strings and passwords via configuration files so that I can take the same package and deploy to different locations and have it run against different sources and destinations. What I am afraid of is that things could easily get screwed up if the configuration file gets moved or renamed, or if the dba that is scheduling the job (outside of sql agent using a different job scheduler on a machine that only has the SSIS service) messes up typing the config file name. Shouldn't it fail if you specify an invalid parameter. Isn't there a way to make it do that? Any help or insight is appreciated. BTW I have used environment variables as well and the issue is the same. If it cannot locate it, it still will execute.
View 2 Replies
View Related
Aug 24, 2007
We have a system here where we develop SSIS packages on a development server. I am trying to figure out the cleanest way to promote these changes to a production server where stored procedures/tables that are used in the package are not deployed yet.
When I switch the connection in the package to the production server there are alot of objects in the package that are "invalidated" because they are trying to verify existence of tables/columns. One example is outputing the results of a query to a text file. The text file destination gets a red X on it because it cant grab the columns from the source query (because that stored procedure doesnt exist yet)
Is there a best practices or something on how to deploy packages to a production system? I have tried turning off "ValidateExternalMetaData" with no success.
Thanks!
View 3 Replies
View Related
Jan 18, 2006
I am trying to deploy a SSIS package which includes a FTP task. It works fine on the machine it was developed . When deploying the package on a production server, all other config changes work, but the FTP task is failing, with authentication error.
any help on how to persist the data would be appreciated
thanks
View 1 Replies
View Related
May 10, 2007
Hi,
I am developing a application which is dependent on Sql database (mdf).
I want to know how should i need to deploye or Package the database along with my
application , would i need to instal the sql server exp on to the client system, or there
is any other way to do it , I know that an Access file can be accessed through the
application even the client system dont hav Office, Is there any such type of
mechanism , through which we can do the same with sql database
View 2 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
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
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
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
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
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
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
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
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
Sep 10, 2007
Hi,
Is it possible to do an event based scheduling of SSIS packages from SQL Server Agent? Like schedule a package to run when a file is available in a particular folder.
Does anyone have any similar experiences to share?
Regards,
Emil
View 1 Replies
View Related
Sep 4, 2015
We are building a dataload application where parameters are store in a table. And there are multiple packages for each load.There is a column IsChecked column if it is 1 then only the child package should execute.Created a master package. In which i have taken execute SQL task in that storing a results in variable and based on the result the child package should execute. But In executesql task i selected result set as full result set. Â I am getting the below error.
[Execute SQL Task] Error: Executing the query "SELECT Â isnull(ID ,0) AS ID FROM DataLoadParameter..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::LoadValue" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 3 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
May 2, 2007
I have just finished installing SQL 2005 Ent Edition on Win 2000 Adv Server, SQL2005 SP2, and SP2 Hotfix KB934458. After the installation, I could see and configure all services via SQL Configuration Manager and SQL Server Surface Area Configuration tools. This worked for a couple of days and now both configuration tools no longer detect SQL2005 components. SQL Server Surface Area Configuration issued an error that said "No SQL Server 2005 components were found on specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLAC)". SQL Configuration Manager did not list any installed services. I don€™t know what caused this. Anyone has any idea? Please help! Below is the Installation Report which shows installed components.
===================================
The following components are installed on this server
DEV
Analysis Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Database Engine
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Reporting Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
QA
Analysis Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Database Engine
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Common components
Integration Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Notification Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Workstation Components
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
===================================
View 1 Replies
View Related
Apr 22, 2015
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
View 4 Replies
View Related
Sep 2, 2005
Hi Well this is my first post on this forum, in fact I have not even used SQL Server yet!
My question is.... I have been asked to produce an application which will be a VB6 application with an SQL Server database behind it. I would like to be able to put the application along with the database on to a CD and run them directly from the CD once it is distributed. So the question I guess is can this be done with SQL Server i.e. can a table be exported in a format which will allow it to be queried in a standalone fashion? Maybe I have not explained myself properly but I welcome any questions which will help clarify my problem.
Thanks Red
View 1 Replies
View Related
