Execute Pakage - Configurations Files

Nov 16, 2007



Hi,

I added a configuration file to my pakage for changing the connections strin, but whe I execute the deployed package and in the Execute pakage utility I add the .dtsconfig file and then execute it it never takes the connection string stored in the configuration file. Mi configuration file is:


<?xml version="1.0"?>

<DTSConfiguration>

<DTSConfigurationHeading>

<DTSConfigurationFileInfo GeneratedBy="domuser" GeneratedFromPackageName="PCKG_BH_AFFILLIATIONCONTRACT_INVOICE_DETAIL" GeneratedFromPackageID="{52B4012E-30A7-46E0-B147-EE40DC1B7228}" GeneratedDate="11/15/2007 7:21:09 PM"/>

</DTSConfigurationHeading>

<Configuration ConfiguredType="Property" Path="Package.Connections[BDTEAMSERVER.BeyondHealthCharge].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=BDTEAMSERVER;Initial Catalog=BDTEAMSERVER.BeyondHealthDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>

</Configuration>

<Configuration ConfiguredType="Property" Path="Package.Connections[BDTEAMSERVER.BeyondHealthCharge].Properties[Description]" ValueType="String">

<ConfiguredValue></ConfiguredValue>

</Configuration>

<Configuration ConfiguredType="Property" Path="Package.Connections[BDTEAMSERVER.BeyondHealthCharge].Properties[InitialCatalog]" ValueType="String">

<ConfiguredValue>BeyondHealthCharge</ConfiguredValue>

</Configuration>

<Configuration ConfiguredType="Property" Path="Package.Connections[BDTEAMSERVER.BeyondHealthCharge].Properties[Name]" ValueType="String">

<ConfiguredValue>BDTEAMSERVER.BeyondHealthCharge</ConfiguredValue>

</Configuration>

<Configuration ConfiguredType="Property" Path="Package.Connections[BDTEAMSERVER.BeyondHealthCharge].Properties[Password]" ValueType="String">

<ConfiguredValue></ConfiguredValue>

</Configuration>

<Configuration ConfiguredType="Property" Path="Package.Connections[BDTEAMSERVER.BeyondHealthCharge].Properties[ProtectionLevel]" ValueType="Int32">

<ConfiguredValue>1</ConfiguredValue>

</Configuration>

<Configuration ConfiguredType="Property" Path="Package.Connections[BDTEAMSERVER.BeyondHealthCharge].Properties[RetainSameConnection]" ValueType="Boolean">

<ConfiguredValue>0</ConfiguredValue>

</Configuration>

<Configuration ConfiguredType="Property" Path="Package.Connections[BDTEAMSERVER.BeyondHealthCharge].Properties[ServerName]" ValueType="String">

<ConfiguredValue>BDTEAMSERVER</ConfiguredValue>

</Configuration>

<Configuration ConfiguredType="Property" Path="Package.Connections[BDTEAMSERVER.BeyondHealthCharge].Properties[UserName]" ValueType="String">

<ConfiguredValue></ConfiguredValue>

</Configuration>

</DTSConfiguration>

Can somebody help me?

View 4 Replies


ADVERTISEMENT

Error Using Configurations Files

Jun 4, 2007

I wanted to add all my configurations/vairbles in a config file, so I€™d tried to use the Package Configuration Organizer, to create and enable package configurations€¦
If I want the config to contain information regarding on of my connection manageres, the package fails during test, with the following msg:


Information: 0x40016041 at OrcaleToSQLServer: The package is attempting to configure from the XML file "BDK_LABasedataDTS_config.dtsConfig".
SSIS package "OrcaleToSQLServer.dtsx" starting.
Information: 0x4004300A at Transfer data from Oracle To SQL server, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at OrcaleToSQLServer, Connection manager "XXXXX.xxxx": An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Der opstod en eller flere fejl ved en OLE DB-handling på flere trin. Kontroller alle OLE DB-statusværdier. Handlingen blev ikke udført.".
Error: 0xC020801C at Transfer data from Oracle To SQL server, OLE DB Source to Orcale [1]: The AcquireConnection method call to the connection manager "LASYS.lakig" failed with error code 0xC0202009.
Error: 0xC0047017 at Transfer data from Oracle To SQL server, DTS.Pipeline: component "OLE DB Source to Orcale" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Transfer data from Oracle To SQL server, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Transfer data from Oracle To SQL server: There were errors during task validation.
SSIS package "OrcaleToSQLServer.dtsx" finished: Failure.

