Package Configuration For Generic Pathnames And Specific Filenames
Apr 3, 2007
It's very easy to make a generic xml configuration file for the connection to the database for all packages my project contains. The connectionstring is the same for all these packages
Now I want to do the same for all flat file sources I have in my project where the connectionstring is not the same for all packages.
For example I use the following flat files in my project:
c:AAA.txt
c:BBB.txt
c:CCC.txt
If I move the packages to the production database the flat file sources are located in another directory.
So in fact for the flat file sources there is a generic part for all files (in this case 'c: ') and a specific part ('AAA.txt', 'BBB.txt' and 'CCC.txt').
Can I indicate this in the package configurations sections? And how?
We are building an application with a database that contains Jobs. These Jobs have properties like Name, Code etc. and some custom properties, definable by the application admin. For bulk import of Jobs, we want to allow the import of an Excel sheet with the columns Name, Code and a variable amount of columns. If the header names of these columns in the Excel sheet match the name of a custom property in the system we want to add the value of that cell into the database as property value.
In our Data Flow of our Import Package in SSIS we added an Excel Source that points to a test excel sheet with the Name and Code columns and €“ for this example - 3 custom property columns (Area, Department, Job Family). When we configure the Excel Source in the Excel Source Editor, we have the option to select the Columns from the Available External Columns table. But here lays the problem, we do not know at design time, what custom property columns to expect. We DO expect the Name and Code columns, but the rest is uncertain at design-time.
That raises the question: Is there some way to select all of any incoming columns (something like a SELECT * in T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is being generated at design-time would need to be updated at run-time to reflect the variability of the columns that might be encountered while reading the excel sheet.
Then, we thought, we could add a Script Component to our data flow that passes some kind of DataSet (or DataReader) in which we can walk through the columns ourselves? But then still, we miss the option to include ANY of the columns while reading an Excel sheet (or any other datasource by the looks of it)
We are aware of the option of optional columns in combination with the RaggedRight option, but it seems that we would have to put all of the columns of a row in just one column and then extract all the columns later with Derived Columns. But then, since the source import file is being prepared by an application admin, we want don€™t want to burden him with this horrendous task of putting everything in one column.
We would like to have some way of iterating through all the columns, either in a Script Component or maybe with a Pivot/Unpivot mechanism.
Does anyone have any suggestions? Are there other options we should have considered?
I need to export multiple tables from a database to multiple csv files (one for each table).
Rather than use SSIS and have multiple OLEDB sources and destinations (one for each table), is there a way to have a generic package that will export all the tables in the database ?
One way I can see is to use BCP in a loop - with the loop powered by a select statement that links to something like sys.tables etc, (or another table that i prepped with just the tables I want if I dont want them all).
i.e I would use a stored procedure that uses BCP (called via XPcmdShell) - so not via SSIS - although I could wrap up the whole thing in SSIS - but there is no realy need.
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
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]
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.
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.
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?
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.
I would like to now how can I check if a specific package is running now, at the moment.
the reason I need it is because I enable in a web page to run ssis package using web servise. I would like to be sure that no more than 1 person is starting the package until it completes.
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.
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.
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?
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
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.
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.
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?
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.
I am trying to import an XLS to a table in my database. Sounds simple, but the import should be an UPDATE, not an INSERT. In other words, I want to transfer data from the XLS sheet, to specific field in my database.
Let me explain more clearly.
I have an XLS sheet with a manager ID and a employee ID. The table in the database is already populated with all employee ID's but the field manager ID is still empty. Every employee has a different manager.
The import should place the manager ID's in the fields where the employee ID is the same as in the XLS sheet.
I need to be able to call a SSIS package with a specific parameter for one of the variables. Basically the package needs to be triggered from an application (.NET, C#) with a specific value.
The package is stored in the SQL Server (in Stored packages).
I have seen the article on dtexec and the SET flag, however we do not want to turn on xp_cmdshell on the SQL server due to the security implications, and that seems to be the only way to call dtexec
I have seen another article that says you can have a job invoke a package then have a specific user granted permission to the job, but that doesn't address how to set the parameter/variable for the package.
There are actually some other steps that technically need to run using the same parameter, so ideally if I could have a stored procedure call the SSIS package, then the subsequent toher stored procedures using the sent in parameter that would be great. I just can't figure out how to do this securely (as xp_cmdshell is not considered secure).
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?
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?
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
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.
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.
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.
Am I missing something, I can't find any way to specific the XML configuration file when I try and run my package in Debug of BIDS. My OLE DB connections are all failing due to a lack of a password. I can set the password in BIDS but its forgotten as soon as I start Debug or build the solution. The only way I seem to be able to run my package (now that I have enabled configurations) is to de-select the enable configurations box.
I've tried putting a configuartion file in the Deployment folder, the bin folder and the project root folder all to the same result.
When I try the option to store package configuration into a SQL Server table, it keeps asking me for a filter and I dont' see any options on the drop down list. How do I set this up? I know how to set the package configuration in an XML file and that worked pretty OK for me. But there is now a need to store the package configuration in a SQL Server table now. Please help me with this problem.
