SSIS Configuration File
May 28, 2007
Hello All,
In my project, developers enable configuration on the packages they develop. This means that if a project has 100 packages, each one has its own configuration file.
If our primary database server changes, I have to go an update 100 configuration files.
This defeats the purpose of having a configuration file in the first place.
I want to have a configuration file which contains common settings for all the packages in the project.
At the same time, I should have the flexibility of having individual configuration files for the project.
Is this possible and how can I do this?
View 8 Replies
ADVERTISEMENT
Apr 3, 2007
We are deploying our SSIS packages into different folders. For example:
Test1
est2
production
Test1 points to the Test1 database, etc.
So, I configured my SSIS package to use a database connection called dbMAIN.
I then setup the SSIS File Configuration (XML) so that "dbMain" points to Test1. This xml file, called Global.dtsConfig, sits in C: est1, the same place as my ssis.dtsx file.
I want to copy my dtsx file and my dtsconfig file into est2 and production. The problem is that the the location of dtsconfig is HARDCODED in the ssis package!
How do I dynamically change it?
I tried to hardcode the location to ".Global.dtsconfig", but that did not work! Please advise!
View 1 Replies
View Related
Oct 17, 2006
Thanks to some earlier help from this forum, I have my package running using configuration files and Windows Authentication. However, when I try to execute the package using Sql authentication, login fails for the user i am specifying. When I check out the configuration file, the connection string does not contain the password. I added the password to the connection string, but it does not cone back. I followed the instructions found on the following site:
http://www.nigelrivett.net/DTS/SSISConfigurationFile.html
After trying what was suggested there, I was still unsuccessful with my login attempt.
Anyone know how to set up a package to run using Sql authentication?
Thanks!
Craig
View 1 Replies
View Related
Nov 14, 2007
I have an SSIS package (which is already delivered to the customer, so I can't change it), but I want to switch off (disable) one of the elements. I use a dtsConfig file (XML), so I want to just send an update to that config file to switch off the element.
So, I used the Package Configurations Organizer to generate a new config file with the element switched off, and it generated the following:-
<Configuration ConfiguredType="Property" Path="PackageRun Data LoadForeach Data FileLoad Invalid Format Recs into rej_dat_format_errors.Properties[Disable]" ValueType="Boolean">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
So I copied these lines into my "production" config file and ran the package, but got the following warning message:-
Warning: 0x80012017 at ImportData: The package path referenced an object that cannot be found: "PackageRun Data LoadForeach Data FileLoad Invalid Format Recs into rej_dat_format_errors.Properties[Disable]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
Can anyone explain why? Is there some issue with having spaces in the Path name perhaps? Is there some syntax to get around it? Why did it generate a Path that is wrong?
Thanks
- AA
View 5 Replies
View Related
Jan 24, 2008
Can someone identify what is wrong with the XML config file that I i'm using with a SSIS package? The XML file was created by Visual Studio tool
Code Snippet
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="BRIDGENETjpacella" GeneratedFromPackageName="SsisEndBatch" GeneratedFromPackageID="{91A1941E-E066-47F7-8DD4-9E260F38741D}" GeneratedDate="1/21/2008 4:35:22 PM" />
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-DEV02.BNETDB2].Properties[InitialCatalog]" ValueType="String">
<ConfiguredValue>BNETDB2IMPORTTEST</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-DEV02.BNETDB2].Properties[ServerName]" ValueType="String">
<ConfiguredValue>CHI-DEV02</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-PROD01.BNETDB2].Properties[InitialCatalog]" ValueType="String">
<ConfiguredValue>BNETDB2</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-PROD01.BNETDB2].Properties[ServerName]" ValueType="String">
<ConfiguredValue>CHI-FILE01</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="PackageRun Report Executor.Properties[Executable]" ValueType="String">
<ConfiguredValue>C:ReportsReportProgramCHI_FILEBridgeNet.Reporting.ExecuteConsole.exe</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="PackageRun Report Executor.Properties[WorkingDirectory]" ValueType="String">
<ConfiguredValue>C:ReportsReportProgramCHI_FILE</ConfiguredValue>
</Configuration>
</DTSConfiguration>
When I run the pacakge, this is what happens:
Started: 10:48:56 AM
Info: 2008-01-24 10:49:02.92
Code: 0x40016041
Source: SsisEndBatch
Description: The package is attempting to configure from the XML file "C:Program Files (x86)Microsoft SQL Server90DTSPackagesBridgeNet.BI.SSIS.EndBatc
ENDBATCH TEST ENV.dtsConfig".
End Info
Warning: 2008-01-24 10:49:03.15
Code: 0x80012011
Source: SsisEndBatch
Description: Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
End Warning
View 6 Replies
View Related
Oct 23, 2007
Hi, the message below is generated when I try to generate an SSIS configuraiton file. The link does not provide any help. Do you have any suggestion what could cause this error?
Thanks, Piet
==========
TITLE: Microsoft Visual Studio
------------------------------
Could not complete wizard actions.
------------------------------
ADDITIONAL INFORMATION:
Could not generate the configuration file. (Microsoft.DataTransformationServices.Wizards)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Wizards.ConfigurationWizardPages.ConfigurationWizardSR&EvtID=CouldNotGenerateConfigurationFile&LinkId=20476
------------------------------
Failure exporting configuration file.
(QAYTriggerNotify)
View 9 Replies
View Related
Apr 25, 2008
Hello
I've been given the task of migrating a DTS package to SSIS (neither of which I am particularly familar with). The first job in the DTS package is to read a .ini file and set a bunch of variables. These variables are then used throughout the DTS package. After running the DTS package through the SSIS migration wizard this job turns into an execute script task and I can't see if it is still reading the .ini file. However, the only real purpose of this step is to allow different parameters to be passed in development, test, production etc. So I am thinking this whole step can be removed and effectively replaced with a package configuration (I'll probably use an XML file). My understanding is that by selecting the name/value pairs as appropriate in the XML package configuration file means this values will be passed in at runtime and achieve the same functionality. Is this the correct way to do this in SSIS, or do I still need the .ini file and variables?
Thanks for any advice on this issue.
Regards, John
View 7 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
Apr 21, 2008
Hello
I am trying to use Foreach loop container - Foreach File Enumerator
Is there easy way to retrieve enumerator configuration folder from variable - so I can easily move package from server to server?
I would like to have name of the folder we getting files from retrieved from variable by using this control
Thank you in advance
Armine Bell
View 3 Replies
View Related
Jan 25, 2007
Hi,
I cannot get the log file path read from the configuration.
If the path in the Connection Manager is invalid, package throws an error "SSIS logging provider has failed to open the log" instead of reading it from the config.file. What am I doing wrong?
Here is the portion of the config file. Everthing else is read from the config file correctly.
- <Configuration ConfiguredType="Property" Path="Package.Connections[SalesforceConnectLog].Properties[ConnectionString]" ValueType="String"> ConfiguredValue>D:APPSBACIARSalesforceConnectSalesforceConnect.log</ConfiguredValue>
</Configuration>
Appreciate a help.
Gulden
View 6 Replies
View Related
May 22, 2007
Hi,
I have created an XML Configuration file within the Designer and have added
my exported my database connection details.
How do I get my TSQL Task to use this connection.
My ConnectionType is OLE DB.
For my Connection how do I pick up what is in my .dtsConfig file?
Thanks in Advance.
View 1 Replies
View Related
Jun 16, 2006
Hi,
My requirement is to have the connection strings for the source and destination to be a parameter for the packages that way they can be changed from the sql job scheduler.
Please let me know how to go about it.
Any help will be highly appreciated.
Thanks
Vijaykishan
View 11 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
Feb 5, 2008
Hi all,
I have finished the development of an SSIS package but I would like to make it a bit more flexible by adding a configuration file. The script migrates a database from a source to a destination and syncs with another source.
So there is the source db and the destination db. However, I have noticed that standard SSIS configurations can get a little tricky. For instance, I have two connection managers for the destination db (one that points to the schema and one that doesnt). So my question is: Is there any way to create a config file thta you can input a few attributes like sourceDB, destinationDB, schemaName and get the package itself to parse and process it?
Thanks,
Yannis
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 21, 2006
Hi,
I need to configure my package using SSIS Configurations. When I opened SSIS Configuration wizard, press ADD and selected Xml Configuration, I found two options.
One being : Specify configurations directly.
next : config location is stored in env variable.
How to use the second option?[ env variable]. Say I want to configure my connection string for my destination database [ OLEDB Destination Component]. How to do that using second option.
Thanks
Kishan
View 9 Replies
View Related
Jul 17, 2007
Hello,
I am having a bit of trouble with SSIS Configurations.
- In BIDS, I have added a configuration file and specified the properties I want to expose.
- When I build the project, I get the standard bindeployment folder which contains the package file (.dtsx), the configuration file (.dtsconfig) and the deploymentmanifest.
- Before deploying the package, I edit the config file to have the settings I want it to for the environment I am deploying to.
- The package deploys OK
- When I work directly on the SSIS server (64 bit), I can go into SQl Mgt Studio, choose 'run package' and when I look in the connection manager window all the settings are as I desire ( I havent had to add a configuration file manually)
- When I work on a client machine, I connect to the SSIS server and choose 'run package' - the properties/connections are the same as on the server but the values themselves are completely different.
Why is this? Why when I run the package from the client (32 bit) do the configuration values appear to be completely different? How can I run the package remotely and pick up the configuration values that I deployed with?
Or have I misunderstood this whole configuration function?
Please, please help!
Mgale1
View 4 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
Apr 27, 2006
Hi,
I have an SSIS Package which reads the properties values from Registry. As per the constraint of SSIS I have created the keys under HKCU in the registry and things work fine from the designer.
But, when I schedule the SSIS Package to run under SQL Agent (which is running under Local System) the values of configuration do not seem to be picked up from Registry.
How I tested was:
I am picking up the server name for the Connection Manager from Registry and I set that value to an invalid servername. But, the job seems to be executing fine.
Using DTEXEC it fails as expected since, this is running under my credentials.
Also, SYSTEM has full control on the new regsitry keys.
Any information on this?
Thanks,
Gaurav
View 5 Replies
View Related
Feb 15, 2007
Hi,
I would like to know what is the difference between XML file and Database in SSIS configuration option.
Thanks,
K
View 2 Replies
View Related
Jul 5, 2006
Hello,
I want to store each SQL Server's (prod, test, etc) connection string in XML to make deployment & configuration easy. However, the connection string must be encrypted. Does anyone have any ideas or suggestions on how to accomplish both. I know how to encrypt it but I dont think SSIS can work with it.
Any help would be appreciated.
View 6 Replies
View Related
Jan 4, 2006
Is there a configuration file in SQL Server where I can find basicconfiguration of the server such as instance name, home folder, datafolder, etc.
View 1 Replies
View Related
Oct 1, 2006
I have a simple SSIS package (stored in the file system) that gets a file path from a configuration file. The configuration type is an indirect XML configuration file that uses an environment variable to store the location of the configuration file.
When I run the package using dtexecui, or just dtexec from a command line, the package successfully picks up the file path from the configuration file (for verification I am writing out the variable containing the path to the log file). However when I run the package from a SQL Agent job it appears that the configuration file is not being used (the path is set to the same dummy path that I used during development). I have tried running the job as both a CmdExec and an Integration Services job and both fail on the same thing (invalid file path).
Both the SQL Server Agent service and the Integration Services service use a domain account as their start up account. This domain account has been included in the local administrators group on the server (in case it was having trouble accessing the environment variables).
What is the problem here €“ surely changing the way in which the package runs should not affect the configuration file settings! Any help would be appreciated€¦
View 8 Replies
View Related
Oct 16, 2006
http://blogs.conchango.com/jamiethomson/default.aspx has a lot of great tid-bits for SQL 2005. I am currently on a tight deadine for 25 SSIS packages that need to be able to move from Dev to QA to Staging to Prod. For the life of me I cannot get any of the packages to *READ* the config files created with the package config wizard. All I want to do is move the connection string out of the package so we can change the config file and not have to touch (hand edit) each package. Any help is appreciated!
View 13 Replies
View Related
May 27, 2008
OK, here's the scenario:
I have some libraries (DLL) that I call from a regular console application. The data access methods get the connection string from the app.config, nothing special there.
The thing is: now I need to use the libraries inside a SSIS package. I call them from a script task, and everything is fine, except for the connection string.
Is there a way that I can get the connection string from the SSIS package configuration like I would from the app.config? Maybe some alternative to ConfigurationManager.AppSettings["ConnString"] in the DTS Runtime?
Thanks in advance...
View 3 Replies
View Related
Sep 15, 2006
Hi
I want know how to config SSIS Package With different connection Manager.
Sample .
When Design time i gave The Connection Manager(Devlopement Server) Name : STG1.XXXSchema
Server Name STG.ssss
Password : xxxxx
I make the The XML configuration file .
after deploy the Package i want from Development Server to Production Server . so i will change the Package Config File Open wiht Notepad .
Server Name STG.qqq
Password :xxxxxx
after Edit Connection String I run the Package . It Always Taking Whatever igave the Connection String When Design time only .is not taking what connection string edit hte in the Package Config File. Please Any one give the Solution .
How we can Change the connection string in XML Config File .Whne I tested . It always taking what ever we given in the Desing Time .
Thanks & Regards,
Jeyakumar .M
View 4 Replies
View Related
Jul 24, 2006
We have more than 100 packages which referenced many of the same data sources. In
ideal situation, we would like to create three master filters based on the dev, test, and prod servers and all packages can just reference the same filters. However since one package normally only use a subset of master connections defined in the configuration files, if only those subset of connections are set up in the package, when the
package is first open in BIDS, lots of errors will pop up saying that it could not find the connections defined in the configuration files. Even though this will not cost runtime error, it is still very annoying. Does anyone have better ideas on how to handle this?
View 9 Replies
View Related
May 31, 2007
I have an SSIS package, (actually many packages at this point because I can duplicate this problem each and every time) with an FTP Task and three connection managers (one for FTP, one for a file share, one OLEDB).
The package in its simplest form, grabs a file from an FTP site and transfers it to the file share. When there are no files found on the FTP site, the FTP task fails as expected.
Here is where it gets strange...I turned on a package configuration, with all variables / properties saved to a SQL table; all except for the OLEDB connection which I am leaving hardcoded in the package for the time being.
The problems I am seeing as soon as I activate the package configuration are twofold: The first occurs with the property "FileUsageType" defined to the file share connection. I have it set to "existing folder" in the SSIS tool, the SQL config table also has a row for this item defined with a value of 2. I have even tried manually changing this value in the SQL config table and the result is always the same error when I run the package:
Error: 0xC002F314 at FTP Task, FTP Task: File usage type of connection "Z:" should be "FolderExists" for operation "Receive".
If I remove this entry totally from the SQL config table, and leave that property hardcoded in the package then I am able to continue execution in the package and that particular error goes away. But then I come to weird problem #2: I stated above that when there are no files in the FTP directory to retrieve, then the FTP task fails, as I would expect. However, as soon as I activate the package configuration, even when there are no files at the FTP site, the FTP task doesnt fail anymore, it marks itself as successful instead. This is wreaking havoc on my precedence constraints that follow the FTP task naturally. As soon as I deactivate the package configuration, the FTP task behaves normally again.
The problems only seem to happen with config packages stored in SQL. I tested this also with XML file config instead and everything seemed to behave as it should. Unfortunately, I need to store these configs in SQL, not XML.
Does anyone have any ideas on what I need to look at to fix these weird issues?
View 5 Replies
View Related
May 16, 2006
Gentlemen
I've been working with SSIS (SQL 2005 SP1) and I have a problem adding a package configuration of the SQL Server type. Upon completion of all steps in the wizard, the "Target Object" and "Target Property" attributes of this configuration remain empty. Hence, the package cannot set this configuration when being executed.
Does anyone know if this is a bug or have I missed something?
Thank you
Karch
View 8 Replies
View Related
Nov 30, 2007
i am trying to configure my ssis package thru xml file option.
the location of the xml file should be pointing to a common folder name in different SQL Server (Dev, QA and Prod)
say for example
xml file need to be present in dev server (DEV01) at \DEV01outputssistest.xml
in QA it should be present in \QA01outputssistest.xml
i am able to do thru BIDS by changing individually but how do i do in package designer only once and change in xml later.
please advice.
View 11 Replies
View Related
Jun 26, 2006
How can I update a variable in the XML configuration file. Can I use the configuration object for it or do I need to use XML objects and do it just like any other XML file?
Another problem I have is, I know I can reference the same XML config file from 2 different packages. But every time I do that through the wizard, only the last package configurations are placed in the XML file.
Any help is appreciated!
View 8 Replies
View Related
Mar 29, 2008
Hi,
I'm sitting down again to see if I can get configuration files to work. To test, I've set up a simple package that reads a flat file and writes to a table.
I've enabled configurations by clicking the checkbox in the configurations window. I am using an xml file on the local server, and there is an absolute path defined to the file. I also added a password to the xml file so the connection will work. To this point, everything works.
However, when I go into the configuration file and edit it to point to another server, it doesn't pick it up!
That is, when I run my package, it still writes the data to the old server. It's like it's completely ignoring the configuration file and just using the OLE DB connection manager instead.
However, if I delete the OLE DB conn mgr, the package doesn't work.
Please help... this is where I got stuck before.
Thank you
View 9 Replies
View Related
