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

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

Execute SQL task and Save Package Configuration wizard

Posted By:      Posted Date: September 26, 2010    Points: 0   Category :Sql Server

Hello Experts,

I am a great fan of using Package configuration in SSIS to ease migration. I am stumped when it comes to sql execute task. We have few execute sql task that requires selecting/updating across mutliple databases in the query. Currently, I am using 4 part name(server.database.schema.table) in query. Doesn't this actualy against theme of using package configuration in SSIS. If I hard code server and database name, I will have to edit each SSIS package post migration to production.

What options do I have? Can you please help this rookie?




View Complete Post

More Related Resource Links

Execute Package Task password issue

I have a parent and child package. Both are stored on the file system and both are password protected. My problem is that when I call the child package from the parent using execute package task, I get prompted for the child package's password. Now the PackagePassword property of the Execute Package Task is supposed to take care of that. But for some reason the password stored in the Execute Package Task is not getting passed to the child package.  I've re-entered the PackPassword property several times, but it did not help.  I even tried loading the password from a config file, but that did not make any difference.  Can anyone help?

Implementing Transaction in SSIS Package - [Execute SQL Task] Error: Failed to acquire connection "<

I have a simple SSIS package with three "Execute SQL Tasks". I am using ADO.Net Connection to execute SPs on a DB server. When I execute this package It works fine. So far so good. Now, I need to implement transation on this package. And problem starts now onwards. When I try to execute package after setting TransationOption = Required for the Sequence container which contains all the tasks, I get following error. [Execute SQL Task] Error: Failed to acquire connection "NYCDB0008.Export". Connection may not be configured correctly or you may not have the right permissions on this connection. "NYCDB0008.Export" is the name of the ADO.Net connection. I have been hunting for any solution but all in vain. I have tried changing all DTC settings on the dev as well as Database server. Please respond if anyone has any solution. Thanks! Anand

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!

SSIS Package fails with Execute Process Task when run from SQL Agent Job


I have an SSIS 2008 package running on Win 2008 64-bit server. The package has an Execute Process Task that runs a net use command using cmd.exe to make a connection.

This runs successfully when tested from the command prompt and runs successfully when the package is run in Visual Studio 2008. However, when the package is called from a SQLAgent job the package fails with the error (actual servernames and passwords removed):

Failing Task Name = EPT Connect mapped drive Error Code = -1073573551 Error Detail = In Executing "C:\WINNT\system32\cmd.exe" "/C net use
\\servername\foldername /USER:userid password" at "", The process exit code was "1" while the expected was "0".

I have determined that a successfull connection should provide a exit code of 0.

The job runs under a proxy account but as explained when the package has run from within VS 2008 it was also tested to run under the proxy account and is successful.

It is not a 64-bit issue as I have tested it by running the package using the 32-bit version of dtexec.exe and the same problem occurs.

Also I have found that by running a simple command process within the Execute Process Task such as dir *.* it is successful when run from the job indicating that it is in fact something related to the net use command itself when run from

Execute SSIS Package PART based on Some Condition

Hello, Is it possible to execute part of the Package based some condition? (Like If var1 = true, then execute this block). If Yes, what control flow I should use. Please advse. I will have a Package Variable and if that variable is True then some part should execute. REst all should execute every time the package is called. Like if I will have "2 Sequence Container" in my control flow, the first one should execute every time, but 2nd one only if the variable value = True. Thanks, Prabhat

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

