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

Encrypting The Configuration Settings In SSIS Package

Apr 24, 2007

Hello Everybody,

I have developed a SSIS package to pull data from a remote SQL Server.

I have specified Database related settings in a dtsconfig file.

I was just wondering if we have any way of encrypting the config file so that only my package can read information out of it.

Or is there any other better way where i can store my database configuration (uid, pwd) so that it is not viewed by anyone.

please help me with this issude, thanks in advance...

Regards,

Sudhir Kesharwani

View 4 Replies View Related

SQL Configuration Settings Being Ignored?

Sep 13, 2006

I am sure I'm being blind here (it wouldn't be the first time - and won't be the last time).

I have a package that retrieves some values from SQL table to allow me to dynically create an email message, so it retrieves To Addresses, From Address, Header, Body from a table, populates variables, and uses expressions to assign those to the SEND MAIL TASK.

That is working (after a bit of head/wall/banging to get my default virtual SMTP server running locally).

Now I am trying to make the SMTP Server Name dynamic. The Property SMTPConnection specifies the SMTP Connection Manager to use, and not the server, so it doesn't seem as though I can configure a property on the SEND MAIL TASK to achieve this.

Now I'm focussing on using SQL Package Configurations to try and drive this. As I see it, just like XML configurations, if you change a value in the configuration source, then when the package runs, it get's that value, and uses it.

I have change the configuration value (outside of the package). I confirm it is actually changed by running a SQL Task that gets the value from the configuration table, and view the value assigned to the variable in watches. The value returned is a different value (in my case I changed it to an invalid servername), and yet the task still runs through successfully, using the previous SMTP servername.

huh?

View 4 Replies View Related

SQL 2012 :: Configuration Manager TCP / IP Settings?

Apr 7, 2015

We changed the IP addresses for several SQL Servers and we're a little confused by what we see in Configuration Manager. The TCP/IP properties "IP Addresses" tab still shows the old IP address, even after a restart. On "Protocol" tab, Listen All is set to "Yes".

We've found one source that says this setting causes the listening on an individual IP to be ignored.

View 0 Replies View Related

How To Get A List Of Configuration Settings For A Database

Jul 20, 2005

Hi,I would like to know how to get a list of configuration settings for adatabase.Is there a table that I can select this info from?Or a file (perhaps an ini file?) of some kind?Things like timeout settings, rollback space, table space, etc.Thanks

View 2 Replies View Related

Confused About Configuration Manager's TCP/IP Settings

Jun 26, 2006

I need another opinion about the SQL Server Configuration Manager's TCP/IP settings...

Recreation steps:
Start, (All) Programs, SQL Server 2005, Configuration Tools, SQL Server Configuration Manager.
Expand the "SQL Server 2005 Network Configuration"
Select the "Protocols for x" where "x" is the named-instance or "MSSQLServer" for default.
On the right-hand pane, right-click "TCP/IP" and select "Properties."
Select the "IP Addresses" tab.

Visually on the screen I have this:
_______________________________________
- IP1
Active            Yes
Enabled           No
IP Address        106.xx.xxx.xx
TCP Dynamic Ports
TCP Port          1433

- IP2
Active            Yes
Enabled           No
IP Address        127.0.0.1 <-loopback
TCP Dynamic Ports
TCP Port          1433

-IPAll
TCP Dynamic Ports
TCP Port          1433
_______________________________________

Questions:
1. If I want to change the listening port, I change all three at the same time?
1a. Does a value in the IPAll section override the individual IPx sections?
2. What does "enabled" mean? How can IP1 and IP2 not be enabled?
3. What does "active" mean? How can something be both active yet not enabled?
(4. Any plans to change the "NULL=False, 0=True" values for the Dynamic Ports?)

View 4 Replies View Related

Target Platform As Part Of Configuration Settings

Jul 20, 2015

We have build configuration setup for each environment that we automatically deploy to (DEV, UA, PROD 1, PROD 2, etc.) We've recently come across a scenario where one environment will now be running a different version of SQL Server than the others.

Is it possible to tie the Target Platform version to a configuration. Such that one configuration could target 2012 while another targets 2014?

View 2 Replies View Related

Need Information On Configuration Settings In Reporting Services

Jan 29, 2008

Hi,

I developed a report in development environment, which will later be deployed to QA, Staging and then Production. This report has more than 10 datasets and if the report is moved through each of the databases, datasource has to be changed for all the datasets. is there anyway that I can do it globally, what i mean is can i set up any configuration files which I can use in QA, Staging and Production.

Any ideas

Thanks,
Priya T

View 4 Replies View Related

Collation Or Local Sensitive Settings Or Other Configuration Properties

Dec 26, 2005

I installed the samples and tutoriales on the same server as SQL Server 2005 and VS 2005 Team Suite.
Living in Belgium, the local settings are:
            language = Duch (Belgium)
            code page = 850
e.g. in the database samples (AdventureWorks database �� AdventureWorks Warehouse database)
my date notation is: 26-12-2005
my number notation is: 1,0013016921998599

all demo samples and tutorials are developed using
            language = English (United States)
            code page 1252
e.g. in SampleCurrencyData.txt
the date notation is: 12/26/2005 00:00:00
the number notation is: 1.0013016921998599

I do not have any problems to test the diferent services (Data Base Engine Services, Analysis Services, reporting & notifications services ..) but I do not succeed to deploy any samples of integration services !

e.g. for Integration Services Tutorial - lesson 1: ��Creating the Project and Basic Package�?
there are no error messages, 1097 rows are processed
but, checking the result in the database, no data is updated in the FactCurrencyRate table of the AdventureWorksDW database !

Why?
-      is the problem related to the local language settings? how to solve this?
-      what is the influence of the code page ? is there any compatibility between 1252 and 850 as code page?
-      Server collation (e.g. Latin_1_General_CI_AS) is reported as key for the Unicode notation for character strings but what about notation of numbers?
-      when to use float data type DT_R4 or DT_R8?
-      I have remarked that the DT_DBTimeStamp is undependent from the source time notation �� Is this correct?
-      what is the difference between DT_Date and DT_DBDate or DT_DBTime, or DT_DBTimeStamp?
-      Is Integration Services dependant of the local settings of the database engine?
-      how to set / modify additional regional properties in a SSIS and SSRS package?
-      how to change the default setting of the Flat File Connection Manager [starting the wizard, the local setting for the language �� Dutch appears and this is OK for me but as codepage appears 1252 (ANSI Latin) and this is not OK as my server code page = 850]?
-      how to work with e.g. US based data as source and Belgium settings for reports?

View 6 Replies View Related

Multiple Environments And Configuration File Settings In Packages.

Aug 10, 2006

Suppose 2 environments on a single machine.

Each environment has different configuration settings....different

databases etc.

All the packages in the first environment have a hardcoded config files

referencing the local drive.

In order to create the second environment do I have to

go into each package and manually change the location of the

hardcoded config files. If I don't it will it not use the config files

from the first environment.

Thanks

View 1 Replies View Related

SSIS Called From Agent Job Not Calling New Child SSIS Package Addition

Oct 29, 2007

I have an SSIS package (TransAgentMaster) that I recently modified to include a call to a child package via the file system. The child package creates a text file. When I run the package in dev studio then the child package/text file is produced.

I then imported the TransAgentMaster as a stored packagesfilesystem package into SQL SSIS and executed the package. The child package produced the text file.

I then ran the SQL Server Agent to see if the child package would work and it did not generate the text file. Thus after updating a SSIS package importing the package into SSIS the job that calls the package will not call the child package. Please not that the TransAgentMaster package calls 7 children packages �� just not my new one.

Any thoughts why the agent will not run the child newly crated childe package?

View 3 Replies View Related

Debugging A CLR Stored Procedure That Is Being Called From An SSIS Package

Mar 3, 2008

I need help debugging a CLR stored procedure that is being called from an SSIS package. I can debug the procedure itself from within Visual Studio by using "Step into stored procedure" from Server Explorer, but really need to debug it as it is being called from SSIS.

View 4 Replies View Related

Debugging A CLR Stored Procedure That Is Being Called From An SSIS Package

Mar 3, 2008

View 3 Replies View Related

Specifying Regional Settings In XmlConfigfile For SSIS Package

Jan 30, 2008

Hi All,

I have been playing around with SSIS for a bit, and i am running into the following problem.

I am trying to import 3 worksheets from an excel file.
The import must be performed on several servers.

I created a config file for this which contains the path to the excel file and the connectionstring, and the LocaleID's of the different tasks.

I have the following tasks :

1. preparation SQL task ( this calls a few sql statements which cleanup certain import tables )
2. data flow task ( this the actual import from the excel file to several tables, this what goes wrong )
3. execute SQL task ( this calls a stored proc which does some filtering the imported data into other tables ).

point 2 is giving problems.

Point 2 will only work when i change my computers regional settings to English.
If i have my settings set to Dutch, it won;t work. Even specifying the english localeID's seem to have no effect.

The problem is a decimal field in the database, and the difference in decimal separator. When i set my regional settings to English the values in the excel are notated like 74.54 which works. And when i have the dutch regional settings the same value in excel is displayed like 74,54. I haven't got a clue how to solve this.

Is there a way to tell the SSIS to not use the regional settings, but use the settings specified in the config file ?
And if this is possible how can i check if the correct settings are used ?

View 1 Replies View Related

SQL 2012 :: SSIS Deployments - Protection Level Settings

Jun 23, 2014

I am currently designing a SSIS package that will migrate data from Syabse to Sql server. If these packages are to be deployed to file system (as this is one time run for migrating data) or to SSIS catalog or to Sql server.

Also, I have not changed the default protection level value in packages during design time and would like to know if I have to change it while handling the deployment using DTutil (yes, i need to deploy & execute using command line utilities).

Please note that I do not have access to PROD, UAT environments and deployment team will use my BAT file that is expected to deploy and execute the packages.

View 3 Replies View Related

Settings For Processing Dimensions And Fact/partitions In SSIS

May 30, 2007

What are the general guildlines for choosing these settings, like paralle vs. sequential, different error configurations? The default selection for processing multiple dimensions is paralle and use default error configuration. But the default for processing multiple partitions is sequential. I cannot find anything helpful other than the definitions from the online help. TIA.

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

[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 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 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 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

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