.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSIS package is not reading from the configuration file in job step configuration tab

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server
Hi everyone,

I have the following question. I have an SSIS package and it has in SSIS in BIDS 'Enable package configuration' and a full path to a file. The packages works ok and reads from this file.

 If I invoke the same package from sql agent as a job step and apply another configuration file in the job step (in tab COnfigurations), the package still uses the one in BIDS. Shouldn't this new config file overwrite the one from BIDS or?

Thank you


View Complete Post

More Related Resource Links

SSIS package - change Web reference's addres from Configuration file?

Greetings. The SSIS packages provide very nice options to configure them from an XML file, which allows to change credentials, database names etc w/o editing the package. This lets me, say, transfer package from one server to another or from test to production w/o any hassle - just changing the xml. Problem is, i need to acces a web service in my package, and built-in SSIS web service task is useless and just SUCKS. It fails to work even with miscrosoft's own sharepoint web service. So, i have to use scripting task. And scripting task uses "web reference" to access web services, which in turn gives no way to change it's url.  So, if i want to test and then move to production, i'll have to edit my package to change the web reference url from "testserver:5123/mywebservice" to "productionserver:81/webservices/mywebservice". And then change it again to test it again. Etc. Is there a solution to this? A way to programmatically change web reference's url from inside scripting task?

start SSIS package via script task with package configuration via variables



it is possible to start ssis packages with dtexec and set variables as package configuration like this:

dtexec.exe /SQL "\mart1\import_fact_calls" /SERVER "R08" /SET \package.variables[FullImport].Value;True

Now I'm trying to start a package via script task with this code:

Public Sub Main()
    Dim pkg As New Package
    Dim app As New Application
    Dim pkgResults As DTSExecResult

    If Dts.Variables("package").Value <> "" Then
      pkg = app.LoadFromSqlServer(Dts.Variables("package").Value, "R08", "USER", "PASS", Nothing)
      pkgResults = pkg.Execute()
    End If

    Dts.TaskResult = ScriptResults.Success
End Sub

How can I add a package configuration like the one with dtexec to the script code?

I like any ideas, links, docus ... Thanks!

Package configuration file with multiple connection strings


I am using the configuration file to read the connection string in the SSIS packages. In one of my packages i have to use two different connection strings. If i specify the second connection string in the config file, then the current package works well but other packages fails during validation saying the second connection manager does not exist in the package. Is it not possible to have multiple connection string in a single config file?

SSIS Package retains designer setting, not Using Configuration From SQL Server Table.


Hello all,

I am using package configurations to set SSIS package variables from the SQL server Database [SSIS Configurations] table.   I have an environment variable set as my first configuration, to hold my database connection, and then the subsequent configurations are SQL configuration entries and held in the [SSIS Configurations] table.

When testing in BIDs, the connection is OK and the designer shows the correct values have been picked up.  All good!

But, when we change the database value in the [SSIS Configurations] table, the old configuration is still being used in BIDs.

I have checked using Profiler that BIDS is calling and retrieving the correct value from the table but the modified value is not being used in BIDs, instead it is retaining the old value when the package was designed.

How do I force the SSIS package to use the configuration specified in the SQL table?

Thanks, Helen


PowerShell Agent.JobStep for SSIS with one or more configuration file??


Is it possible to script adding a sql agent job for an SSIS package that uses one or more configuration files?  I've spent the better part of the day searching forums and blogs for any examples, but all I can find is just default TSQL or stored proc jobs.  I have most of the script written to pull the appropriate information in, I just do not see how I can specify the configuration files for the job step.  And yes, I've even tried going through Visual Studio accessing that api to see if i can dig out the appropriate object/property to no avail...

Any help would be incredibly appreciated as this would make a huge step forward for our deployment process :)

---- UPDATE ----

I know this sounds odd, but I'm mostly asking because I can't even access a sql 2008 server right now to see what gets put into the step normally for the SSIS Package..I'm assuming all it's really doing in that step is a dtsexec with the appropriate switches, if this is the case, then I can probably just build the command out to pass in multiple configurations...?

Steps by Step document for configuration Merge Replication in 2005

Hi , Can anyone provide link to configure Merge Replication in 2005 with print screen.Also let me know the difference between Transcation level and Merge Replication. regards Vijay  

Error in Step 8 of Share point configuration wizard

HI Team,        I am getting an error in step 8 of Share point Configuration wizard, Its running for ever its running in the step 8 for ever later i need to kill the configuration wizard Please advise Thanks Kumar

using application configuration file in AppData special folder

