SSIS Custom Configuration

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

System Configuration File For Custom Dll Used Script Component In SSIS Packages

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

Custom Package Configuration Functionality

Nov 14, 2007

I've been working for the last few days on some custom "configuration-like" functionality that allows me to store configuration information external from the SSIS packages that need it, without relying on the built-in Configuration features of SSIS. (Of course, if someone wants to tell me how to implement Configuration encryption and a few other features while using the built-in tools, I'm more than eager to hear that too. ) I've never had the need to do this before, and although everything is going relatively smoothly, there are a few things that I'm lacking, and the main one is validation.

Currently in order for me to use my new functionality, I need to set DelayValidation = true on my data flow tasks, or else they will fail validation (and thus fail the package) unless the design-time connection information is valid. I would strongly prefer to leave DelayValidation = False so I can more easily have "deployment sanity checks" without needing to actually execute my packages.

So here is my question:

Is there a mechanism by which I can have my own logic (implemented in a Script Task currently) executed before package validation so that I can assign values to variables which are used by connection managers, so that the end product is that the package passes validation without needing to set DelayValidation = true, and fails validation if the information in the external "configuration store" is not valid for the current deployment environment?

Any tips or tricks would be very much appreciated - thanks in advance! Thanks in advance!

View 7 Replies View Related

Custom Task And Reading Package Configuration

Oct 27, 2006

Hello All,

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!

Jay_G

View 3 Replies View Related

Custom Assembly : Retrieving Data From From Configuration File.

Mar 7, 2006

Hi all,

my problem is that: I do read informations (e.g. connectionString) from configuration file by one custom assembly.
My environment is RS2005, VS.NET 2005 Prof.Edition, WIN2003 Server SP1, .NET Framework 2.0.50727
The scenario is that:
A text box into my report require a function contained into MyCustom.dll. This code do read information from MyCustom.dll.config.

This file contains:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="IS2009cfg" connectionString="data source=servernameinstancename;initial catalog=dbname;user id=xx;password=yy;" />
</connectionStrings>
<appSettings>
<add key="DefaultHeaderLogo" value="This is a test" />
</appSettings>
</configuration>

Using
ConnectionStringSettings conn = ConfigurationManager.ConnectionStrings["IS2009cfg"];
the result is null.

Also using
string defaultHeaderLogo = ConfigurationManager.AppSettings["DefaultHeaderLogo"];
the result is null.

MyCustom.dll and MyCustom.dll.config are located into locations of Report Server and Report Designer, with this Build Events on succesfully build:

rem Copy custom assembly to ReportDesigner location...
copy "...mydeveloplocation...inDebugMyCustom.dll" "C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesMyCustom.dll"
copy "...mydeveloplocation...inDebugMyCustom.dll.config" "C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesMyCustom.dll.config"
rem Copy custom assembly to ReportServer location...
copy "...mydeveloplocation...inDebugMyCustom.dll" "C:Program FilesMicrosoft SQL ServerMSSQL.4Reporting ServicesReportServerinMyCustom.dll"
copy "...mydeveloplocation...inDebugMyCustom.dll.config" "C:Program FilesMicrosoft SQL ServerMSSQL.4Reporting ServicesReportServerinMyCustom.dll.config"

The question is simple: where I do locate configuration file for obtain value during report execution?

Many thank's in advance

Claudio Barca
Turin - Italy

View 2 Replies View Related

SSIS XML Configuration?

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

SSIS Configuration

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

Custom SSIS With ATL Dll

Aug 8, 2006

Hi All,Is it possible to create SSIS custom component with ATL COM dll with MFC support?I'm not familar with C# & VB# languages :-(Regards,Svilen Varbanov

View 2 Replies View Related

[help] SSIS File Configuration (XML)

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

SSIS Package Configuration

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

SSIS Package Configuration.

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

SSIS Package XML Configuration

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

Indirect Configuration In SSIS

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

SSIS Configuration Files

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

Package Configuration In SSIS

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

SSIS Registry Configuration

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

SSIS Configuration Option

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

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 View Related

SSIS Configuration In Different Environments

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

SSIS Custom Task

Nov 19, 2007

Hi there,
I'm still pretty new to SSIS, what I am really battling with is getting a custom task to work.
I have developed a custom task, digitally signed it added it to the Global Assembly Cache, written the UI for it - Pretty much as it is done here: http://technet.microsoft.com/en-us/library/ms136080.aspx
but my problem is that when I drag it from the tool box into the control flow pane, I get this message: "The task user interface specified by type name 'XXXXXXXXXXXXXXXXXXX' could not be loaded.
(Microsoft.DatatransformationServices.Design).

Does anybody have an idea of what I am doing wrong here?

Duane.

View 2 Replies View Related

Custom Object In SSIS

Apr 2, 2007

I wrote a C# class that has a couple of methods that can be called to grab data out of a flat file and import it to a DB. This class was written for a web applicaiton originally. (class has many complex business rules in it)

Now I would like to use this same class in a SSIS package.

Is it possible in a script task to create a new object with my class and use it's methods?

In the end all I want SSIS to do is create my object, call a method in that object and be done.

If I was doing this in DTS, I would have to create an .Net EXE that used my class but I'm hoping to avoid that in SSIS.

Frenchy

View 19 Replies View Related

SSIS Configuration Files - How Do You Use The Things?

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

Passwords In The SSIS Configuration File

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

Configuration Settings In A DLL Called From SSIS

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

How To Do XML Configuration With Different Connection Manager In SSIS

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

SSIS DTS Configuration File Issue

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

SSIS Package Configuration Filter

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

Bizzarre SSIS Configuration Behavior

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

SSIS Package Configuration Issue

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

SSIS Package Configuration ISSUE

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

SSIS XML Configuration Updating At Runtime

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

Displaying Custom Properties For Custom Transformation In Custom UI

Mar 8, 2007

Hi,

I am creating a custom transformation component, and a custom user interface for that component.

In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.

I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.

How do I go about getting the properties for my transformation component listed in this property grid?

I am writing in C#.

View 5 Replies View Related

Create A Custom SSIS Log Provider

Jan 18, 2008

I created a SSIS log provider derived from LogProviderBase, it's appear in my VS2005 and work fine, the only one problem is, there is not a dropdown list of connections in the SSIS Log Configuration column, how can I add it?

http://technet.microsoft.com/en-us/library/ms403361.aspx

thanks

View 6 Replies View Related