Translation Danish line:
Der opstod en eller flere fejl ved en OLE DB-handling pÃ¥ flere trin. Kontroller alle OLE DB-statusværdier. Handlingen blev ikke udført -> One or more errors regarding a OLE DB-action on serveral steps. Check all OLE DB-statevalues€¦ the action was not carried out..

View 3 Replies View Related

2 Configurations Files In The Deployment Package ?

Apr 9, 2007



Hello,



I have two SSIS packages in the same project with, for each one, a (XML) configuration file.



I have created a Deployment Utility file (.SSISDeploymentManifest).



My problem is that, when I execute that utility file, I can't deploy more than one configuration file, the UI only ask for one.



Is anyone can help me ?

Thanks a lot.



Alexis

View 1 Replies View Related

Can You Help With DTS Pakage?

Nov 2, 2005

Hi,

In our travelinsure1 database, I used DTS to create a package which needs to run on a daily base.
The major task is import data from a text file to a database (CMC_test) table tis_StudyUSA_dataEase_test.
But before I do the import, I need to flush the data in tis_StudyUSA_dataEase_test.

So I need to execute" Delete from tis_StudyUSA_dataEase_test" then the import.
But I just don't know how to set up in DTS package to let this statement run first, not after the import.
Otherwise it will just have empty table.

I just start to learn how to use DTS. Any clue will be very helpful.

Betty

View 8 Replies View Related

Pakage Running In Sql Server 2005

Aug 2, 2007

hi friends,

i created the one package for import the data from excel to server 2005

in intellegence services i add the package after deploy

if we run the package (by giving the connection working fine)


BUT I WANT TO RUN THE PACKAGE BY DAILY AUTOMATICALLY(just like a scheduler)


is it possible if possible please give me the coding for auto run a packages

regards
koti

View 1 Replies View Related

Error When Save A Copy Of Pakage In SQL Server

Feb 15, 2007

Dear All,

I just created SSIS in my local computer. It is successfully run if trigger manually. I want the SSIS triggered by SQL_proxy_agent.

So, I try to save the copy of SSIS pakage in SQL Server but there is an error appear:

Error Code 0x80040E09: Execute Permission denied on Object ' sp_dts_getfolder', database 'mdsb', shema 'dbo'



Could anyone clear my doubt.



Thank you.

View 3 Replies View Related

How To Execute .sql Script Files?

Feb 12, 2008



Hi,

I have designed a Contacts application, where I store all the Contacts in a SQL Database.

Now, I am deploying this application using Visual Studio 2005 Setup and Deployment project.


I might need the update the database with additional Columns etc at a later stage.

How, can I update the SQL Database after it has been deployed on a client computer?


Please assist

Thanks

View 4 Replies View Related

Execute List Of Sql Script Files ?

Apr 10, 2008

Is there an "easy" way to execute a number of .sql files without opening and executing each one ?

View 1 Replies View Related

OSQL Command - Execute All Files In A Folder;

Aug 8, 2006

Hi,

How do I execute all the .sql scripts in a folder with OSQL command?

Thanks in advance,

Hari Haran Arulmozhi

View 4 Replies View Related

Execute Package Task And Configuration Files

Nov 25, 2006

Hi all

For selecting different load targets we use package configurations to override the connection parameters in the connection manager.
To organize the different tasks we splittetd our load processes into multiple package files (dtsx) which are then put together into a workflow using "Execute Package Task" component.
With the option /CONFIGFILE its possible to override the configuration file for the package that is called with DTExec. Unfortuanetly it does not override the configuration of subpackages calles by the "Execute Package Task".
Are there some ways to configure subpackages on the command line?

Thomy

View 1 Replies View Related

Capture Files With ForEach Loop And Execute Sql Task

Feb 27, 2008

Hi all,

I'm trying to capture the file name and insert to the Database with Loop Container and Execute Sql task...However when I run, I get error with the input parameter.

In my Sql Task, the parameter mapping:
Variable name: user::variable,
direction: input,
Data Type: Varchar,
parameter name: @xVariable,
parameter size: -1

connectiontype: OleDB
sourcetype: direct input
statement : insert into xtable(xcolumn) values(@xVariable)

Anyone have any other suggestion how to capture the filename and input in the database?

thanks

View 4 Replies View Related

Integration Services :: Unzip Files Using Execute Process Task

Jul 6, 2015

while i am trying to unzip files using execute process task ,getting below error

[Execute Process Task] Error: In Executing "C:Program Files7-Zip7z.exe" "a -tzip D:excel.zip D:unzipfileexcel.xls" at "", The process exit code was "1" while the expected was "0".

Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

i want to know more about unzip and zip files and folders using execute process task.

zip folder:  C:Program Files7-Zip7z.exe
SQL version: SQL server 2008 R2

do not having win rar so please instruct using 7z.its quite interest to work but i don't know to get desired result.

View 6 Replies View Related

SQL Server 2008 :: Use Folder Name As Database From Batch File To Execute Files

Jul 17, 2015

I've been struggling with this issue,

1) Test--FolderName (This Test folder name should use as a database name for below sub folders)

a)Create--Sub Foldername
i)create.sql
b)Alter---Sub FolderName
i)Alter.sql
c)Insert---Sub FolderName
i)Insert.sql

[Code] .....

The scripts need to be run in order. So script one needs to run first folder in that sub folders after that next second folders etc..

Is there a way to create a bat file that automatically runs all these scripts, in order against, the databases they need to?

The databases that they need to run against have the name of the database at the beginning of the name of the folder.

View 0 Replies View Related

Configurations

Jun 15, 2007

Hello, I had a question I thought someone might be able to answer before I test my theory.



If I run an outer package through SQL Server Agent and specify it to use a configuration file, will the children packages it call also use the configuration file? My instinct is no it will not.



If this is in fact the case, would it be considered best practice to just specify the configuration file for each package through an indirect environment variable? Is it possible to set an environment variable on the set values tab within sql server agent?



Then my only concern is how would developers test packages on their machines, should they also set up the same environment variable on their machine?



Any thoughts?

View 7 Replies View Related

Configurations

Apr 24, 2006

I have read many posts about configurations. However, in practice, I cannot solve a problem that is bothering me. I have connection managers (that manage SQL Server 2005 connections) configured in a local package. I have ProtectionLevel = 1 (By the way, where do you state that you want it "DontSaveSensitive", etc?). When I deploy the package to another computer (using the deployment utility) though, I get the error "The AcquireConnection method call to the connection manager ... failed". Could someone tell me, very explicitly, how can I use configurations to solve this problem? Or are there other ways to solve the problem? The problem, of course, is that the connection managers' passwords aren't being migrated from a computer o another. Thanks a lot.

Pedro Martins

View 7 Replies View Related

Transaction Log Configurations

Apr 4, 2004

Hello, everyone:

How to configure transaction log file size, transactional backup interval and log shipping intereval, exactly? Someone told me it depends on the transaction speed. Is it correct? I have an average transaction speed about 10/min., one day, on SQL 2K/W2K. How should set above issues?

Thanks a lot.

ZYT

View 3 Replies View Related

Configurations For Partitions.

Aug 23, 2007

Hi experts,

In SQL Server 2005 database we have partitioned a very big table into 30 partitions each holding few million of records.

Im just curious to know whether there are some configuration related to processors or system hardware in order to benefit from partitioning ? (Ex : If we have multiple processors Whether they need be configured to do a parallel processing ? )

Any real time experience (other than referring links) would be really helpful for me.

Thanks in advance,

Hariarul

View 1 Replies View Related

Package Configurations ...

Feb 6, 2008

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 Related

Package Configurations

Oct 19, 2006

Hi,



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

View 4 Replies View Related

Package Configurations

Feb 12, 2007

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?

View 6 Replies View Related

Package Configurations

Aug 5, 2007

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

View 8 Replies View Related

SQL Server Vs. XML Configurations

Jan 5, 2007

I have been experimenting with both XML and SQL Server Configurations. Management would really like to go with the SQL Server Configurations, but I seem to be having problems with them.

The main goal is to be able to create a configuration for each connection and have every package refer to its specific configuration, therefore in the future we could change say our SA password and only have to update this in a few places rather than for every package.

In order to experiment with the problem I am having I developed 2 identical packages, except one uses SQL Server Configurations and the other uses XML. The configurations save the exact same properties. With the XML package I can go into the configuration file and change say the password and initial catalogue properties and have these changes go into effect. With the SQL Server package these changes do not seem to take place. So I guess what seems to be happening is with the XML package the connectionstring seems to be generated with the other properties that have been saved in the configuration, but in the SQL Server package none of this seems to take place and the connectionstring just stays exactly as it is saved in the configuration file.

I was wondering if I just seem to be missing something or if anybody else has experienced this problem and found a solution.

Any help is much appreciated,

Sam

View 20 Replies View Related

SQL Package Configurations

Nov 15, 2006

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

View 2 Replies View Related

Package Configurations

Mar 25, 2008

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)

View 9 Replies View Related

Pls Help With Package Configurations

Nov 7, 2007



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.

View 6 Replies View Related

Question About Configurations

Jun 29, 2006

I've been reading up on package configurations, but I'm not quite sure how to use them correctly. If I am developing a package, I want to connect to a testing server. Then, when it is working correctly, I want to connect to a production server. Are configurations a helpful way of doing this, or would it only be helpful if in the future, I need to make the packages reference a different server?

View 4 Replies View Related

Deployment - Database Configurations

Nov 14, 2007

Greetings,I've been looking around all morning and can't seem to find a good answer on this so I thought I'd post a message here.I'm using the recommended practice of keeping the ASPNETDB.MDF data separate from my site data. (I have my site data located in separate databases but still keep them in the App_Data directory.)I'm using SQL Server 2005 Express on a Server 2003 box and I've implemented scheduled backups on the databases in the Sql Server.The questions I'm having are:1. Why isn't the ASPNETDB.MDF database getting attached to the server? Is it using User Instances?2. If it is using User Instance then isn't this degrading performance? If so, how to force it to not use User Instance?3. If  User Instance is how it is supposed to work then how are we supposed to do a full backup of the ASPNETDB.MDF database if it isn't attached in the server? (xcopy doesn't seem to work on User Instances because of the dreaded "is being used by another process" error.)Any recommendations, advice, links, or answers to my questions would be greatly appreciated.

View 1 Replies View Related

Client Configurations----immediately

Aug 2, 2000

Hi ,
I am configuring odbc connection at my users place, i tried to change his client configuration , but it is not going in. What was the problem why it is not going in.pls suggest me.
i am having sql7.
--chowdary

View 4 Replies View Related

Security Configurations Report

Mar 6, 2008

Hi all,

I would like to retrive system report that provides the following security configurations for MS SQL

1. Minimum Password Length

2. Initial log-on uses a one time password

3. Password composition (e.g., alpha/numeric characters, not words in

dictionary)

4. Frequency of forced password changes (password expiration/aging)

5. The number of unsuccessful log-on attempts allowed before lockout

6. Ability of users to assign their own passwords

7. Number of passwords that must be used prior to using a password again

(password history)

8. Idle session time-out

9. Logging of unsuccessful login attempts

do i have to script it or is there any alternate option

thanx in advance

View 4 Replies View Related

Database File Configurations

Apr 3, 2012

We have been given a new Database server with SAN drives attached for storage. We will see the SAN as 4 separate drives on the server (D,E,F,G). Drives D and E will be a 7 disk array using RAID 5. F and G will be s sperate 7 disk array using RAID 5 also.how best to configure our databases on these drives. I was thinking of splitting the systems db's, user db's, indexes and logs like this:

D: System DBs
E: User DBs
F: Indexes
G: Logs

I wanted to keep data and indexes seperate as well as data and logs seperate. I'm not sure if it makes sense to keep the system dbs on there own filegroup or not.

View 7 Replies View Related

SSIS And Dynamic Configurations

Oct 9, 2007

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

View 5 Replies View Related

Package Configurations Problem

Aug 30, 2006

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

View 36 Replies View Related

Package Configurations Stored In SQL

Jan 3, 2008

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


View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved