Package Configuration/migration Questions
Apr 19, 2007
I have a two questions that hopefully someone can assist.
Question 1
We use the XML file option to store configuration information for our SSIS packages. It seems that the XML configuration files are not being used in all instances even though we specify the file in SQL Agent. From what I€™ve seen, properties like server name and SQL user id are being pulled from the XML configuration file. Other types of references in the configuration file like email addresses for a send email task are not being pulled.
The way I determined this is that I manually edited the configuration XML file on SQL Server. Changes done to email addresses in the configuration file are not used when I run the package via SQL Agent. But changes to server name and/or SQL user name are used.
If we run the deployment wizard and update the email addresses via the Wizard, the changes are reflected in the configuration file and the SQL Agent job picks up the changes. It seems a waste to have to rerun the SSIS migration wizard just to update the configuration XML file.
We are using SQL Server 2005 SP 1. When we deploy SSIS packages, we run the deployment wizard and choose the following options:
SQL Server deployment and check the Validate packages after installation.
Specify SQL Server, Use Windows Authentication, and check the Rely on server storage for encryption
Update information in the configuration file within the wizard
Question 2
When we migrate SSIS packages on the production server, we get the following error message during the Package Validate step: The SSIS logging provider has failed to open the log. Error code: 0x80070005. Could this be caused by differences in the SQL directory structure between the Development and Production Servers? On the Development workstation and the Development server, a directory called C:Program FilesMicrosoft SQL Server90DTSLogProviders exists. On our production server, this folder is located on D:Program FilesMicrosoft SQL Server90DTSLogProviders.
View 7 Replies
ADVERTISEMENT
Oct 2, 2007
Hello,
I'm new to databases but not to programming in general. I've got a few applications which currently sit on a single PC and read/update a particular XML file on the box. The applications are C# apps and use FileSystemWatchers and a mutex to keep watch for changes and update the XML file as needed. It has worked reasonable well for me.
Now, I need to run these applications on separate machines, so it seemed a good point to consider moving toward a database solution.
I'm hoping that there is a straightforward solution using SQL Server that will ensure that my applications alway have the current view of the world. I had run into instances with my existing setup where once in a while an application thought its DataSet was up to date, but wasn't.
Can someone point me in the right direction to implement the solution? I suspect that this is a really common need for lots of programs that are distributed. I'm still quite a newbie with databases and SQL Server, so I'm not even sure I'm using the right terms to describe things.
Thanks,
Matt
View 1 Replies
View Related
Jan 29, 2008
I have a question regarding a DTS (SQL server 2000 package) to SSIS (SQL SERVER 2005 package) migration.
which is the best forum to post my question ?
View 1 Replies
View Related
May 23, 2006
Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do
thanks in advance Dave
Background:
SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go
http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/24/6843.aspx
Wizard results:
Name:
ETL
Type:
SQL Server
Connection name:
ETLConfiguration
Any existing configuration information for selected configuration filter will be overwritten with new configuration settings.
Configuration table name:
[dbo].[SSIS Configurations]
Configuration filter:
PT_CUST_ABR
Target Property:
Package.Variables[User::gsPreLoad].Properties[Value]
Package.Variables[User::gsPostLoad].Properties[Value]
Package.Variables[User::gsLoad].Properties[Value]
Package.Variables[User::gsFlatFilename].Properties[Value]
Package.Variables[User::gsFileName].Properties[Value]
Package.Variables[User::gsCDOMailTo].Properties[Value]
Package.Variables[User::gsCDOMailSubject].Properties[Value]
Package.Variables[User::giRecordCount].Properties[Value]
Package.Variables[User::giFileSize].Properties[Value]
Package.Variables[User::giBatchID].Properties[Value]
Package.Variables[User::gdFileDateCreated].Properties[Value]
Package.Connections[MyDatabase].Properties[ServerName]
Package.Connections[MyDatabase].Properties[InitialCatalog]
USE [ETLConfiguration]
GO
/****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
View 3 Replies
View Related
Jun 29, 2006
I've been doing some VB6 app development against a SqlExpress database, but have confusion with issues of deployment and configuration. It's my intention to distribute the app and db to client installations, both as stand-alone and network-ready shared access. Here's basically what's occurred thus far:
The database originated as an Access mdb file.
Downloaded and installed SqlExpress, Tools, Manager, and sample database(s).
Used the Upsizing Wizard in Access to create a SqlExpress db.
Attached to the database in Express Manager so I could examine and access the table data.
Hit the data through the application with the following connection string:
Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=.sqlexpress; AttachDBFileName=(MyDataPathAndFileNameHere)
Been developing against this db ever since.
Now, some points of my confusion thus far with regard to deployment:
1. Named Instance and User Instance. I assume I'm using a Named instance with the preceding connection string. Honestly, I don't recall doing any kind of adminstative setup/configuration of the db except attaching to it in Express Manager, so I'm confused over why I would need a User Instance for a local copy of the database. Are there setup steps I've missed or did the Upsizing Wizard do them for me? I've read the white paper on user instance usage, but it raises as many questions to me as it answers. I do understand User instance is intended for local copy of the db, even though I'm not using it that way in development.
2. Do I distribute both the MDF and LDF files? I saw mention somewhere not to distribute the LDF log file, but the app doesn't work without it being there.
3. What has to be done for deployment to a server for shared acccess. Aside from installing SqlExpress, and possibly Manager, I'll need to configure for remote connection usage using Configuration Manager or Surface Area Configuation tools. Does this need an administrator to create and establish users accounts, rights, and such?
4. Are there any reference books out on this stuff yet, besides books online, which I don't find satisfactory.
5. Can SqlExpress coexist on Sql 2000 server? I realize SqlExpress is essentially Sql 2005, but our existing server is Sql 2000. Should it go on a different server?
Sorry for the myriad of newbie questions, but I'm a programmer and not a db admin.
TIA,
Rick
View 6 Replies
View Related
Feb 2, 2007
Hi,
I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.
So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.
Is this possible?
Thanks for your help.
$wapnil
View 6 Replies
View Related
Sep 10, 2007
Deployed Report having SSIS package as source do not work when Indirect Package configuration is used in ETL package. It seems ETL package when called/executed from Report manager does not recognize environment variable to pick up the dtsconfig file.
The Report works when Direct package configuration is used to same dtsconfig file.
What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod very difficult.
View 1 Replies
View Related
Sep 14, 2006
Hi guys,
Here's the deal. I have a child package, (say, pack01.dtsx), which uses a dtsconfig file for its connection string, which can be called from other packages, but which also can be called by itself.
However I also have another package (say, pack02.dtsx) which uses the same dtsconfig file for its connection string. It calls on pack01.dtsx.
When I use DTEXECUI and run pack01.dtsx, specifying the proper .dtsconfig file, it goes well. But when I try and run pack02.dtsx, an error occurs saying pack01.dtsx connection cannot be established.
How do I pass the connectionstring being used by pack02 to pack01, without having to remove the configuration file setting of pack01? Can a Parent Package configuration and a configuration file try and map to the same property?
Hope someone could help. Thanks.
View 7 Replies
View Related
Jan 11, 2007
I've been migrating SQL 2000 DTS packages into SQL 2005 using the Migrate DTS 2000 Package wizard in Visual Studio 2005. All packages migrate fine except one which throws out the following error:
exception of type 'Microsoft.SQLServer.DTS.MigrationWizard.HelperUtility.DTSMWException was thrown
No other supporting information is given to help diagnose the problem. Has anyone else come across this error before? Looking at past threads, I've checked the DTS package for leading or trailing spaces which might cause an issue and there are none.
Thanks.
View 3 Replies
View Related
Mar 13, 2007
In Visual Studion I invoke the wizard, indicate the source for the DTS packages and pick a folder for the migrated package but I don't get a list of my source packages. Instead, I get the error message, 'index out of range, must be non negative, parameter name index(mscorlib)'.
Has anyone ever gotten this error. What does it mean and how can I fix it?
Thanks in advance,
View 1 Replies
View Related
Mar 6, 2006
Hi Guys,
Here's the current scenario:
I am creating a windows application to manage my ssis packages.
My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.
However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.
The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.
Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.
Thanks,
kervy
View 3 Replies
View Related
Jun 8, 2007
OK, I was able to successfully migrate all of my DTS packages to SSIS, for SQL 2005. I can log into intergration services and see my packages listed under:
servername --> stored packages --> msdb. Now my question is, how can I open these packages, not run them open them in a design mode like you can in SQL 2000, you can double click on the package name and view the design of the package. how can i do that now that I have them in SQL 2005?
View 6 Replies
View Related
Apr 14, 2008
I'm slowing coming up to speed on configurations and deployment.
I have 2 questions for this thread.
Question 1:
When I create the deployment manifest (the file that gets created when I build) and then run it on the destination machine, there's a step in the wizard that asks for a folder location. The exact page on the wizard is called "Select Installation Folder", just to make it clear what I'm talking about.
What significance does this folder have? I noticed that when I was using the XML config option, the config file appeared there (and nothing else). When I use the SQL Server config option, I didnt see anything going into that folder. Are these the expected results in each case?
The description on that wizard page says, "The installer will install the SSIS package dependencies in the following folder." I'm not sure what this means and if I should expect more than the XML config file to appear in there (in the case when I used XLM config option). What are the "dependencies", other than the config file, that the wizard is saying that the folder will have?
Question 2:
I've been playing with deployment and there's a bunch of "play" packages that are on my test server. How do I clean these up?
View 10 Replies
View Related
Nov 10, 2005
I am unable to migrate any DTS packages, from a SQL Server 2000 package, from a structured storage file, or from a DTS package imported into SQL2005 (Developer Edition, 32 bit). Running the Package Migration Wizard, every time I get to the List Package screen, i get the error below. I haven't found any other mention of this so far. Anyone else seen this error or have suggestions?
View 20 Replies
View Related
Aug 8, 2007
I have created a package configuration to read the connectionstring for the oledb connection manager.
Saved the file as Environment.dtsConfig in the same directory as the other packages.
Do the packages refer to the configuration file automatically?
Does it make a difference if I use the dtexec to start the ssis packages since there is now a configuration file?
Thanks
View 4 Replies
View Related
Mar 29, 2007
I would like to set package configuration.But dont know how to.
What i want to achieve is that i want to change connections based on test ,development and production environment of the same package...
How can i do that without me changing the connections manually to each task based on environment..
using package configuration i dont know how to...Please let me know.
View 17 Replies
View Related
Apr 5, 2006
Hello,
I'm developing some packages in SSIS and I need to implement a configuration file to load the server's connections and other parameters, so I can use this same connection for all packages.
Have anyone a step by step to perform this configuration ?
I exported a Connection Manager to a .dtsConfig file, but I don't know how to use this config file.
Any help is welcome !
Thanks,
Guber
View 1 Replies
View Related
Oct 3, 2006
Here is the scenario we are
trying to use; (it a bit long winded please bare with me)
There are 2 Packages Parent
and Child. Both have 2 Connection Managers (€śConfiguration€? & €śData Connection€?).
The €śData Connection€? has an invalid connection set to start with this is so
that we can deploy this to multiple places and use a SQL table to hold the
configuration.
At runtime €śConfiguration€? is
set to a valid SQL2005 database connection via the command line or in BI but €śData
Connection€? is left invalid.
The Package Configuration point
to an SQL server using €śConfiguration€? this has the connection string for the €śData
Connection€? Connection Manager (a valid connection string). The package errors
with can not acquire connection.
The first task is to put the
Connection string of the Connection manager €śConfiguration€? in to a variable.
Then the child package is called.
The Child package has the same
2 connections €śConfiguration€? & €śData Connection€? both are Invalid at this
time.
1) We use the Package
Configuration to populate a variable from the parent package which holds the €śConfiguration€?
connection string.
2) We use a Package
Configuration to populate then Configuration€? connection from the parent
package which holds the €śConfiguration€? connection.
3) We use a Package
Configuration to populate the €śData Connection€? Connection Manager using the €śConfiguration€?
connection. Which should be Set via step 2 here.
The First task on the child
package has a bit of script to check all the connection manager return a valid
connection to make sure database are up and running before connection. This
fails at the moment return in an error.
We want to use the same code
in multiple environments and as such don€™t want to make code changes that are really
only environment changes.
IE connection string to
servers. I can€™t seem to see anything saying we are doping anything wrong but if
the connections are all set to a valid connection before running this seems to
work. When invalid connection are used this seems to fall over.
View 1 Replies
View Related
Aug 8, 2007
I have created a package configuration to read the connectionstring for the oledb connection manager.
Saved the file as Environment.dtsConfig in the same directory as the other packages.
Do the packages refer to the configuration file automatically?
Does it make a difference if I use the dtexec to start the ssis packages since there is now a configuration file?
Thanks
View 1 Replies
View Related
Aug 10, 2007
Does it matter if the configuration is by xml,sql server,parent package variable,environment variable etc.
I use xml configuration which i kind of feel easy as i can change the connection directly by typing.Not used with others much..
Is there any security issue here .
Please let me know
View 5 Replies
View Related
Sep 14, 2006
Hi,
i am haveing package which is devloped in BIDS and i am executing the package from DotNet (Windows Application C#).during development my package is pointed to Server1,Database1.when we move the package to another test environment for a different server and database (Server2,Database2). we are giving the connection string details in dtsConfig file .but Package is not takeing the new connection string instead its takeing the old connection string (design time connection) and throwing error.what could be the problem
Invoking Code (C#)
--------------------
btn_SaExecute.Enabled = false;
string pkgLocation;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
pkgLocation = "D:JeganSSIS PrjsInvokeTestInvokeTestData1.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);
pkg.EnableConfigurations = true;
pkgResults = pkg.Execute();
Data.dtsConfig
----------------------
<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="PSLSDCJegan.thangadurai" GeneratedFromPackageName="Data1" GeneratedFromPackageID="{30ACE86F-D0D3-4575-AD46-2E9C2F34D46B}" GeneratedDate="9/14/2006 12:54:01 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="Package.Connections[XXXX.YYYY].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=XXX;User ID=XXX;Password=XXX;Provider=MSDAORA.1;Persist Security Info=True;
</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Thanks
Jegan
View 1 Replies
View Related
Sep 1, 2006
Hi all,
Hopefully a simple question. In my c# application i can call ImportConfigurationFile(<str>) to import an xml configuration file. How do i go about importing configuration settings from a SQL configuration package? I have created this but see no way to load it. I'd prefer to use the config file created in the SQL database.
Any help would be most appreciated.
Many thanks,
Grant
View 1 Replies
View Related
Oct 17, 2006
Hi all of us,
I'm looking any .dtsconfig file as sample for my first package configuration. I've got ssis packages which might be updated on development and then moved to production. First of all, I'd like dynamically change the connections but I don't know exactly how to do that. I'm taking a look on the wizard but when I choose a Sql Server request me a table.
Any sample or link for that would be welcomed!
TIA
View 2 Replies
View Related
Aug 25, 2006
Type:
Configuration File
New configuration file will be created.
File name:
C:Test.dtsconfig
Properties:
Package.Connections[Flat File Connection Manager].Properties[ConnectionString]
------------------------------------
So lets say, I modify the connectionstring for the Flat File connection manager by editing the xml file. Next time, I run the package, it does not grab the new value. Am I misunderstanding the concept of Package Configuration?
View 3 Replies
View Related
Mar 14, 2007
Hi All,
I have couple of properties in my package Configuration
(1) Lets me know the Folder path - where I have file processing
(2) Connection String
When working in the Development machine, I am able to play around these values and test the application. I have tried both XML Configuration and SQL Server Configuration
But during deployment in the Test Server, the values in the Configuration are not getting reflected automatically and takes the values assigned during deployment
I tried importing the XML file and it works. But the values are not persistant.After running the package, the value gets resetted to the old value. Every time I run my package, I am reassingning my package variables.
Be it XML Configuration File or SQL Server, I would like to have the variables pulled out from the Configuration I am setting during the deployment. the values should be persistant and should refer the updated values.
Any help will be highly appreciated...thanks
View 10 Replies
View Related
Dec 7, 2006
hi everyone
I am putting my connection string in package configuration file, which i create through package configuration option.
But connection is not picking up value where as other varibles are picking up values from same file.
Is there is any other setting that we have to set for connection string.
Thanks and regards
Rahul Kumar
View 3 Replies
View Related
Apr 12, 2007
Hi, I'm facing a problem that I'm trying to solve. I report it in the following hoping somone can help me!
the context:
- I have a solution containing almost 20 packages
- I have a main package containing the sequences (Sequence Container) calling all others packages (Execute Package Task)
- every package has the connections to its own DBsource ; so the package X has the its own connections to DBsourceA and DBsourceB, the package Y has the its own connections to DBsourceB, DBsourceC, DBsourceD and the destination DB is unique for all the packages.
My problem is:
I have the necessity to define in the best way a config (one for all the dtsx, possibly) that allow me to manage easily the switch from the developing env to the deployment env, basically for the DB connections.
Which is the best way to do this and can anyone tell me the steps to follow?
View 8 Replies
View Related
Mar 6, 2006
I've been searching for an answer to my question quite some time now and I've not been able to figure it out yet.
Situation:
- I've created a SSIS package containing a bulk insert task.
- I've added a package configuration containing the appropriate connection manager (i.e. dev, beta or live)
- CreateDeploymentUtility = true
- I've copied the deployment folder to our beta server and I started the manifest file to install the package to the sql 2005 server, after that I specified the config file location and changed the value so the approriate connection manager is used.
- When I execute the package from the sql server the package doesn't read the value from the xml config file, it uses the connection which was originally specified in the package, whereas when I run the package from my BIDS it is reading the value from the xml config file?
I can't seem to figure out why this is happening? am I missing something here?
Thnx.
View 1 Replies
View Related
Jan 3, 2007
I'm facing a little problem concerning package configuration that is using a SQL table.
I have 4 exactly the same Db's on the same SQL server, each with another name of course (Dbipa, DbIpB, DbIpC & DbIpD).
My developers created 4 exactly the same SSIS packages with 4 exactly the same jobs, each for his own DB only the connection settings are different.
Now I was trying to manage this with only one SSIS package by using an SSIS Package configuration table in each DB.
I thought it would be possible to start the package with the following parameter
/SET Package.Connections[SQL].InitialCatalog;DBIPA OR
/SET Package.Connections[SQL].InitialCatalog;DBIPB OR
/SET Package.Connections[SQL].InitialCatalog;DBIPC OR
/SET Package.Connections[SQL].InitialCatalog;DBIPD
But this doesn't work because before he changes the initial catalog in the connection named SQL, he already did the loading of the SSIS_Configuration table. So he point always to the same config table.
Is there a way to change the connection before the package configuration is being executed.
Ludo Bernaerts
View 1 Replies
View Related
Nov 9, 2006
1) We are doing data migration from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.
2) We have used SSIS packages and data flow tasks in which we mentioned connection strings for source and target containers.
3) We have a master execute package which contains series of execute packages in relational order.
4) When we execute this master package, we should be able to dynamically specify different source and connection strings for all packages.
5) In master execute package, we generate an XML configuration file using SSIS -> Package Configurations... using Connection Managers -> Particular connection
6) Now, we change connection string to point to another database after adding this new OLE DB connection in Connection Manager of each and every package.
7) When we save, build and execute master package it is still executing migration packages against the old database. Please let me know what I am missing.
View 3 Replies
View Related
Jul 17, 2006
Hi,
Programmatically, how will i be able to know if a package is using package configurations?
Thanks.
View 10 Replies
View Related
Jul 2, 2007
I have a package that executes succesfully before package is configured.
but when package is configured to use configuration details either from SQL Server configuration table or XML configuration file, gives an error :
[Connection manager "XYZ"] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Can anyone help me out with this ?
Experts !!! this is my second post, my previous post is still unanswered.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1793362&SiteID=1
View 7 Replies
View Related
May 29, 2007
Hi,
I am new to SSIS. I have an SSIS package which we are storing it in the application server ( not in SQL Server ). We are storing the database name etc.. in package configuration. Where should I store the configuration file? In app server or in the database server. Also please tell how to execute the package stored as file system.
Thanks
ramesh
View 11 Replies
View Related
