Click here to monitor SSC
  • Av rating:
  • Total votes: 169
  • Total comments: 59
Nigel Rivett

Executing SSIS Packages

30 August 2006

Executing all SSIS packages in a folder: three methods

A common requirement is to execute several SSIS packages as part of one logical process. This is especially true when one is transferring existing data from an external system.

If the indivisual packages are placed in a folder and named in such a way that a batch can be executed via a wildcard filemask, then they can be altered without changing any code, and can be used for different processes by changing the filemask. This article presents three different methods of executing all packages in a folder:

  1. SSIS control package
  2. Stored procedure
  3. DOS batch file

The methods are of differing complexity and flexibility, the SSIS package being by far the most difficult to code, and the DOS command being by far the simplest but also the least flexible.

The folder that contains the packages is d:\TestPackages\.

All the packages to be run have names of the format t1…….dtsx.

SSIS control package

For this we create a package that uses a for each loop task to call an execute package task for all packages in the folder.

The first task is to create an SSIS package with a Foreach Loop container that will loop round the packages in the folder, setting a variable called "PackageToRun" to the file name for each package.

  1. Load Business Intelligence Development Studio and start a SSIS project.
  2. Create a new package.
  3. Add a Foreach Loop container to the package.
  4. Right-click on the Foreach Loop container and select Edit.
  5. Click on Collection.
  6. Set the Enumerator to Foreach File Enumerator.
  7. In the Enumerator configuration:
    1. Set Folder to "d:\TestPackages\"
    2. Set Files to "t1*.dtsx"
    3. Under Retrieve file name select Fully qualified.
  8. Click OK.

  1. Click on Variable Mappings.
  2. Click on the Variable drop-down list and select New Variable.
  3. Set Name to PackageToRun.
  4. Click OK.

Next, we need to add the Execute Package task to the Foreach Loop container so that this task will be executed for each package that we wish to run. We then set the variable value to be the name of the package to be executed by the ExecutePackage task.

  1. Drag an Execute Package task into the Foreach Loop container.
  2. Right-click on the Execute Package task and select Edit.
  3. Select Package.
  4. Set Location to File system

  1. Click on the Connection drop-down list and select <New connection…>.
  2. Set the File to an existing package.

  1. Click OK to save the connection.
  2. Click OK to complete the Execute Package task configuration.

Finally, we configure the connection to use the variable package name:

  1. Right-click on the connection and select Properties.
  2. In the Properties window change the name to PackageToRunConnection.

Note: this will automatically update the connection name in the Execute Package Task.

  1. Select Expressions and add a new expression.
  2. In the property drop-down list select ConnectionString.
  3. Click on the Expression Editor button.
  4. From the Variables tree drag @[User::PackageToRun] into the Expression window.

  1. Click OK twice, to save the expression.

The package should now look like this:

If you now run the package, it will execute each package in the folder with the correct filename mask.

Stored Procedure

This involves the following steps:

  1. Retrieve a list of the package names to run from the folder
  2. Loop through each package name
  3. Execute each package

First of all, we need to get a list of all packages in the folder. The following code executes the DOS command dir /B d:\TestPackages\ t1*.dtsx and places the output in the temporary table #dir. The DOS command gets a list of the filenames in the folder which satisfy the filemask.

Create table #dir (Filename varchar(1000))
Insert #dir
Exec master..xp_cmdshell 'dir /B d:\TestPackages\ t1*.dtsx'
delete #dir where Filename is null or Filename like '%not found%'

The following is a useful technique for looping through values in a table and here we use it to loop through the filenames in #dir:

