May 13, 2008 at 3:26 pm
I am not sure if we can execute SSIS package using t-sql, but I want to do the following:
Step1. Execute SSIS package1
Step2. Check for the file1.txt in c drive using the below code:
Declare @result int
exec xp_fileexist 'c:\file1.txt', @result output
IF (@result = 1) --if file exists exec ssis package
begin
Execute SSIS package2
end
If we can't use t-sql, please let me know how I can do that as part of sql job. Thanks!!
May 13, 2008 at 4:37 pm
Hi,
You cn usev execute sql task to use t-sql. In your case, you have to use variables to map your stored proc opuput. even you can use t-sql on job too. At the job steps, type type t-sql code there.
-V
May 13, 2008 at 4:57 pm
i donno if this is what u need
but the below steps will check if a file exists and then proceed to execute a package
first u will need to define three package variables (scope should be package)
Defined variables:
ExecDir package string (path of the directory for example c:\dir\)
FileExists pack boolean false
FileName pak string fil.txt (name of the file)
set precompile to False
read-write = User::FileExists
read-only User::ExecDir,User::FileName
Add the following code to the script task(DESIGN SCRIPT):and then SAVE IT then close it
------------------------------------------------------------------
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim fileLoc, fileName As String
If Dts.Variables.Contains("User::ExecDir") = True AndAlso _
Dts.Variables.Contains("User::FileName") = True Then
fileLoc = CStr(Dts.Variables("User::ExecDir").Value)
fileName = CStr(Dts.Variables.Item("User::FileName").Value)
'System.Windows.Forms.MessageBox.Show("FileDir:"+fileLoc+"FileName:" + fileName)
If File.Exists(fileLoc + fileName) Then
Dts.Variables.Item("User::FileExists").Value = True
'System.Windows.Forms.MessageBox.Show("File exists")
Else
Dts.Variables.Item("User::FileExists").Value = False
'System.Windows.Forms.MessageBox.Show("File not exists")
End If
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
-------------------------------------------------------------
then add a data flow task to the script task
The next step is to define a condition in the control flow. This is done by double clicking on the path line from the Script task to the Data Flow task
expression @[User::FileExists]==True
HOPE IT HELPS..................
May 14, 2008 at 5:33 am
Hi
Here is the code to execute SSIS package from t-sql
Declare @result int,
@cmd nvarchar(4000)
exec xp_fileexist 'c:\file1.txt', @result output
IF (@result = 1) --if file exists exec ssis package
begin
set @cmd ='master.dbo.xp_cmdshell '+'''dtexec /sql "Maintenance Plans\package2" /ser (local) ' +''''
execute sp_executesql @cmd
end
First you need to save your SSIS package on the db server.
These t-sql statements you can add to the sql server job in job steps.
Hope this helps
Regards
Bindu
May 20, 2008 at 10:10 am
I did the following to save the SSIS package onto db server:
Imported the SSIS package into MSDB folder and also imported into the
\MSDB\Maintenance plans folder.
I get the following error:
Could not load package "\Maintenance Plans\SSIS.dtsx" because of error 0xC001000A.
Description: The specified package could not be loaded from the SQL Server database.
Source:
Started: 12:01:51 PM
Finished: 12:01:51 PM
Elapsed: 0.25 seconds
I think the way which I said above for saving the SSIS package may not be the correct way, please advice how to save the SSIS package onto db server. Thx!
May 20, 2008 at 12:57 pm
Hi,
You need to deploy your package. How did you copy the package to msdb folder ? Refer the SSIS package deployment..
-V
May 21, 2008 at 9:35 am
veenagireesha (5/20/2008)
Hi,You need to deploy your package. How did you copy the package to msdb folder ? Refer the SSIS package deployment..
-V
Thanks for your response, I imported by right-clicking on the MSDB folder and then option "Import package". When I changed it to use file switch /f, it worked fine:
dtexec /f "c:\source\source.dtsx"
But I am curious to know how the SSIS packages can be deployed.
Thx!!
May 21, 2008 at 10:50 am
To thenepatsrule: You would never want to deploy a package to production that required user interaction such as a message box.
To Bindu: xp_cmdshell is dangerous which is why it is disabled by default in the SQL 2005 Surface Area Configuration.
To Mh: There are several ways of accomplishing what you seek. Using the Kimball method I would suggest...
1.) Create a 'Master' package that will first execute Package1 (using the 'Execute Package Task').
2.) Use an 'Execute SQL Task' to check if the file exist.
3.) Create a conditional precedence constraint to ensure @result=1 (meaning the file exists)
4.) Use another 'Execute Package Task' to execute Package2
5.) Create some auditing so that if the package fails you have a log of when and why. (optional)
If you get the Kimball groups 'Microsoft Data Warehouse Toolkit' these methods are all described in much more detail...
Good Luck!
Ben Sullins
bensullins.com
Beer is my primary key...
May 21, 2008 at 3:30 pm
Thanks for the response, now I just have only 1 package instead of 2 packages. How do I check for the file existence and then exec the package if the file is present.
Thanks!
May 21, 2008 at 3:35 pm
Use the same 'Execute SQL Task' I suggested for the Master package and add a condition to your precedence constraint that specifies @Exists =1 for example...
Ben Sullins
bensullins.com
Beer is my primary key...
May 23, 2008 at 4:47 am
Also to consider,
If you right click on the package properties, there is a deployment utility option.
When selecting this, change the CreateDeploymentUtility option to true.
When you rebuild your package, this will create a Integration Services Deployment Manifesto in your \bin folder.
When you double click on this file, it will open a package installation wizard which will copy your package to the target destination.
~PD
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply