Executing packages Dynamically

Question

Executing packages Dynamically

dbdeli

Say Hey Kid

Points: 686
More actions
June 11, 2008 at 6:23 pm

#154441

I have created an SSIS package to execute other ssis packages dynamically.
I have one Execute SQL Task with Single Row, OLD DB connectionType, direct input and a select statement that returns 3 variables as a result set.
In my package I have declared the three variables as global in the scope of the package (ImportId, SequenceNumber, and PackageName).
I have a Script Task and set up the ReadOnlyVariable to = user::packageName
and in the script I have :
Dts.Connections("PackageToExecute").ConnectionString = Dts.Variables("User:packageName").Value.ToString
My third task is an Execute Package Task so I can execute the other package (pkgAssignCompanies) dynamically.
I have set up the properties as :
Location = File system
Connection = PackageToExecute
No to mention that I have two connections in my solution ( PackageToExecute and devdb)
The first two tasks Execute SQL Task and Script seem to work well but the third (Execute Package Task) turns red and I get the following error:
Information: 0x40016040 at importPackages: The package is attempting to configure from SQL Server using the configuration string ""DEVDB.Report";"[dbo].[SSIS ConfigurationsTest]";"test";".
Information: 0x40016040 at importPackages: The package is attempting to configure from SQL Server using the configuration string ""devdb.PackageBroker";"[dbo].[SSIS Configurations]";"test";".
SSIS package "importPackages.dtsx" starting.
Executing ExecutePackageTask: pkgAssignCompanies
Error: 0xC00220DE at Execute Package Task: Error 0x80070002 while loading package file "pkgAssignCompanies". The system cannot find the file specified.
.
Task failed: Execute Package Task
SSIS package "importPackages.dtsx" finished: Success.
The File Connection Manager in many different ways. I even re-created the package and save it in a under:
C:\temp\ImportDTS\Integration Services Project1\Integration Services Project1\bin\pkgAssignCompanies.dtsx
But what I noticed was that when I select the EXECUTE PACKAGE TASK and execute it alone, it exectues Package pkgAssignCompanies, which is the ultimate goal.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Error: 0xC00220DE at Execute Package Task: Error 0x80070002 while loading package file "pkgAssignCompanies". The system cannot find the file specified.

I believe this would normally display the full path of the package file. Are you only specifying the package file name and not the entire path to it's location?

dbdeli Say Hey Kid Points: 686 More actions · Answer 2

Michael,

I actually forgot to include the .dtsx extention in PackageName. I added it and its working just fine.

Now, I have another question. I have an audit table called tblimportLog with

LogID, ImportID, StepID, BeginTime, EndTime, Status.

What type of SSIS Task do I need to use to populate that table after the package has been execute?

Thanks.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

If you are going to log this manually, I would use a SQL Command component and put it into the appropriate Event Handlers of the package.

However, rather than creating your own table, you may want to right-click in the control flow of your package and choose logging. You can configure the SQL Server log provider to log to a table - it will name it dbo.SysDTSLog90 - and you cna then query this table for the event information you care about.

dbdeli Say Hey Kid Points: 686 More actions · Answer 4

Thanks for your imput. It worked well.

I went ahead and setup the logging in my SSIS package. It seems to work pretty well.

Other than that, all the tasks in my SSIS package are in the Control Flow. So I can't really use the OLE DB Command. Other than setting up the logging in SSIS, is there any other ways of recording the logs in the Control Flow?

Thanks

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

By SQL Command, I meant the Execute SQL Task in the control flow, not the OLEDB Command transformation. You could use this to insert or update records at key points in the control flow.

dbdeli Say Hey Kid Points: 686 More actions · Answer 6

Going back to the original problem, where the the Set Package Path (script task) and execute package task did not recognize the packageName. Now it works fine because I have placed the extention .dtsx and it executes the package that is in the path because it recongnizes its name

The packageToExecute connection is set to pkgAssignCompanies itself:

C:\temp\ImportDTS\Integration Services Project1\Integration Services Project1\pkgAssignCompanies.dtsx

This works only if you want to execute that same package, but if you want to loop through the next package and execute that it is not going to work. So I changed the path to PackageToExecute to

C:\temp\ImportDTS\Integration Services Project1\Integration Services Project1

but it gives out an error now because it does not know which package to execute. How would I resolve this problem.

Thanks

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 7

You can either dynamically alter the connection manager (which I have not had a lot of luck in being stable) or make the connection manager always point to the file you want.

That second option sounded confusing, but it works. Copy one of your packages to a new file and point your connection manager at it. In your loop container, use a file system task to copy the actual .dtsx file you want to run to the place your connection manager is pointing. This way, you can hard-code the path for the connection manager and just replace this file with the package you want to execute.

dbdeli Say Hey Kid Points: 686 More actions · Answer 8

Michael,

Thanks for all your help. Really appreciate it.

Going back to the same question for updating the log / history table in the Control flow.

So you recommended that I should use an Execute SQL Task to insert the Table

My package starts with an Execute SQL Task looks to see which steps / SequenceNumber, PackageName, and ImportId to pass down to execute the correct package.

select top 1

c.importID

, c.sequenceNumber

, c.PackageName

from dbo.tblImportConfig c

left join dbo.tblHistory h

on c.importid = h.importid and c.sequenceNumber = h.sequenceNumber

where h.status is null

order by c.importid, c.sequenceNumber desc

Now, after the package is executed I want to insert a new record in tblHistory (importLogId, ImportId, SequenceNumber, StartTime, EndTime, Status)

So, I assume that following the Execute Package Task I should add an Execute SQL Task and in the presedence Container I should select the value as Completion. But the part that I am confused is that do I do it all with one Execute SQL Task and

I assumed that I could do an insert statement in the Execute SQL Task;

Insert into tblHistory values (?,?,?,?,?,?)

and declared the variables in the package to pass and then mapped thouse variables in the parameter mapping but when I execute I get an error..

Please advise.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 9

I would recommend using logging if possible.

Barring that, one option is to use multiple execute SQL tasks and put them at whatever point in your package you want to insert into your logging table. Another option would be to go to the event handlers - if you can find an event that fires at the correct points, you may be able to use just a single task.

As far as using the task. Your insert statement looks ok, and I assume you have your parameters mapped correctly. What is the error message you are getting? Typically parameter mapping issues are related to data type mismatches.