Execute an SSIS package from ASP.net (C#) - my package keeps failing. Help! - Works on localhost but

I'm looking for any and all information on how to execute an SSIS package from my asp.net web site. I have written the call and when I run the application from (Localhost) it works, but when I run it from the server it does not.Here is my code to call and execute the package: public string ProcessFiles(int ClientID, string FileName) { //System.Security.Principal.WindowsImpersonationContext impersonationcontext; //impersonationcontext = ((System.Security.Principal.WindowsIdentity)ServiceContext.User.Identity).Impersonate(); string packagePath = "\\\\\\Customer File Import.dtsx"; Microsoft.SqlServer.Dts.Runtime.Application app; Microsoft.SqlServer.Dts.Runtime.Package pkg; Microsoft.SqlServer.Dts.Runtime.Variables vars; Application integrationServices = new Application(); DTSExecResult result; FileName = FileName.Substring(FileName.Length - 25 , 25); app = new Microsoft.SqlServer.Dts.Runtime.Application(); pkg = app.LoadPackage(packagePath, null); //pkg = integrationServices.LoadFromSqlServer("Customer File Import.dtsx", "", "healthtech\\brian.montfort", "Nightdragon8", null); vars = pkg.Variables; if (pkg.Variables.Contains("

Execute Process task - On Error (i want to override the error message)

Hi, In my package I am calling one exe to copy the files from local folders to SFTP. I am using a execute process task and I am passing the parameters like (address, username, password,....). The exe and syntax are given below. SFTPCopier.exe <<machinename>> <<username>> <<password> <<local directory path>> The problem is when the exe is failing to copy the files to SFTP due to some reason, the SSIS package is outputting the entire command in the log information (including the username & password). I dont want to write the password in the log information. I want to remove that password from the log information when an error occured in the execute process task. Can some one please tell me the way to override the message? Can I put a on error handler for that execute process task and use a script task to modify the "ErrorDescription" variable? Will that works? Thanks in advance.  

Execute SSIS package stored in Database - From Stored Procedure

Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 

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. 

Configuration failed when running wizard on 2010 RC1

Tried running with another config database name but it still failed.   dialog box appears , stating system.invalidcastexception thrownany ideas would be appreciated.  We uninstalled the BETA first and are worried that maybe something remained causing this.                       Leaving function TaskCommon.TryGetWssVersion4ConnectionStringExists02/11/2010 15:06:27  9  INF                              DID NOT discover a v4 connection string and SPFarm.Local indicates that this machine is not joined.02/11/2010 15:06:27  9  INF                              Entering function Farm.Clear02/11/2010 15:06:27  9  INF                                Entering function CentralAdminServiceInstance.Reset02/11/2010 15:06:27  9  INF        &nb

Execute SSIS PAckage on Remote Server

I've seen several posts pertaining to this topic, but none of them covers what I'm having an issue with. I'm trying to create a "master job server". Essentially, I want to control all maintenance jobs from one central location. I've created SSIS packages that handle full backups, transaction log backups, index rebuilding, etc. on all of my production boxes. I've stored those packages in SQL Server. What I'd like to do now is create a separate job for each of those tasks on one central SQL Server and run those packages from there. I've been successful in creating a test job. The only step in the job is an SSIS PAckage execution step. The step successfully found the package I want to execute on the target server. When the job ran, I got the follwoing error: The job failed.  Unable to determine if the owner (ADMINSYS\arobinsf) of job SFO2PQE03 - Database Backup (FULL) has server access (reason: Could not obtain information about Windows NT group/user 'ADMINSYS\arobinsf', error code 0x2751. [SQLSTATE 42000] (Error 15404)). I want to make sure this job is executing on the remote server and not trying to execute locally. Is there something I'm missing in the set up of this job? I should also mention that the SSIS packages were created in SQL2K5, but I'm executing them from SSMS for SQL2K8. Any help would be appreciated! Thanks! A. M. Robinson

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

execute sql task vs data flow task

My manager has asked me to use linked server to connect to ORACLE database rather then oledb   my source db is ORACLE and my Distination is SQL server 2005 ( in 64 bit  windows server) I am planning to write this query in an execute SQL task, INSERT  dbo.MV_MAX (PERIOD                                 ,PRODUCTGROUPID                                 ,PRODUCTID                                 ,SUBPRODUCTID                                 ,HLBER_ID                                 ,BE_ID                                 ,ENTITYID                             &n

create ssis package with muli task

Hi Friends, Pls solve my following query with example. now i am doing project using ssis 2005. in that 1. import data from multiple resources like .xls, .xml,db 2.Read only file path from created meta data table like dil_table_met (Filetype=.xls,filepath=c:\..) 3.In that meta table, i want to read only file path and check file path which filetype like .xls or .xml and then import to excel source and to db destination , the same via for all file type 4. next read data from resources import correct data into correct table and wrong data into error table 5.for all these above condition are in loop.   pls give a example for this its very urgent pls pls help me out for thisR.Vinothraja

How to get values from a multi row set in case of Execute SQL task?

Hi All, I'm testing the Execute SQL task, when I set the ResultSet to Full row set an try to retrieve those set into a variable, the package runs well but I' m not able to extract what is inside the record. If I try to debug the package to explore the data. All that I recieve is _ComObject as type, System._ComObject as value, I know (if I'm not wrong) that this _ComObject is the box that contains the rows though the values the question is how to extract those variables. For example, I can say how to enumerate the results sets using a ForEach container? Thank you The complexity resides in the simplicity

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