Reading A .dtsx File's Package Configuration In VB.NET
Jan 10, 2008
So I've seen articles outlining how to execute a package in VB and how to pass variables along to the package, which is great.
But I'm wanting write a VB.NET app that will read the xml in a .dtsx package, looking for the available variables (and datatypes) that need to be passed to the package to configure it properly. Anyone done this? Is it best to use parent package variables? Or another type of configuration?
When looking at the xml of a .dtsx file, I can see a variables section that could be parsed... is there anything letting me know what is required versus optional? How do I know the datatype that I need to pass? Any help would be appreciated...
I have searched on this forum for a similar question but couldn't find it so I apologize if this has been asked. If so, I'd greatly appreciate a link to the question. I have created a custom task and am trying to read an xml package configuration file within my custom task. To be more specific, I have added an ADO.Net Connection to my database onto the package and have generated the appropriate tags within my package's configuration xml file. I'm not seeing the classes I should use to access the configuration file of the package I've created. Does anybody have any ideas how I can accomplish this or a link to a document that might cover the material? Thanks!
I am having trouble with a dtsx package to truncate a table, then insert the contents of a .csv file. The package is being executed off the local filesystem, reading a csv on the same file system, and inserting into a remote SQL 2k5 server. If I run the package alone in BI it will run perfectly, if I implement the package into a console app in visual studio, it will trunc the table, but will not insert any of the data in the csv file. When running from DtExec I recieve the following error on the CSV portion after the table is truncated:
Code: 0xC00470FE Soure: Data Flow Task DTS.Pipeline Description: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for for componenet "Soure - My_File_CSV" (1).
I have tried all the work arounds I can find without any luck. All help will be appreciated.
Started: 5:05:48 PM Error: 2014-08-21 17:05:50.64 Code: 0xC000F427 Source: File System Task Description: To run a SSIS package outside of SQL Server Data Tools you must install File System Task of Integration Services or higher. End Error
I have a dtsx package that is calling another dtsx package, however, if the called upon dtsx package fails with errors or what not, then the calling package does not continue as well. Is there any way to override this such that if the called upon package fails, the downstream actions in that package can stop, but the calling packages downstream actions to continue?
I'd need an help because I'm stucked!! I have to import an Excel file into my DB. The Excel file is made by 2 worksheets but I need only one and inside this worksheet I have to loop through the columns and for each column I define a Data Flow that trasform the data as necessary and then insert into the table.
I started with a "Foreach ADO.NET Schema Rowset Enumerator" with connection=excel file and the schema was set to "Columns" but the loop go also through the worksheet that I don't need..
after 4 hours of tries I'm lost... Someone could give me an advice? ThankX Marina B.
I am trying to create and later read a data file from a package deployed in SSISDB, but it is not reading it while it is successfully creating the file. The same package when run from the file system package, runs successfully. Generating ispac and deploying in SSISDB is running for infinite time. Is it a permission issue?
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.
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 have an SSIS package which is suppose to read this db file and insert it into the SQL 2005 table. But when I execute this package it goes into a loop, reading the file over and over again. The package fails after inserting some 10 million rows ( actual rows are not more then 270,000) giving the error msg 'Not enough space on temporary disk'.
On examing the data transfered into SQL there are duplicate rows.
I also used the import export wizard (thinking there might be some error in Package code) provided by SQL to transfer the data from .db to SQL but it has the same result(goes in a loop).
I would appreciate any help in this problem. Let me know if you have any other questions.
I have about 40 packages in my job. One of the problems that I have encoutered is that I don't know how I could use same XML dtsconfig file for all of my files. Each SSIS calls different file.
The XML dtsconfig file is a replacment for my *.ini file in sql 2000. The dtsconfig file will hold paths to all different files. Each SSIS will take different file name at a runtime.
How can I have all the files paths in one dtsconfig file - and is it even possible to do that in SSIS?
I'm having difficulty configuring a package to look for an xml configuration file that contains values for source/destination connection strings.
The goal is to be able to have a package and its configuration file on one file system and to be able to copy/move the file dtsx and dtsx.config between build/staging/production servers while only having to set the xml value for the source/destination configurations.
In the package, I've created an xml configuration file called zzz.config. I took the absolute path out of the location of the xml file so that I have xml location = zzz.config instead of xml location = e:ssiszzz.config I'm assuming that when it gets to the point of validating the location of that file, it starts by looking in the current directory where the package is found.
All is well as long as I execute the package through the execute package utility by double clicking on the package file.
The problem lies when configuring a job in the sql server agent, it doesn't seem to pick up the xml configuration values because its pointing to the old server and not the current server stored in the xml file. I'm assuming its because the current directory is not the package directory at this point.
How can I get around this? Do I have to move the xml config file (zzz.config) to another directory where the agent will look for?
I know I could configure this as an environment variable but this contradicts the point of having movable packages without any extra tweaks.
The system creates a XML file but when I run the package I get the following error in the output pane. Information: 0x40016041 at FMC_People: The package is attempting to configure from the XML file "L:ProjectsVinciSSISDVLFMC loader ImportFMC Loader ImportFMC Loader ImportJACBE_IF_CONFIG.xml". SSIS package "FMC_People.dtsx" starting. Information: 0x4004300A at Dataprocessing_PEOPLE, DTS.Pipeline: Validation phase is beginning. Error: 0xC0202009 at FMC_People, Connection manager "JACBE_IF": 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.". Error: 0xC020801C at Dataprocessing_PEOPLE, FMC_ARE_PRESENT_destination 1 [22338]: The AcquireConnection method call to the connection manager "JACBE_IF" failed with error code 0xC0202009. Error: 0xC0047017 at Dataprocessing_PEOPLE, DTS.Pipeline: component "FMC_ARE_PRESENT_destination 1" (22338) failed validation and returned error code 0xC020801C. Error: 0xC004700C at Dataprocessing_PEOPLE, DTS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Dataprocessing_PEOPLE: There were errors during task validation. SSIS package "FMC_People.dtsx" finished: Failure.
I don't get it. Where do I go wrong?
I tried the same with a DtsConfig file instead of an XML but to no avail. the way of working as described in BOL and in the book professional SQL SERVER 2005 Integration service seems to me perfectly similar.
does anybody know where is stored information about belonging a certain configuration file to a package? How can I connect a configuration file with an installed package without using SSISDeployment wizard?
I have a bundling package that runs about 20 other packages. It has been working fine for a while but a couple of days ago it fail with the following message,
Error 0x800706BE while loading package file "D:PackagesToradSales.dtsx". The remote procedure call failed.
I´m running the SSIS packages in an 64-bit environment.
I created a simple file system task to copy a file from one location to another on my local system.
I used variables to specify the source and destination paths and now in my configuration file if I configure the values to something else also the package is taking the initial default values and not the values specified in the configuration file.
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
We are facing some issues with SSIS and thought if you could be of some help.
Actually, instead of Deploying our SSIS projects we copy the SSIS files to a different server from where we execute the SSIS Packages. The reason being, we have a Core Version of the application and this is deployed to different markets and subsequent customisations are performed in the market specific version of the Packages. The problem is although we have a Package Configuration Xml File for our Connection object, the first time when we copy the Packages, we have to manually open each of the Package and change the URL of the Xml File for Configuration. I read through some of the articles on the internet and came to know that the Package Configuration does not support relative path and only absolute path is possible.
Can you please share some of your suggestions on the above problem.
I've added an XML package configuration file to my SSIS package and it appears to be working fine.
The XML file is located in the same folder as the solution file.
I've recently added the entire solution to Visual Sourcesafe 2005, and I achieved this by doing the following:
1. Right-clicked on the Project name in the solution explorer and selected "Add to source Control". 2. When prompted, provided my Sourcesafe login credentials and clicked "OK" 3. Accepted the default VSS project name and clicked "OK" 4. Clicked "Yes" to allow VSS to create a new project
Problem: The XML configuration file hasn't be added to sourcesafe along with rest of the solution.
I'm able to add the file manually myself, but this doesn't seem right to me. Am I missing something? Should the config file show as part of the Solution in Visual Studio 2005?
I've used XML package configuration in my packages in order to populate key variables. The configuration String is pointing to a local folder in my machine. After that, I've checked my whole solution into TFS.  I did check the checked in file but could not find the .dtsConfig XML file. The problem occurs when the other teammate checked out this solution from TFS into his own box. When he tried to open the solution, it gives warning (not error though) saying it could not find the package configuration file.machine does not have the same URL I had in my  box.  In situation like this, how can we fix in the multi-developers SSIS environment?
We are trying to create a deployment utility for a solution. The issue we are facing is, we are using a single package configuration file and when we try to build the solution to create the deployment utility, the build process fails saying that the package configuration file already exists. THe reason for this is while trying to build, the utility copies the configation fiel for the packages, it copies for one, but for the second onward, when it tries to copies, it fails saying the file already exists.
Any idea how to overcome this, or else any suggestions how to perform the similar steps to create a deployment utility for a solution in which the packages share a single package configuration file.
I'm fairly new to the SSIS world, and I've recently ported a bunch of dts packages over to SSIS. I'm an ASP.NET developer so I'm very familiar with the capabilities that configuration files give you, and I attempted to set up my solution as follows:
All of my "Data Sources" are at the project level, and added (with the same name) to each package. I wanted to have a single config file that had all of the project-level settings (i.e. connection strings, data file paths, etc). I then have a config for each package with the package level settings - i.e. variables, etc.
The problem becomes that all packages do not use all data sources. This results in an error when I try to open up a package for editing, it complains that it doesn't have a reference to data source XYZ that it is seeing in the configuration file.
Is there any way that I can get around this? If I have a password to a database change, I don't want to have to look through every config file and change it in multiple places.
I have been reading through different posts about how to manage SSIS configuration files across multiple environments. I have seen a few that mention a solution that turns out not to really work (or maybe I€™m doing something wrong); which is what I hope to find out here.
Solutions I am working with: 1: Put the XML Configuration file in the same directory as the SSIS package and only specify the config file name when setting it up in the package. I do not specify a path to the XML Configuration file, just the config file name.
ISSUE: This works when I first create the package and test it without closing down visual studio. Even after deploying the SSIS package and the config file and moving it between environments it works as long as the working directory of the DTExec command is the same directory as the SSIS package and configuration files. The problem comes in after I have closed Visual Studio and opened it again and loaded up the SSIS package. It then gives a bunch of errors stating that it can€™t locate the configuration files€¦. If I could fix this issue, then this would be a solution that I could live with. (I would much rather use a relative path to a different directory but I€™ll live with what I can get). Can anyone solve the issue of Visual Studio 2005 not recognizing the location of the configuration files when only the configuration file name, and not the path is specified?
-- The other odd thing is that I have been able to get this to work consistently with some of my packages but not others. I'm not sure what I did to get those packages to recognize the location of the xml config file.
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 have a SSIS-package with Excel Connection Manager that fails on our x64-platform with error: Error: 2006-12-08 06:46:23.77 Code: 0xC0202009 Source: dpd2_philips Connection manager "Excel Connection Manager" Description: An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2006-12-08 06:46:23.77 Code: 0xC020801C Source: Copy Data from Blad1$ to dpd2 dbo philips Task Excel Source [107] Description: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. End Error
It's works fine on my computer (x86) and if I start the package manually on the x64-server. But I noticed that it runs under 32bit (DTExecUI.exe *32 in taskmanager) when it's started manually.
Any idea? The server use dtexec.exe to start the packages. Maybe it runs under 64bit then?
I understand that I can use dtutil to provide a password to encrypt the package. However, when I run the encrypted package, I need to provide the same password in order to run it. Is there a way that I can encrypt the package but allow anybody to run it without providing the password? That is, I want the package to be encrypted so that nobody can load and modify the code in Visual Studio, but I want people to be able to execute it. Is there a way to do this? Similar to generating a exe file?
SSIS package created from the wizard couldn't be run from the visual studio editor? The run button is disabled. Is it not possible to run from inside editor as well?
I experienced a weird error while deploying my SSIS package. After running the manifest file, i noticed that one of the configuration file's path was not updated in the dtsx file. My solution has 8 packages and almost every package has 2 configuration files. Except 1 file every other config file's path is being updated. Has anybody experieced such a problem?