Hi! So I understand it is a best practice to save the application configuration files under the AppData folder, rather than in Program Files, since UAC will restrict write acces to the Program Files folder, which is fine. Now, saving the configuration file to another location is no problem, using the SaveAs method. However, when I load the application, I expect some settings to be read. From what I know, this is done either using direct calls to the ConfigurationManager class, either initializing a Configuration object. For the first case, a example is this: if (ConfigurationManager.AppSettings["Title"] != null) { this.Title = ConfigurationManager.AppSettings["Title"]; titleTextBox.Text = ConfigurationManager.AppSettings["Title"]; } For the second case, I would use it like this: Configuration config = ConfigurationManager.OpenExeConfiguration( ConfigurationUserLevel.None); string setting = config.AppSettings.Settings["Title"].Value.ToString(); So, if the application runs in the Program Files folder, an exception is thrown when I save the file. However, if I save the file using SaveAs and save it to AppData folder, obviously, when loading, the settings are not loaded from the AppData folder, but from the configuration file existing in the Program Files folder. One way to solve

Problems running a bat file within a SSIS package

I have a ssis package which has 2 tasks, first it builds an excel sheet with data on the server from where I am running the ssis pkg and the second task (a sql process task), runs a bat. the bat file that basically copies the  excel sheet created from the first step to another server. Problem is that the ssis pkg runs succesful when i run it from BIDS, bust when I run is as ajon, it gets hung. It completes the first step, it is the sescond step that is get hung. I noticed that when i run from BIDs, it does come up with a window open file - security warning and says the publisher could not be verified, are you sure you want to run this software. why does it do that ??? the file I am asking to run is a .bat file. it should automatically use the cmd.exe app to run the bat file. Please help !!!   Thanks in advance.

Sharepoint 2010 COnfiguration Wizard Error Step 3 of 10

I am trying to install Sharepoint 2010 on a Windows 2008 R2 Hyper V Instance.  The database is SQL Server 2008 R2.  All them are RTM I am getting this error Failed to Create the configuration Database An exception of type Syste.InvalidCastException was thrown.  Additional exception information: Unable to cast object of type "Microsoft.IdentityModel.Configuration.MicrosoftIdentityModelSection' to type "Microsoft.IdentityModel.Configuration.MicrosoftIdentityModelSection' to diagnose the problem, review the logs at so after reviewing the logs this is what I find Resource retrieved id PostSetupConfigurationFailedEventLog is Configuration of SharePoint Products failed.   This is the error from the event logs Log Name:      Application Source:        SharePoint 2010 Products Configuration Wizard Date:          4/26/2010 11:30:09 PM Event ID:      104 Task Category: None Level:         Error Keywords:      Classic User:          N/A Computer:      jedicouncil.endor.local Description: Failed to create the configuration database. An exception of type System.InvalidCastException was thrown. 

SharePoint configuration wizard fails at 9 th step after installation of KB961749 on MOSS2007 SP1 se

Hi,   System details: 2003 enterprise OS, 2005 SQL server, SharePoint 2007 SP1 with version. Now i m trying to install KB961749 (February 24, 2009 Cumulative Update), installation completed successfully after this while running SharePoint configuration wizard its failed at 9th (last step) in the wizard. Share point version also not updated after this. i tried to upgrade through psconfig command line also, same result. failed at 4th step. Logs gives below info: Not running in high contrast, so we will paint the background with our trademarked image 09/02/2010 14:21:24  11  ERR                      The B2B upgrader timer job failed. Resource retrieved id UpgradeTaskFailConfigSyncDisplayLabel is Failed to upgrade SharePoint Products and Technologies.  Task upgrade has failed with a PostSetupConfigurationTaskException An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown.  Additional exception information: Failed to upgrade SharePoint Products and Technologies. Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown.  Additional exception information: Failed to upgrade SharePoint Products and Technologies. Microsoft.SharePoint.PostSetupConfigur

Placing ..... configuration information in a config file other than app.config

Hi, I'm sorry if this question has been asked/answered before on this forum. I have searched both here and on the web in general, but to no avail as yet... I am running VS2010 and .Net 4, so in theory have all the latest versions of everything at my disposal. I have an executable a.exe. This downcalls into a dll b.dll. b.dll wants to send an MSMQ message using WCF. For cleanness (a.exe is a generic service and knows nothing about WCF) I want to define the client endpoint in b.dll.config. However I am finding that WCF is insisting that the information is held in a.exe.config. The exact problem is that when I create my proxy (i.e. the class which derives from ClientBase<T>), I get the message Could not find endpoint element with name 'XXX' and contract 'YYY' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this name could be found in the client element. From what I have seen I have just described an extremely common problem that people have had with WCF over the years. My questions is basically to find out, given that I have WCF 4 at my disposal, whether there is now a standard approach to this scenario? Things I have tried so far: The obvious one...putting all my config info in a.exe.config. When I do this, everything works file, so I

Error in step 5 - Sharepoint 2010 Configuration Wizard

I can't proced througt the step 5 of the configuration wiz.I have installed in a VM.Please, i need some help.. The error: After installing the Sharepoint Search Server in the wizard. Gives the following exception. Failed to register SharePoint services. An exception of type System.ServiceProcess.TimeoutException was thrown. Additional exception information: Time out has expired and the operation has not been completed. System.ServiceProcess.TimeoutException: Time out has expired and the operation has not been completed. at System.ServiceProcess.ServiceController.WaitForStatus(ServiceControllerStatus desiredStatus, TimeSpan timeout) at Microsoft.SharePoint.Win32.SPAdvApi32.StartService(String strServiceName) at Microsoft.SharePoint.Administration.SPWindowsServiceInstance.Start() at Microsoft.SharePoint.Administration.SPWindowsServiceInstance.Provision(Boolean start) at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.Provision() at Microsoft.SharePoint.PostSetupConfiguration.ServicesTask.InstallServiceInstanceInConfigDB(Boolean provisionTheServiceInstanceToo, String serviceInstanceRegistryKeyName, Object sharepointServiceObject) at Microsoft.SharePoint.PostSetupConfiguration.ServicesTask.InstallServiceInstances(Boolean provisionTheServiceInstancesToo, String serviceRegistryKeyName, Object sharepointServiceObject) at Microsoft.SharePoint.PostSetupConfiguration

SSIS Connection Configuration

Hello,   I have a bunch of packages that use different connections. I would like to put all these connections in the a sql config file and use only the needed connections for that package when executing. Right now when I run a package that doesn't use all the connections in the config table I get errors for all the other connections.   I would like to keep all the connections in one table for easy management, is this possible and even if the package does not use all the connections.   THanks, J

if ssis package writes to excel file that might be open by a user

I have written a package that writes to an excel file to a network folder. Eventually this package will run as a job. What is the best way to handle the situation where someone opens the file or inadvertantly leaving it open while the package is running and and stop the package.? No one needs to modify or write anything to this file after it is finished

SMS reporting server configuration error: The MemoryLimit Element is not a configuration file elemen

Attempting to configure reporting services for SCCM 2007 SP2 R2. Installed SQL Standard 2008 reporting services. Attempting to configure reporting services, but service will not start w/ error: System.ServiceProcess.TimeoutException: Time out has expired and the operation has not been completed.    at System.ServiceProcess.ServiceController.WaitForStatus(ServiceControllerStatus desiredStatus, TimeSpan timeout)    at ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(Boolean start) Digging into Reporting Services logs reveals: rshost!rshost!1900!09/08/2010-14:50:25:: i INFO: CLR runtime is initialized. rshost!rshost!1900!09/08/2010-14:50:25:: i INFO: Derived memory configuration based on physical memory as 16775112 KB appdomainmanager!DefaultDomain!1b54!09/08/2010-14:50:25:: i INFO: Entered managed ServiceMain in DefaultDomain. configmanager!DefaultDomain!5ac!09/08/2010-14:50:26:: e ERROR: Error loading configuration file: The configuration file contains an element that is not valid. The MemoryLimit element is not a configuration file element. library!DefaultDomain!5ac!09/08/2010-14:50:26:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: , Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.&nb

Package Configuration Problems SQL 2008 R2

I have an SSIS package that is using a SQL Server configuration to store connection strings for the other connection managers used by the package.  The connection string for the connection manager used to get the SQL Server configuration data is passed to DTExec when the package is executed.  The SQL configuration sets the values of variables that hold the connection strings and the connection managers use the variables in an expression to set the connection string.  I'm doing this so that I can move the packages from dev to qa then to production.  I also need to have the child packages use the same connection strings as the parent package so each child package has a parent package variable configuration containing the connection strings.  It uses these to set the values of child package variables which are then used to set the connection string property of the connection managers using an expression. I tested my packages by connecting to the development SSIS server, clicking on the package and selecting run package.  I select the connection managers tab so that I can change the connection string of the connection manager that points to the SSIS Configurations table which contains the development connection strings.  This works great. I deployed my packages to the production server and again tested the packages by running them from the pr
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend