November 20, 2016 at 1:03 pm
Hi, sorry in advance for the n00b questions, but I'm very new to SSIS.
Here's the story: for my work I need to transfer around 200 tables over VPN to a linked server. It a migration project and I need to transfer the data around 5 or 6 times during next year.
Got it all setup and working. First with queries (insert into...select from..) but the project demands the use of SSIS. So I studied SSIS and started with it. Got is working and created the needed packages. Besides: the transfer is way quicker via SSIS. So SSIS it is!
So all good. I create and save the packages with the import and export wizard. When I wanna execute the packages I use the 'Integration Services Execution Utility'. I tried the user key for encryption and it works fine, but someone else must be able to run it, so I must use the password option. So on execution the p/w need to be entered but that's no big deal.
So all okay, but here my questions:
*is there any (easy) way to make a very simple user interface for the execution of the packages? Let's say a basic windows where the packages are listed. Select one (or more) en hit the 'run'-button? So colleages only have to open this program and hit the button.
I can program in VB.net and C# and tried searching the internet for clear examples but no joy really. It's just to make it very easy for everyone to run when I'm away and it just for triggering the execution of the packages. That's it.
*is there a way to get more and more detailed reports/results when the job's done? Where should I look? On the other hand: if I can manage to trigger the packages to run from a program (vb.net or C#) then I can build my own 'logs' by writing to txt files or anything.
SO, the first point is really the most important. I hope anyone can help me.
Many thanks in advance!
November 21, 2016 at 6:20 am
Super_Grover (11/20/2016)
Hi, sorry in advance for the n00b questions, but I'm very new to SSIS.Here's the story: for my work I need to transfer around 200 tables over VPN to a linked server. It a migration project and I need to transfer the data around 5 or 6 times during next year.
Got it all setup and working. First with queries (insert into...select from..) but the project demands the use of SSIS. So I studied SSIS and started with it. Got is working and created the needed packages. Besides: the transfer is way quicker via SSIS. So SSIS it is!
So all good. I create and save the packages with the import and export wizard. When I wanna execute the packages I use the 'Integration Services Execution Utility'. I tried the user key for encryption and it works fine, but someone else must be able to run it, so I must use the password option. So on execution the p/w need to be entered but that's no big deal.
So all okay, but here my questions:
*is there any (easy) way to make a very simple user interface for the execution of the packages? Let's say a basic windows where the packages are listed. Select one (or more) en hit the 'run'-button? So colleages only have to open this program and hit the button.
I can program in VB.net and C# and tried searching the internet for clear examples but no joy really. It's just to make it very easy for everyone to run when I'm away and it just for triggering the execution of the packages. That's it.
*is there a way to get more and more detailed reports/results when the job's done? Where should I look? On the other hand: if I can manage to trigger the packages to run from a program (vb.net or C#) then I can build my own 'logs' by writing to txt files or anything.
SO, the first point is really the most important. I hope anyone can help me.
Many thanks in advance!
The simple interface you are looking for is more difficult than it may appear. Much of the problem is due to the fact that the packages need to run on the server hosting them. One avenue worth considering is writing a T-SQL stored procedure to execute your packages and calling that proc from C#. This gets round the package-location problem.
If you find one of your packages in SSISDB (via the Integration Services Catalogs node), right click it, then select Reports / Standard Reports / All Executions, you will find plenty of logging information there. If that's not what you meant, please clarify.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 21, 2016 at 7:23 am
In addition to what Phil gave you for question #1, you say you want an easy user interface for execution packages? What about SQL Agent? Why not set up a single SQL Agent job that has a step for each package? Set each job step to Quit on Success and Failure so only one package runs per execution. This would give you the ability to run packages one-at-a-time. You can't do a multi-select with this method, but it's a nice, easy way to give them the ability to see the available packages and execute them without having to develop something custom.
November 23, 2016 at 9:23 am
Thanks Phil and John. I choose the path of executing the dtsx packages through a stored procedure.
Meanwhile I made quite some progress. Got the SP working, so I began building a UI in C# for calling the SP. Which also works fine.
But that is when I hard code the package name. So I made the SP with a variable (package name) and it also works fine so I'm almost there.
For now I have two more issues. I know it's not a C# forum but who knows maybe you guys can also help me with this.
1): I use the following code to list the packages in a datagridview. I then click the package name which become ofcourse the parameter for the SP.
But: this code lists the packages that reside on the Integration Services instance of the server. My packages are on the DB Engine instance in the Integration Services Catalog\SSISDB. Any idea how to go there and list those packages?
Here the code:
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
PackageInfos pInfos = app.GetPackageInfos("\\", "NLDSLASSQLTST", null, null);
foreach (PackageInfo pInfo in pInfos)
{
if (pInfo.Name != "Maintenance Plans")
this.dataGridView1.Rows.Add(false, null, pInfo.Name, null);
}
2): When I execute the package through my C# interface which calls the SP it take a couple of minutes before te execution is finished. But to prevent starting it again (or another package) I'd like disable the start button and make the SP to give me some signal when the package has finished. Is there any way to do that?
November 23, 2016 at 10:30 am
M'kay, well forget about issue 1. I had a good look into it and it seems to work completely different than the old DTS assembly. I gave it a try but didn't work. So now I imported my package into the MSDB and all works fine for now. I can pick a package and run the package through that varable.
Any thoughs on issue 2? I'd like to get some sort of 'notification' from the SP when the package is finished executing.
November 23, 2016 at 10:32 am
Guessing that the DTS.Runtime library is referencing the local SSIS instance. Not sure how you'd point that to another catalog. You may check the class properties to see if there's a way. Otherwise, just query the internal.packages table in the catalog of choice. You can still load the results into a grid for display and execution.
For question #2, I'll have to defer to someone else. I don't execute packages via SP so I'm not sure how it communicates execution completion back to the caller.
November 23, 2016 at 11:27 am
John Rowan (11/23/2016)
Guessing that the DTS.Runtime library is referencing the local SSIS instance. Not sure how you'd point that to another catalog. You may check the class properties to see if there's a way. Otherwise, just query the internal.packages table in the catalog of choice. You can still load the results into a grid for display and execution.For question #2, I'll have to defer to someone else. I don't execute packages via SP so I'm not sure how it communicates execution completion back to the caller.
This is possible if you make the proc run the package in Synchronous mode.
This sort of thing:
DECLARE @synchronized BIT = 1;
EXEC SSISDB.catalog.set_execution_parameter_value
@execution_id
, @object_type = 50
, @parameter_name = N'SYNCHRONIZED'
, @parameter_value = @synchronized;
If you do this, your C# will wait for the execution to complete & you can then grab the package execution status from SSISDB.catalog.executions.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 24, 2016 at 3:34 am
Phil Parkin (11/23/2016)
John Rowan (11/23/2016)
Guessing that the DTS.Runtime library is referencing the local SSIS instance. Not sure how you'd point that to another catalog. You may check the class properties to see if there's a way. Otherwise, just query the internal.packages table in the catalog of choice. You can still load the results into a grid for display and execution.For question #2, I'll have to defer to someone else. I don't execute packages via SP so I'm not sure how it communicates execution completion back to the caller.
This is possible if you make the proc run the package in Synchronous mode.
This sort of thing:
DECLARE @synchronized BIT = 1;EXEC SSISDB.catalog.set_execution_parameter_value
@execution_id
, @object_type = 50
, @parameter_name = N'SYNCHRONIZED'
, @parameter_value = @synchronized;
If you do this, your C# will wait for the execution to complete & you can then grab the package execution status from SSISDB.catalog.executions.
Thanks again Phil! Works absolutely great!
No just adding some more additional features to my program such as logging and I'm done 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply