tidal with SSIS

[coool_sweet1]

hi

i need to schedule my ssis using tidal,

i dont know what parameter i need to pass to command.

after seeing other packages they are passing .exe file, my question is do i need to create this file,whats purpose of this file and how can i create it.

i have 1 parameter in my ssis package

[Dinesh Sankaranarayanan]

You haven't mentioned the version of SSIS. 

If you are using 2008, you have two choices

1. Create an SQL job that in turn calls the SSIS package. The passing of parameters to SSIS is done within the SQL job. You can call the SQL job from Tidal if you have the MSSQL adapter
2. Use command line dtexec.exe to invoke the package. Depending on how the package has been deployed(on the db server or on file system) the switches /SQL or /FILE would be used. The parameters are passed using /Set switch. For example 

dtexec /f mypackage.dtsx /set \package.variables[EmpID].Value;12345

Refer to MSDN for details

If you are running 2012, then you can also call the package using stored procedure. It can be invoked synchronously(Tidal waits for the execution to complete) or asynchronously

Hope this helps 

[shoxsie]

In working with the MSSQL Adapter,  I have noticed that it does not recognize a Job Step type of 'SSIS'.   Makes me wonder if Cisco has a newer version of the adapter that supports that job step type?

Steve

[shoxsie]

 Here's a screenshot with the version information of the MSSQL Adapter in our environment.

[Dinesh Sankaranarayanan]

There is no direct support for SSIS job in the MSSQL adapter.

If you have 2012, you could invoke the SSIS package using the stored procedure provided by MSSQL 2012. There are many articles on MSDN which should help you

Or use command line or stored procedure methods mentioned above

[coool_sweet1]

hi

i have my package in 2012,i am calling it from TIDAL.

but i need to pass following parameter to tidal 

a.       Jobname:ssis_job1

b.      Program command: (here i do see there is .exe file everybody has,what this file

and how can i create it,whats purpose if this file)

c.       Program command parameters: Server=server01

 I=20 Job=ssis_job1

d.      Capture Alternate Output File

e.      Schedule: daily

f.        Run Agent:

                        g.Runtime user: SQL_user

[Dave.Carnahan]

If the SSIS package has multiple parameters, (aside from the location of the DTSX and DTSConfig files), you'll need to setup the parameter entries for DTEXEC.

[1] Here's a DOS example for feeding parameters to an SSIS job:

(see attachment png file beginning with the name 'DOS.xxx')

[2a] You also should try out Microsoft PowerShell as a shell for executing your Tidal jobs.

Here's an example of feeding DTEXEC parameters from PowerShell, 

(see attachment png file beginning with the name 'PowerShell.xxx') 

[2b] There's a 2nd way within Microsoft PowerShell that is as fast as being natively in SSMS and executing your package (instead of shelling out to DTEXEC) and that's to call the SQL provider within (regular old) PowerShell. 

Here's one link (below), but just Google -

'PowerShell execute SSIS packages from PowerShell with parameters'

-

http://www.databasejournal.com/features/mssql/ssis-2012-using-powershell-to-configure-package-execution-parameters.html 

-

And did I mention the SQLPS (or SQL PowerShell provider?) I'm half way guessing there's a way in there to get done your SSIS packages, but I'm staying on the 'core' PowerShell road myself.

Oh, and did I mention the beginning and ending dates for that package are dynamically coming from variables created and passed into the job stream from Tidal?

Good luck... 

[vamsivaliveti.edu1]

I am referring attachment png file beginning with the name 'DOS.xxx' and i have below doubts.Input will be appreciated

1)Could you please provide details and code for what i need to mention in 01_Tidal_Env.Bat file and also where i need to place this file

2)What is SSID.DtsConfig and where this file is present?

[Dave.Carnahan]

[1] See above for contents of 01_....Bat

[2] Also - 01_Tidal_env.bat is not a requirement,

But rather only identifies things going on at the time of this Tidal job execution, displayed in the console output prior to the actual job execution.

It presents things like what server this job went off on (for the server farm concept), when this occurred and what OS - just in case there's a discrepancy in the Tidal agent 'server farm' you're running.

[Dave.Carnahan]

Here's the 01_Tidal_env file spoken in the PowerShell language:

==

cls
;$object = Get-WmiObject -Class Win32_OperatingSystem
;$lastboot = $object.LastBootUpTime
;
;If ($ENV:OCSJNUM)
{'[1] It is now ' + (Get-Date).GetDateTimeFormats()[28] + ' running Tidal job: ' + $ENV:OCSJNUM + ' on ' + $($ENV:ComputerName) + ' using (' + $($ENV:Number_Of_Processors) + ') processors. Last bootup time is: ' + ($object.ConvertToDateTime($lastboot));}
else
{'[1] It is now ' + (Get-Date).GetDateTimeFormats()[28] + ' running on ' + $($ENV:ComputerName) + ' using (' + $($ENV:Number_Of_Processors) + ') processors. Last bootup time is: ' + ($object.ConvertToDateTime($lastboot));
 }
$PM = ". Phys total memory: $([decimal]::round(((Get-WmiObject win32_computersystem).totalphysicalmemory ) / 1gb)) gig"

'[2] The shell is PowerShellv'+$($Host.Version.Major)+' with ('+(Get-Command).Count+') native cmdlets. The OS is '+$(Get-WMIObject Win32_OperatingSystem).Caption+'v'+$(Get-WMIObject Win32_OperatingSystem).ServicePackMajorVersion+$PM ;
==

For PowerShell, this is expressing the current Tidal job execution date/time, computername, number of cpu's on the machine, unique Tidal job-run ID, last reboot of this server, number of PowerShell Cmdlets, OS version, amount of memory.

For some of these things, PowerShell calls WMI objects, which is very nice and convenient.

==

[Dave.Carnahan]

Both the BAT files (below) are contained in a common folder on each server where a Tidal agent resides with the identical path.

--

[1] What's in 01_Tidal_Env.bat?

Basic Tidal job identifying information.

===

Echo It is now %Date% at %Time% running Tidal job: %OCSJNUM%
Echo [1] This server '%ComputerName%' has (%NUMBER_OF_PROCESSORS%) processor(s)
Echo [2] The OS-version for '%ComputerName%' is:

===

[2] Finding the right version of SSIS for the server you're running the job on?

There you have to know which version you want to use before you execute the package.

Here's a script for SSIS 2008

==

@Echo Off
 Goto SQL2008_on_%COMPUTERNAME%
:SQL2008_on_SERVER1
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Version 2009.0100.1600.01 from 04/03/2010 11:56 AM
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
 "D:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.exe" %1 %2 %3 %4 %5 %6 %7
 Echo -Done with OSQL-SQL2008 on %ComputerName%
 Goto A999_Exit
:SQL2008_on_SERVER2
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Version 2009.0100.1600.01 from 04/03/2010 11:56 AM
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
 "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.exe" %1 %2 %3 %4 %5 %6 %7 
 Echo -Done with OSQL-SQL2008 on %ComputerName%
 Goto A999_Exit
:Default
 Echo Couldn't find SQL2008 OSQL.exe on '%ComputerName%'
:A999_Exit
 Echo.
 Echo End of - OSQL SQL2008 - script

==

[vamsivaliveti.edu1]

can i use below code directly with out using 01_Tidal_Env.bat and SSID.DtsConfig file.If not when to use these two files exactly?

1.  create a job on a windows agent

2.  command:  cmd.exe

3.  parameter:  /C \\path\to\batch\file.bat

4.  file.bat file contains something like this:

@ECHO OFF

DTEXEC.exe /FILE "\\path\to\file.dtsx" /argument1 /argument2

where:

a) your dtsx file is an integration services package you can create/edit with Microsoft Visual Studio

b) /arguments are optional depending on the package you have created

[Dave.Carnahan]

Above, I've placed a 'generic' location of the tools OSQL.exe based on where the DBA's have room to install the different versions.

Some NA's use the C: drive, others, D:, ...

{You name the drive letter, its site specific information you'll need to dig up and place into this BAT file}. The same goes with DTEXEC, SQLPS.exe, and anything else you'll want to always return back to.

I call this a 'shim' like in the house building industry, shims are used to fill in gaps in differences (hanging doors and windows, leveling flooring, etc).

For Tidal, its leveling your OS and toolset platform(s).

[Dave.Carnahan]

[1] What's in the file called 'myssis.project.dtsx'? The SSIS 'project' package.

[2] What's in the file called 'myssis.project.dtsconfig'? The config file for the SSIS 'project' package.

[3] Where do I keep these two files? Highly likely in a folder for the project by themselves.

If log files are kept around, log files might be written there also, along with other packages or apps - all having to do with the individual project this all pertains to.

With my employer, these 'projects' make up a Tidal 'Job Class' so they can be placed in focus rapidly (and eliminate the other projects).

Projects generally fall within divisions within the agency and we have just a handful of divisions, with many projects (or programs) in each division.

This is all cataloged as a top-down approach: Agency / Division / Program / Special need {and so on}.

One neat thing about storing keywords and your Tidal job-hierarchy in the Tidal Job Class 'Description' tab, is that you can extract this information, (break it out by a delimiter for defining fields using a vertical tab character '|'. Then the information extracted can go to the managers in Excel or whatever choice software for PM interests.

[shoxsie]

We run this SQL from the MSSQL Adapter to kick off an SSIS package.

declare @execution_id bigint

declare @package_parm smallint = 30 --Type of Parameter (30 for package Parameter, 20 for Project Parameter)

exec ssisdb.catalog.create_execution

@folder_name = 'SCS Interfaces' --SSISDB Folder

,@project_name = 'Z_SCS_DeleteFile' --Project Name

,@package_name = 'SCS_Delete_File.dtsx' --Package Name

,@execution_id = @execution_id output

exec ssisdb.catalog.set_execution_parameter_value

@execution_id = @execution_id

,@object_type = @package_parm

,@parameter_name = 'FileName'

,@parameter_value = N'\\127.0.0.1\Data\SCS\UWNN369_Trigger.txt'

exec ssisdb.catalog.start_execution @execution_id