Declare @Filename varchar(1000)
Select @Filename = ''
While @Filename < (select max(Filename) from #dir)
Begin
   Select @Filename = min(Filename) from #dir where Filename > @Filename
end

Now we just need to create the command to execute the package:

Declare @cmd varchar(1000)
select @cmd = 'dtexec /F " d:\TestPackages\' + @FileName + '''
exec master..xp_cmdshell @cmd

This takes the package name and uses the dtexec command to execute it. Putting this all together, we get:

Declare @Filename varchar(1000)

Declare @cmd varchar(1000)

Create table #dir (Filename varchar(1000))

Insert #dir

Exec master..xp_cmdshell 'dir /B d:\TestPackages\t1*.dtsx'

delete #dir where Filename is null or Filename like '%not found%'

Select @Filename = ''

While @Filename < (select max(Filename) from #dir)

Begin

   Select @Filename =  min(Filename) from #dir where Filename > @Filename

   select  @cmd =  'dtexec /F "d:\TestPackages\' + @Filename + '"'

   exec master..xp_cmdshell @cmd

end

drop table #dir

The above completes the code needed for the requirement but to simplify the implementation I like to keep a utility stored procedure which calls another stored procedure for each file (SSIS package) in a folder. The following utility stored procedure, ProcessAllFilesInDir, uses the above techniques to obtain a list of files from a folder and calls another stored procedure for each one. The file path and mask are input as parameters together with the @ProcSP parameter that defines the stored procedure to be called for each file (in this case this will be the ExecutePackage procedure that executes each SSIS package).

Create procedure [dbo].[s_ProcessAllFilesInDir]

@FilePath varchar(1000) ,

@FileMaskvarchar(100) ,

@ProcSp varchar(128)

as

   set nocount on

  

declare @File varchar(128) ,

      @MaxFile varchar(128) ,

      @cmd     varchar(2000)

  

   create table #Dir (s varchar(8000))

  

   select @cmd = 'dir /B ' + @FilePath + @FileMask

   insert #Dir exec master..xp_cmdshell @cmd

  

   delete #Dir where s is null or s like '%not found%'

  

   select @File = '', @MaxFile = max(s) from #Dir

   while @File < @MaxFile

   begin

      select @File = min(s) from #Dir where s > @File

     

      select @cmd = @ProcSp + ' ''' + @FilePath + ''' , ''' + @File + ''''

     

      exec (@cmd)

     

   end

   drop table #Dir

go

With this, the only coding needed is to create the procedure to execute the package and to call it. The ExecutePackage procedure is as follows:

Create procedure [dbo].[s_ExecutePackage]

@FilePath   varchar(1000) ,

@Filename   varchar(128)

as

Declare @cmd varchar(1000)

   select @cmd =  'dtexec /F "' + @FilePath + @Filename + '"'

   exec master..xp_cmdshell @cmd

go

And the call:

Exec [dbo].[s_ProcessAllFilesInDir]

   @FilePath = 'd:\TestPackages\' ,

   @FileMask = 't1*.dtsx' ,

   @ProcSp = '[dbo].[s_ExecutePackage]'

DOS batch file

The DOS command required is dtexec /F "d:\TestPackages\mypackagename" as above. We can make use of a DOS For loop to execute this command for all files in a folder:

for %1 in (d:\TestPackages\t1*.dtsx) do dtexec /F "%1"

This uses the for loop construct:

  • (d:\TestPackages\t1*.dtsx) returns a list of the files which meet the path (similar to a dir command).
  • For %1 in (d:\TestPackages\t1*.dtsx) loops through the file list setting the variable %1 to the filename.
  • Do dtexec /F % executes dtexec /F with each filename in turn.

Note that you can place this command in a bat file to execute.

Summary

We have introduced three different methods of executing all packages in a folder. The methods are of differing complexity, the SSIS control package being by far the most difficult to code, and the DOS command by far the simplest.

There are a few things missing from the solutions presented which would (or should) prevent them from being used in a production system. At a minimum this should include logging and error handling. The start and end time of each package execution should be logged by the control process and also errors should be detected and logged.

At first sight, it may seem that the DOS command is the one that we should be using, but this method loses a lot of flexibility. There is much you can do with batch files, but not many people have skills in writing such code beyond very simple commands, so you can quickly end up with an un-maintainable system.

The stored procedure solution gives a lot of flexibility – it is easy to add logging and pre/post processing to the process. However, it does require an installation of SQL Server. Although this process would usually be run via SQL Server, it may sometimes need to be run on a client machine (or not involve SQL Server at all); this is quite difficult with a stored procedure as it is server based whereas the other two solutions are client applications.

The SSIS solution keeps the process within SSIS, so you can take advantage of the integration of package processing, e.g. inheritance of properties from the calling package, development and debugging of packages, within the development studio.

Overall, none of these solutions is very difficult to implement, so the decision which to use will probably depend on the experience of the developer, how much flexibility is required, and the environment in which it is to be run.

Nigel Rivett

Author profile:

Nigel spent his formative years working in assembler on IBM Series/1 but retrained in VB when that went out of fashion. He soon realised how little relational database expertise existed in most companies and so started to spend most of his time working on that. He now sticks to architecture, release control / IT processes, SQL Server, DTS, SSIS, and access methods in VB/ASP/.NET/Crystal Reports/reporting services. He has been involved with SQL Server for about 10 years from v4.2 to v2005, and was awarded Microsoft MVP status in 2003. He tries to stay away from anything presentation oriented (see www.mindsdoor.net). Theoretically he is semi-retired but seems to keep being offered potentially interesting work.

Search for other articles by Nigel Rivett

Rate this article:   Avg rating: from a total of 169 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: One more way of executing the packages
Posted by: Anonymous (not signed in)
Posted on: Thursday, August 31, 2006 at 11:56 PM
Message: Hi,

The information provided is pretty good and in -detail.An addition to the list of methods of package execution might be using a scheduler task where in the packages can be put on a schedule based on the order by which they have to be executed. So with this approach we can even define the order of the package execution for those which are interdependant.

Thanks & Regards,
Vivek S

Subject: re: One more way of executing the packages
Posted by: nigelrivett (view profile)
Posted on: Friday, September 01, 2006 at 4:30 AM
Message: If that was important I would probably add the entries to a table with a sequence number and use the stored procedure (probably) or an SSIS loop task to execute them. It could also be sequenced by the package name.
As I mentioned you would probably need to log the result of the execution and a table with the package names would be a good place to hold the last start and end execution times anyway.

The problem with adding the individual packages manually to a schedule is that if there are a lot it becomes difficult to maintain. You could create the schedule from the file list but then why not just execute from that list.

Subject: One more way of executing the packages
Posted by: Anonymous (not signed in)
Posted on: Tuesday, October 03, 2006 at 12:57 AM
Message: %1 is a parameter (first) passed to a batch file. The usual syntax is %a not %1. Also when in a batch file the %a needs to be a %%a.

regards

Subject: Dynamic SSIS packages
Posted by: Anonymous (not signed in)
Posted on: Wednesday, October 04, 2006 at 9:24 AM
Message: How do you dynamically (programatically) create and execute an SSIS package on the fly from a C#.NET web app.? This was possible with the old DTS, but since the object model has changed in SQL 2005 and the environment/tool have changed, now it seems hard to find a working example on this topic. Any hint on this issue?

Subject: What if your packages needs any variables to be set to execute correctly
Posted by: Anonymous (not signed in)
Posted on: Friday, October 13, 2006 at 5:02 AM
Message: What if your packages needs any variables to be set to execute correctly, how you will pass variables in your methods?

Thanks

Subject: What about SQL security and variables?
Posted by: jpazgier (view profile)
Posted on: Friday, October 13, 2006 at 5:15 AM
Message: Your methods for execution SSIS packages are nice but what if your SQL server security does not allow to use command shell from SQL? One method is gone. What if you have to pass parameters/variables to your package how you find what to pass? Do you use XML configuration files in this case? Do you have any ideas how to solve this problem.

Subject: Nigel Rivett you are good: as always. Thank you
Posted by: Anonymous (not signed in)
Posted on: Friday, October 13, 2006 at 5:58 AM
Message:

Subject: Executing all SSIS packages in a folder: three methods
Posted by: Anonymous (not signed in)
Posted on: Friday, October 13, 2006 at 9:58 AM
Message: This is great stuff!
Thanks...I found it just when I needed it!

...Bryan H

Subject: Excellent
Posted by: Anonymous (not signed in)
Posted on: Thursday, November 02, 2006 at 2:02 PM
Message: Thanks. This was a big help.

Subject: Re: Dynamic SSIS packages
Posted by: Anonymous (not signed in)
Posted on: Wednesday, November 08, 2006 at 9:12 AM
Message:

Hi,

Use Package Configurations.

Click in a free area on package and select "Package configuration" in SSIS menu.

Good Luck,

Landry 


Subject: Alternate way?
Posted by: Anonymous (not signed in)
Posted on: Thursday, November 30, 2006 at 1:36 PM
Message: Thanks, however in my organization all the SSIS packages are stored in MSDB database and not as a File system.
How do i execute the set of packages in this case?

Subject: re: Alternate way?
Posted by: nigelrivett (view profile)
Posted on: Tuesday, December 12, 2006 at 9:47 AM
Message: In ssis the for each loop enumerator allows you to loop through a resultset. You can query for the package names then execute them.

Using a stored procedure just populate #dir with the results of a query and change the dtexec command to execute from the server.

For the dos batch file - you could use osql to create a list of packages then execute that but this loses a bit of the simplicity that would be the main advantage of the batch file.

Subject: re: What about SQL security and variables?
Posted by: nigelrivett (view profile)
Posted on: Tuesday, December 12, 2006 at 9:56 AM
Message: >> what if your SQL server security does not allow to use command shell from SQL?

Well you could use another less secure server - maybe sql server express. I've used this technique to execute commands on a remote machine which I wouldn't want to do from a secure server.

As to the variables setting - it depends where you want to get the variable info from. Probably would set up the values in a table or file so that they give a history then retrieve them from there.

Subject: re: What if your packages needs any variables to be set to execute correctly
Posted by: nigelrivett (view profile)
Posted on: Tuesday, December 12, 2006 at 10:10 AM
Message: Maybe set the values in a configuration file or use the /set parameter on dtexec to set the variables.
For the control package you would probably set the variables in the control package and access them as parent variables in the called package.

Subject: Package execute failed??
Posted by: Anonymous (not signed in)
Posted on: Sunday, December 17, 2006 at 10:42 AM
Message: Hi,

Thank you very much for the usefull information, but i faced some problems and i wish you can help me.

I tried to execute .dtsx file using a stored procedure but the sql command executed successfully and the SSIS package failed because of security error,why? and what can i do? could you help me please??

Subject: Package execute failed??
Posted by: Anonymous (not signed in)
Posted on: Tuesday, December 19, 2006 at 3:06 AM
Message: Hi,

Thank you very much for the usefull information, but i faced some problems and i wish you can help me.

I tried to execute .dtsx file using a stored procedure but the sql command executed successfully and the SSIS package failed because of security error,why? and what can i do? could you help me please??

Subject: Package execute failed??
Posted by: nigelrivett (view profile)
Posted on: Sunday, January 21, 2007 at 11:32 AM
Message: Probably because you have a connection that requires a password.
see
http://www.nigelrivett.net/DTS/SSISConfigurationFile.html

Subject: Re: Alternate Way?
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 01, 2007 at 4:20 AM
Message: Hi Nigel Rivett,

The post was really useful one and this is what I have been searching for... but I need your inputs in completing this..

I am referrring to one of the other folks who have raised the same concerns as mine when we have the packages in the MSDB database.

Can you post us a detailed way of achieving this...Please update me with that link...Thanks

Regards,
Maddy

Subject: Solution?
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 01, 2007 at 9:29 PM
Message: I am new to SSIS, I have created a SSIS package and scheduled it to run every night, the package truncates the tables and then populates it with data from the production database. The problem is, when there is a job failure in between, some tables are truncated and then fails. How do I roll back if there's a job failure?

Subject: Re: Alternate Way?
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 22, 2007 at 7:52 AM
Message: I am also looking into a Server Storage - MSDB solution. I have not attempted this but I am going to look into the cmd approach, but the parameters to the DtExec would be different, here's an example of the syntax that we are currently using via batch files. I'll post back if/when I find that the solution works (or not):

DTExec /SER "SERVER_NAME" /sq "PackageName" /SET \Package.Variables[PACKAGE_VARIABLE_NAME].Value;"ValueForVariable" /CONF "\\ConfigFileServer\SomeFolder\ConfigFileName.dtsConfig"


Subject: Schedule Parameters
Posted by: Anonymous (not signed in)
Posted on: Wednesday, March 28, 2007 at 10:03 AM
Message: Hello, I have an SSIS Project, but I want to change the execution unit from a Web Form. Example:
By default the Schedule executes it activities 'dayly', and from an aspx form, I want to change the execution to 'monthly', can you tell me how to do it?

Regards From Venezuela
leonel.abreu@noixno.net

Subject: Schedule Parameters
Posted by: Anonymous (not signed in)
Posted on: Wednesday, March 28, 2007 at 10:21 AM
Message: Hello, I have an SSIS Project, but I want to change the execution unit from a Web Form. Example:
By default the Schedule executes it activities 'dayly', and from an aspx form, I want to change the execution to 'monthly', can you tell me how to do it?

Regards From Venezuela
leonel.abreu@noixno.net

Subject: Sequential or Parallel
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 05, 2007 at 10:13 PM
Message: Great Post

Will the package run Sequentially or Parallel?

Subject: Need Help on ssis
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 26, 2007 at 1:50 PM
Message: would like to get help on creating an ssis package for copying files from one location to another using foreach loop container and file system task.


Subject: Executing all SSIS packages in a folder: three methods
Posted by: Anonymous (not signed in)
Posted on: Monday, April 30, 2007 at 6:46 AM
Message: Truly great article it is.

Subject: re: Solution?
Posted by: nigelrivett (view profile)
Posted on: Wednesday, May 02, 2007 at 8:47 AM
Message: You could include everything in the package in the same transaction so that it can all roll back on an error - but this is not usually what you require. It would mean that everything would be logged.

Consider loading everything into copies of the production tables then swapping them over at the end or copying the production tables before the truncate so the data can be erplaced on a failure.
Or maybe just backup the database and restore it on error.
Or do all the processing in other tables (in a staging database?) so that the bit that replaces the production data is less likely to fail.

There are a lot of ways of accomplishing this but depends on your requirements.

Subject: re: Need Help on ssis
Posted by: nigelrivett (view profile)
Posted on: Wednesday, May 02, 2007 at 10:55 AM
Message: That should be easy.
There is an option in the for each task to set the collection to iterate through a folder (you can use wildcards) and set the variable from that (have a look at the article - it does this for ssis packages which are just files after all).
The set the source and destination connection for your copy from this variable in the loop container and you should be done.

Subject: re: Sequential or Parallel
Posted by: nigelrivett (view profile)
Posted on: Wednesday, May 02, 2007 at 11:04 AM
Message: For the examples the packages will run sequentially.
That is often best anyway as the disk access is usually the limiting factor.

If I want things to run in parallel (usually threads of proceses) then I would name the packages by the thread and then it it simple to run the threads in parallel. For the container just add extra containers for the different threads. For the batch and SP run extra copies of them.

Subject: Sequential or Parallel
Posted by: Harinath (not signed in)
Posted on: Tuesday, August 07, 2007 at 4:15 AM
Message: Cool Info Folks!
Thanks...

Subject: Using a CLR Stored Procedure
Posted by: Anonymous (not signed in)
Posted on: Monday, August 20, 2007 at 3:21 PM
Message: Have you written a CLR procedure that could execute a SSIS package stored in MSDB or the File System? This would be useful for those environments where xp_cmdshell is not a option.

Thanks...

Subject: Using a CLR Stored Procedure
Posted by: Anonymous (not signed in)
Posted on: Monday, August 20, 2007 at 3:34 PM
Message: Have you written a CLR procedure that could execute a SSIS package stored in MSDB or the File System? This would be useful for those environments where xp_cmdshell is not a option.

Thanks...

Subject: i want some clarification
Posted by: singam (not signed in)
Posted on: Thursday, October 18, 2007 at 8:53 AM
Message: hi Nigel Rivert,
i'd like to call a ssis package in a stored procedure, is it possible?
if yes, tel me

Subject: i want some clarification
Posted by: singam (not signed in)
Posted on: Thursday, October 18, 2007 at 8:54 AM
Message: if thats(calling an ssis package in mssql stored procedure) possible please mail me apkvnbw@yahoo.com

Subject: Target table being truncated
Posted by: simon (not signed in)
Posted on: Friday, February 01, 2008 at 4:24 PM
Message: When my XML file has bad data, the package is failing and issuing a Truncate Table on the target table.
This is deleting all the data from prvious loads.
How can I stop it from doing the Truncate?

Subject: Great Article
Posted by: Bibhuti Bikash Thakuria (not signed in)
Posted on: Wednesday, February 06, 2008 at 3:34 PM
Message: Hey,

Great article with very simple desc. I was quite confused how to do this but you made it so simple for me.

Hope to get good articles from you.

Thanks
Bibhuti Bikash Thakuria
NY,USA

Subject: SSIS parallel execution-Clarification required Pls
Posted by: Janani (not signed in)
Posted on: Friday, February 08, 2008 at 1:32 AM
Message: The information given above is very useful.

Am relatively new to SSIS.I have followed the steps which has been mentioned for executing packages parallely.

But am facing a problem, While executing the package (which contains teh for each loop and execute task) only 2 packages are being read from teh folder i had specifies,whereas there are 4 packages saved in the folder, but only 2 are being executed.

Thanks,
Janani

Subject: SSIS parallel execution-Clarification required Pls
Posted by: Janani (not signed in)
Posted on: Friday, February 08, 2008 at 1:41 AM
Message: The information given above is very useful.

Am relatively new to SSIS.I have followed the steps which has been mentioned for executing packages parallely.

But am facing a problem, While executing the package (which contains teh for each loop and execute task) only 2 packages are being read from teh folder i had specifies,whereas there are 4 packages saved in the folder, but only 2 are being executed.

Thanks,
Janani

Subject: urgent help on ssis packages 64 bit ,,,>dbajohnny@gmail.com
Posted by: Anonymous (not signed in)
Posted on: Sunday, February 17, 2008 at 3:26 PM
Message: hi,
Urgent need help from anybody reg.SSIS Packages on 64 bit

We are currently facing the problem with building 64 Bit SSIS pacakages(located on DB Server..which is 2003 Server Edition, 64 Bit) with connecting 32 bit oracle(loaded on the App Server..Windows 2003, 32 bit) in Business Intelligent Development Studio(Integration Services) with Sql Server 2005 64 Bit. We have installed 64 bit Oracle client and network tools and drivers on Database Server(DB Server), but we are still unable to connect from to Oracle from the BIDS for making the 64 bit SSIS packages.

Please give guide me towards the steps for testing SSIS packages which are in 64 bit Using XML files?

how to proceed towards making these packages??

Thanks
dbajohnny@gmail.com



Subject: new to ssis: stopping the job
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 06, 2008 at 3:55 PM
Message: i need help in stopping the job through ssis. details appreciated. thank you

Subject: simple question/answer?
Posted by: Aurelio (not signed in)
Posted on: Tuesday, March 25, 2008 at 12:32 PM
Message: I was looking through all this and I did not see the simple answer. I may have just missed it or it so common knowledge it is assumed.
When I save my sql packages, they save in the integration services msdb section.
Can I call this simply from a stored procedure?
I don't care about loops and all that for now, as they may confuse me on the simple part of the question, and I can list all the packages without problem.
I am just wondering if you can do something like.
Exec Server/path/package in a stored procedure.
Thanks


Subject: Creating SSIS packages programatically
Posted by: Prince (view profile)
Posted on: Tuesday, April 08, 2008 at 2:52 AM
Message: I want to create a SSIS package through c# code. My aim is to read the data from Excel sheet and upload the data to sqlserver. I have done this job by creating a package using the wizard. But the datasource maynot be same.

So how can we write a package in c#?

Subject: Calling SSIS Package from ASPX , executing but the ETL process not doing
Posted by: Raja (not signed in)
Posted on: Tuesday, April 22, 2008 at 11:29 PM
Message: I created a SSIS package and called the package thru Managed DTS namespace in ASPX. Executing the DTS but the DTS ETL process not executed. Getting failure result. Even I created a user thru WPG and assign the user to Adminstrator group and assign the permission for the ASPX Site, the result is failure. Please advice me to solve it.V.Urgent. Thanks.

Subject: update new server name in existing SSIS packages.
Posted by: pavi (not signed in)
Posted on: Monday, May 05, 2008 at 6:55 AM
Message: hello,

we have changed the name of MS SQL server 2005 from XYZ to ABC using

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO



Now our maitenance plan is getting failed we are not able to execute backup jobs we are getting following error



Date 03.05.2008 16:00:00
Log Job History (ADM_AdminDB_TP_Backup.Subplan_1)

Step ID 0
Server ABC
Job Name ADM_AdminDB_TP_Backup.Subplan_1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. Unable to determine if the owner (XYZ\SQLServer) of job ADM_AdminDB_TP_Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'XYZ\SQLServer', error code 0x534. [SQLSTATE 42000] (Error 15404)).

how to change server name in SSIS packages??

Subject: update new server name in existing SSIS packages.
Posted by: pavi (not signed in)
Posted on: Monday, May 05, 2008 at 8:17 AM
Message: hello,

we have changed the name of MS SQL server 2005 from XYZ to ABC using

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO



Now our maitenance plan is getting failed we are not able to execute backup jobs we are getting following error



Date 03.05.2008 16:00:00
Log Job History (ADM_AdminDB_TP_Backup.Subplan_1)

Step ID 0
Server ABC
Job Name ADM_AdminDB_TP_Backup.Subplan_1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. Unable to determine if the owner (XYZ\SQLServer) of job ADM_AdminDB_TP_Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'XYZ\SQLServer', error code 0x534. [SQLSTATE 42000] (Error 15404)).

how to change server name in SSIS packages??

Subject: update new server name in existing SSIS packages.
Posted by: pavi (not signed in)
Posted on: Monday, May 05, 2008 at 8:45 AM
Message: hello,

we have changed the name of MS SQL server 2005 from XYZ to ABC using

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO



Now our maitenance plan is getting failed we are not able to execute backup jobs we are getting following error



Date 03.05.2008 16:00:00
Log Job History (ADM_AdminDB_TP_Backup.Subplan_1)

Step ID 0
Server ABC
Job Name ADM_AdminDB_TP_Backup.Subplan_1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. Unable to determine if the owner (XYZ\SQLServer) of job ADM_AdminDB_TP_Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'XYZ\SQLServer', error code 0x534. [SQLSTATE 42000] (Error 15404)).

how to change server name in SSIS packages??

Subject: how to reun parallely the pakages
Posted by: Anonymous (not signed in)
Posted on: Thursday, May 22, 2008 at 2:20 AM
Message: create a master package, sequence to execute dtsx packages should be :

1) Execute all prestg-stg dtsx packages
2) load all dim tables
3) load fact table

Subject: thanks
Posted by: jayant das (not signed in)
Posted on: Tuesday, June 24, 2008 at 9:28 AM
Message: Dear sir

I would like to Nigel Rivett

Regards Jayant das
9313406257

Subject: Excellent!!!
Posted by: Thendral (not signed in)
Posted on: Tuesday, July 08, 2008 at 8:09 AM
Message: Nice Article...

Subject: Need Help
Posted by: Sateesh (not signed in)
Posted on: Thursday, July 10, 2008 at 2:59 PM
Message: I am new to this SSIS ,i was importing data from sybase server to other
i created ssis package and scheduled it using sql server agent BUT what i need is inside the sql query i wrote (select x, y from view1) this view1
should change increment as view2, view3 on daily basis.so can you please tell me the way to do it.

Subject: How I can execute SSIS package using c#.net (2.0) (using com object) ?
Posted by: Paresh (view profile)
Posted on: Friday, July 11, 2008 at 3:33 AM
Message: Hi,

How I can execute SSIS package using c#.net (2.0) (using com object) ?

(As i am using DTS package previously and calling the same through c#.net (=
1.1) by using com object Interop.DTS.dll)

Please do the needful.

Thanks in Advance.

Regards,
Paresh

Subject: starting a package
Posted by: Horst (not signed in)
Posted on: Tuesday, July 22, 2008 at 1:11 AM
Message: I would start a Package from a Stored Procedure. When i start it from a StoredProcedure it needs 46 Sekconds to run. If i start the thame package with util DTExecUI the duratin is < 1 second.

Can anybody tell me where is my problem / bottleneck

Subject: Main Package calling a Sub Package
Posted by: Anil Pillai (not signed in)
Posted on: Monday, July 28, 2008 at 1:30 PM
Message: Nigel,
I looked at this link - and yes, it works fine.
http://www.nigelrivett.net/DTS/SSISConfigurationFile.html

But I have come across a situation when I have to call a package (that was built the way you described in your link) using another package. Works fine in BIDS. But breaks in a job when this Main package is called. The error looks like the subpackage is somehow not invoking the configuration file (where the password is stored).

Any idea? I have tried practically everything, including credentials/proxy etc.
Thanks
A

Subject: Compare Source Tables rows and destination Tables rows
Posted by: surya.narayana (view profile)
Posted on: Tuesday, September 02, 2008 at 2:09 AM
Message: I have prepared ssis package for downloading data from AS400 to sql server. How to Compare Source Tables rows and destination Tables rows in ssis package? I would like to abort the ssis process if both row counts are not the same.

Subject: SSIS For Each Loop Crashes when files run indivually fine
Posted by: dschweers (view profile)
Posted on: Monday, October 13, 2008 at 4:13 PM
Message: I tried to do the first option on the page and each runs fine when run one at a time but after 4 or 5 packages is crashes and says that the table all ready exists. It's like it's stumbling on itself.

Subject: Individual files run fine but crash in for each loop
Posted by: dschweers (view profile)
Posted on: Monday, October 13, 2008 at 4:24 PM
Message: I tried to do the first option on the page and each package runs fine but when run as a for each loop, after 4 or 5 packages is crashes and says that the table exists. It's like it's stumbling on itself. I just created the table.

Subject: Alternate Approach using Stored Procedure
Posted by: weyenbrm (view profile)
Posted on: Tuesday, January 06, 2009 at 9:37 AM
Message: An alternate approach to using xp_cmd_shell (which is not recommended) in a stored procedure to execute a package is to dynamically create a job and execute it. You can see an example at:
http://3msoftwaretechnologists.blogspot.com/2008/12/calling-ssis-package-from-stored.html

Subject: How I can execute SSIS package using Access 2007 application ?
Posted by: Yasmina (view profile)
Posted on: Thursday, February 12, 2009 at 4:22 AM
Message: Hi,

How I can execute SSIS package using Access 2007 application ?

Thanks in Advance.

Regards,
Yasmina

Subject: Using the Stored Procedure method for SSIS
Posted by: rcrill (view profile)
Posted on: Monday, February 16, 2009 at 11:00 AM
Message: I am getting an error the says the SSIS package can't be read because it is an unreadable XML format. What is a readable XML format?

What do I do with that.

Thanks

Ross

Subject: Foreach File Enumerator using Excel files
Posted by: sqllion (view profile)
Posted on: Thursday, June 18, 2009 at 2:01 PM
Message: For more information on For each loop enumerators (Foreach File Enumerator using Excel files) and programming integration service (SSIS) using C# .Net and VB .Net, please visit the below link:
http://www.sqllion.com/2009/06/programming-foreach-loop-container-–-enumerating-excel-files/

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.