I have a dynamic flat file I need to import to a table (in the same format as the file). The problem, I'm realizing, is that dynamic column mappings are a pain with SSIS. I have to know the format of the flat file ahead of time, which I won't.
What are my options here? Can package configurations help with this?
I have a package that will be run by many people, basically that take a flat file from one format to another format. The variables I'd need to change are @originalFilePath and @destinationFilePath.
I'm looking at package configurations now, and am wondering what I'm missing. It seems like the configuration settings are static, with an xml file or a sql server table being my most viable options.
What I need is for multiple users to be able to execute the same package with different parameters at the same time. Can anyone guide me in the right direction? I know that through ASP, I could generate XML docs on the fly, but I want to make sure I'm using the best method. What's the best way of going about dynamic configs?
I have around 120 tables. I am using script component to pull the values from oracle stored procedures. I do not want to create 120 source & desitinations in my dataflow. Please advice how can this be possible with one script component and one oledb destination.
I noticed I can add mutiple outputs in 1 script component which makes the script component to work like a dataset (container of different recordsets (tables) ), if I am correct. Can this be redirected to an dynamic oledb connection.
Hello,
I am working on a typical data conversion project where we are migrating data from an old data model to a new data model, using SSIS. Both the DBs are in SQL.
Now we have a situation where say there are 25 source tables and 20 odd target tables.
For transporting data, we are using OLEDB Source & OLEDB Destination transforms. However, each transform maps to one view or one table. As a result, the Data Flow is really messed up with 45+ transforms in it. Is there an elegant way of doing this ? With say just one datasource or maybe fewer transforms?
Thanks,
Satya
When I set up a Flat File, Excel, or XML source, I have to specify the complete file name, in particular the folder where the file exists. I would like to specify the location dynamically, via a variable or property -- but how?
View 4 Replies View RelatedUntil there's an Integration Services 2.0, what custom components would you most like to see examples of? The documentation team is starting work on the 2nd Web refresh of Books Online and SQL Server samples, anticipated for release around April, and may be able to incorporate some requests as samples or BOL topics.
View 26 Replies View RelatedI have a ForEach Loop Container that is running from a Foreach ADO Enumerator with records telling me which companies have records to export. As I loop through I use a data flow task to export the records to Excel, I want to create separate Excel files using some of the parameters from my recordset as parts of the name.
I have DelayValidation=True for my DFT and my Excel Connection Manager, ValidateExternalMetaData=False for my Excel Destination Adapter, and an expression setting the ExcelFilePath and ServerName properties to the dynamic path & file name from variables.
The layout will be the same (i.e. metadata) for each file. The files are just getting broken up by company and service type and I want to use that in naming the files.
I am currently getting the following errors:
[EX_DST New Enrollments File [238]] Error: An OLE DB error has occurred. Error code: 0x80040E37.
[EX_DST New Enrollments File [238]] Error: Opening a rowset for "NewEnrollments$" failed. Check that the object exists in the database.
[DTS.Pipeline] Error: component "EX_DST New Enrollments File" (238) failed the pre-execute phase and returned error code 0xC02020E8.
What do I have to do to create the new Excel File? I thought it would do it when the properties were set. Do I have to create the "table" for the worksheet named "NewEnrollments"? If so, how do I accomplish it.
Thanks in advance.
sk
Hello,
I have made a package to import data from a flat file datasource to our development SQL Server database and am very pleased about how well it works.
Now, it's time to import these data into our pre-production database using the same package. I am still wondering how to parametrize the connection string so that I can switch configuration easily. I specifically want to avoid creating 2 similar packages to do the same job but with different destinations.
I have read about variables and configurations but I am still confused. They say that the package has to be reloaded or something for a change to happen. At best, it would be perfect to have a drop down list somewhere, select the correct database and hit a button to execute the package. I would be very interested in knowing if .NET could be of any use for this kind of job.
Where can I get information about this topic?
Thanks!
Marc Lacoursiere
During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.
I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.
Hi all,
I need one more help!
we can select the dtsconfig file with the Environment variable(indirect configuration);
but i need to select the configuration file at runtime; i've to load the package from the server and apply local configuration file to the package and run in a web server;
Requirements;
1. i have packages with its xml configurations for connection strings alone!
2. i deployed it on the server
3. Trying to execute the packages in a web page onclick event;
We have ASP.net WebPage; in On_Click Event, i have this code;
Application ap = new Application();
Package pk = ap.LoadFromSqlServer("\PROJECT", "itsssqldb", "pmo_package_user", "password", null);
pk.ImportConfigurationFile(@"Packagesdev_staging.dtsConfig");
pk.Execute();
here PROJECT is the Package name; i'm loading the package from the Common Sequel Server 2005; and applying the configuration file to that package and trying to execute this;
but its not using the config file what i mentioned; its returning failure!!
Can you please help me to resolve this issue!,
(The intention of doing this job is to select the configuration file( prod, dev) at runtime)
I want to see if anyone in this community has come across this and has found a solution.
we have 3 different SQL Servers because we are still porting over SQL 2k databases 2k5. We have multiple databases of course. We would like to create some kind of template that would create the connection manager based off of the information in our XML configuration file. We are thinking we would have 2 files for this. One would house the actual server information but omit the database name and username/password. The second will be a list of all the databases with their usernames/passwords for each one.
If we put all of these in the configuration files and do not have a connection for each one, SSIS will throw the error stating it is missing connection managers for those items. We would like to see about maybe specifying the database name and have the connection manager dynamically pull the database specific information into the package. This would also need to be able to handle more then 1 database should there be the need.
I am new to SSIS. It seems pretty nice and extensible but my learning curve on it is horrible as I never programmed DTS packages before.
I hope I am making sense here. Thanks for any help offered!
- Taco
Hey there,
I know that many articles have been written describing configurations for packages but I have yet to have found one that describes if it is possible to use SQL Server configuration type for a package that is to be tested on DEV, then UAT, then PROD boxes.
I would like to know if there's a way to store values in a config table in a database on DEV, UAT and PROD but never have to change anything in the package.
I mean, I wonder if I can pass a parameter that defines the server to go get the configurations from.
The 3 servers will contain a database with a config table named the same on each server but have different configuration values to point them to the proper sources and destinations depending on which server the configuration database resides.
Thank you,
Robin
Please help....
I have different files that are sent from our vendors. Some are TXT and some are XLS. Some will have the same structure. I plan on grouping these together as best as I can.
My main problem is that I would like to go from one source that matches a group of files to a single SQL table. I'm still learning about SSIS and its capabilities. If I can get pointed in the right direction or have an example to work from, that would be great.
I've tried googl'ing to find some step by step, examples, and hints to do this right, but so far I'm at a bit of an impasse.
Thanks,
Dave
I have a question about Package Configurations. I had developed a prototype that has connections defined to 2 DEV SQL Servers and runs fine in DEV. I created a Configuration XML file and changed the XML file settings to point to our 2 TEST SQL Servers. I ran this package without issues. However, I was expecting the 2 SQL Server OLE DB connections within BIDS to point to the TEST Server after the package ran. I still was pointing to DEV. Am I missing something here? Thanks in advance.
View 5 Replies View RelatedHi,
I have a package configuration for my SSIS packages to allow me to dynamically change the connection string of the connections in my packages. This was done so that when I deploy my packages to my development or test server, the packages would use either the development or test server name in the connection string. I have set the configuration up to use an environment variable to store the location of the config file (an xml config file). The package however does not seem to be using the environment variable though. If I change the location of the config file in the variable's value, it still points to the old location. Can someone please help.
Thanks
Hi,
i keep struggling with dtsconfig.
i have 4 packages which all have the same datasource based on the global-datasource.
is there a way i can save that global-datasource, instead of configuring all 4 packages to use a config-file?
Before I started using package configurations, I made several (wrong) assumptions. My primary use for configurations are to move packages between environments (dev, test, prod), so my config files only contain connection information.
My first assumption was I could create 3 different configuration files (XML) with the connection string properties for each environment. Because I use shared data source connections in my packages, these are the only properties I should have to store within my configuration file. Then I use indirect configurations, which I had a system environment variable set up on each server, called SSISConfigPath. On all servers, I have it pointing to a shared folder on our network so us developers can update the configuration files at will, if needed.
Now, in theory if I deploy all of my packages to the SQL Server MSDB on each of the servers, and schedule a job on each server, everything should work, right? Wrong.
First of all, we had to restart the SQL Agent service in order for the package to recognize there is a new system variable (similar to the Indirect Configuration "gotcha" that Jamie blogged about, but in this case, it wasn't a cmd window, but a windows service).
Next, an SSIS package doesn't first read the configuration file, but it tries to validate the connections that were originally stored in the package to begin with before replacing them. This is a problem when the environment you develop on is your local PC (laptop for example), and deploy to production, the SSIS package tries to connect to your laptop PC first, throwing an error because it cannot find it. If it can find your laptop, it succeeds, and only then switches the connection info with what is in the configuration file.
Can this "validation" step be disabled? It seems changing the "Delay Validation" = true on the package doesn't work for connections.
And when you open BIDs to run a package, the "Syncronize Connection Strings" dialog always appears. When you say OK, it doesn't stick and always asks you each time you open the file.
Please let me know what I should be doing, because this doesn't seem to work as flawlessly as I thought it should.
-Kory
Hi,
Within the SSIS package i am currently developing I have a number of variables relating to folder locations. I have added these to a SQL configuration package as i'd like to be able to change these prior to the SSIS package being executed. I plan to launch the package by calling a SQL Agent job from a web page.
On this web page i'd like to be able to change the values in the SQL database so that the package runs with User specified folder locations.
If anyone has done this before i'd appreciate if you could suggest how best to accomplish this. At present i'm thinking of just interogating the created database but as there are no keys etc set up this may not make updating the table easy.
Many thanks,
Grant
I have an SSIS Package that utilizes two Configurations at design time...
1. Environment Variable Configuration sets the root folder variable and subsequently several other variables within the package using expressions (adapted from Jamie Thompson's template package - thanks Jamie)
2. Indirect XML Configuration to set the connection string to the appropriate db.
I've got 3 environments in play.
1. My workstation, using BIDS to design / debug the package. For my PC, the Indirect Config sets the connection to our test server/database.
2. A test server. For the test server, the Indirect Config on the test server sets the connection string to the test database on that server.
3. A production server. For the production server, the Indirect config on the prod server sets the connection string to the prod database on that server.
Problem...
Running the package in BIDS correctly runs against the test db, no problems.
Running the package on the test server in SSMS (either in the Execute Package Utility or using the Agent) without specifying any Configurations on the Configuration tabs, runs fine against the test db.
Running the package on the prod server in SSMS (again, in the Execute Package Utility or using the Agent) without specifying any Configurations on the Configurations tabs runs the package against the test db.
Is this because I'm not specifying the Configuration I have stored on the production server to use the connection string for production?
Curiously, the Indirect Configuration I have setup to read an environment variable to set the root folder (for logging, etc) works fine without having to specify any configurations on the configuration tabs in SSMS. In fact, I don't think you can specify environment variable configurations on these tabs - they open a Browse window to search the filesystem for a config file.
Any clarification anyone can give me regarding these Configuration tabs, indirect configurations, and what their purpose is would be great. I've spent days researching, reading through the two books I have, etc, and there's not much out there on this.
Thanks!
Jamie Pick (not Thompson)
Hi
I created an xml configuration file and specified all the details,i have saved the package with protection level as "dont save sensitive".We are running the package thru autosys.But the package is repeatedly failing,not sure whats wrong with this one.
Pls can someone help me with this??
Thanks in Advance.
SVGP.
Hi all,
I have just set up my first deployment utility for the Integration services package i've been building. I seem to be getting problems with the package Configurations. I added an XML config file so that i could change the values of my SQL connection manager at deployment time. This was so that i could deploy it on both a test environment and the live environment. Any other variables can be changed in code by the calling application. As soon as i added the options for the sql connection manager and enabled package configuration i got errors when running the application:
[Execute SQL Task] Error: Failed to acquire connection "InternalProductionData Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection.
This is before i even deploy the project. If i disable the package configurations everything works as expected. Can anyone help suggest why this might not be working.
Many thanks in advance.
Grant
I'm relatively new to SSIS, but I have read all the info/threads on Package Config. and I'm really confused.
Our production env. is clustered, and we are running multiple sql instances, so the sys admin does not want me to use Env. Variables or XML files for the package configurations (it's too hard for him to maintain the info across multiple instances, etc). So, I am storing the package configurations in SQL.
Just as a simple test, I used the wizard to create the 1 package configuration for production. The configured value as a complete connection string
When I run the package, it completely ignores the package configuration. How do I change the connections on my tasks to read from the package configuration instead of the connection manager they are currently associated with?
TIA,
Mary Jo
I have a problem where in I am trying to set up the package in a new environment.
I just need to change the server to which the data flow task will be populating.
I know we have xml configuration file. Is there any way i can read the files from .NET code and change the server name.
Thanks
Sai
Hello,
I have a solution with a lot of packages, and each of them has configurations set on an SQL Server table. Right now the information in the table is outdated and I'd like to overwrite it with the new values on the properties of each package (and its elements). Is there another (faster) way to do this without having to disable/enable or recreate all configurations manually?
Thanks in advance
I'm still struggling with finding a sound approach to creating configurations that are truly portable.
View 43 Replies View RelatedI have a package that executes several other packages. I have an XML packageconfiguration file to set the servername for the parent package, do I need to just add that packageconfiguration to the parent package and the servername in the XML file will be used until all the packages executed from the parent package are executed or do I need to add that configuration file to each lower level package?
Thanks for any help.
To any Microsoft SSIS developers reading this forum, some requests:
1) do NOT allow a package to run if it has "package configurations" enabled and one or more XML files with configurations cannot be found;
2) update the properties window with the properties read from the "package configurations";
3) allow relative paths in the "package configurations" locations (relative to the package location)
Thank you :-)
Hi All,
May be I am doing something wrong over here, but I have been trying in vain to test out a simple scenario where I can use my Parent Package configurations in my Child package. I have two packages ready and can someone please walk me through this process. Appreciate all help
Thanks
I've been planning on putting all my connection strings for my SSIS packages into package configurations. So far, I've been using XML config files, and these strip out the passwords in connection strings which then have to be retyped every time in notepad.
Has anyone got some better guidance regarding this? Could I use another type of package config to avoid this problem?
My contraints are such that I cannot use integrated security for the packages, so the password MUST be in the connection string. However, I can place the config files in a secure location where only the package and admins have access to them.
Hi all
I am trying to create indirect package configurations so that:
1) My configuration data is stored in a SQL Server table, and
2) The location of that table is stored in an environment variable.
Achieving #1 is easy, but when I hit the "Configuration location is stored in an environment variable", I strike problems. I can specify a environment variable no problems, but there doesn't seem to be anywhere in the documentation to describe the syntax of WHAT should go in that environment variable.
HELP!!!!
Thanks in advance
Jays
Hi,
I'm looing for advice on how to organize my SSIS packages. I'll quickly describe my requiremenst, and then outline how I see the solution.
1. Should work for development in BI Studio. Design time experience should be good, so static schema information should be available in all steps. Packages should run in debug mode inside BI Studio.
2. Should be able to run automated tests (which I gather is difficult. See proposed solution below). Tests should run both on a test machine, in batch mode, and from BI Studio.
3. Should work for deployment.
The packages should be fine-grained. Automated tests will be implemented as SSIS packages that call the packages to be tested via an Execute Package Task. There will be one test package per testcase. A test will look like: [Create Schema] -> [Load test data]->[Execute Package to be tested]->[Verify expected results]->[Teardown].
There will be one master test package that executes all the tests.
For this I need a) some way to vary the connections for the packages depending on whether they are run from a testcase or from the normal control flow. I'm considering making the connection strings configurations based on a parent package variable. Other configuration types strike me as not suitable since developers will want to run packages both as part of normal flow and as testcases on their machines. Is this a viable strategy? Any drawbacks?
b) need to organize testcases some way. I tried putting tests in a subfolder of the project to minimize the clutter, but BI studio shows them at the root node. I tried putting a new project (for containing the tests) in the same folder, and adding the packages (via add existing item), but BI Studio insists of adding copies (!) of the package files, rather than reusing the existing files. Any suggestions?
I welcome any feedback, suggestions of alternate strategies, etc.
/Anders
If the configurations are stored in the database then does the package pick up the configuration as and when required or it a one time pick when the package is executed.
What I plan to do?
When the package is executed, I want to have a script task at the start of the package that will update the configuration table in the database with the values that the package needs to run with. I mean change the values of the configurations of the tasks which will be executed after the script task.
Thanks for your time.
$wapnil
If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?
I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..
My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.
Thanks