Sunil Reddy Enugala

…..is just walking through MSBI Street.

Import and Export from SQL Server to Sybase DB using SSIS

Posted by Sunil Reddy Enugala on November 1, 2013

Hi All, In todays post, I would like to discuss about import and export from SQL Server to Sybase DB. 

Lets start with

Exporting data to Sybase DB

Create a ADO .NET Connection manager to connect to Sybase DB.

Right click in the connection manager tab and select New ADO.NET Connection.

image

Select .Net Providers\Sybase Adaptive Server Enterprise Data Provider as Provider and pass other properties values below.

 

image

 

Host : Sybase DB Server name as value.

Port : value which you set while installing Sybase DB Server(In my case, I took default value.)

User name: Login name

Password: Password

Click on Test Connection  button, before going for OK button.

Now, drag ADO NET Destination and set the properties as following.

image

 

Select the Destination table in Sybase Database and map the columns accordingly.

Now, execute the package to dump data from SQL Server to Sybase DB.

image

 

Importing data from Sybase DB

Create a ADO NET Connections manager as discussed above and drag a ADO NET Source to connect Sybase DB.

Once you have configured the connection manager and set it in the ADO NET Source, than

everything will be same as the first case.

 

image

 

Lets discuss the same using SQL Server Import and Export wizard

Exporting data to Sybase DB

As my Sybase DB server is of 64 Bit version , I have used same version of SQL Server Import and Export wizard.

before starting, we need to create Data Source Name (DSN), As my OS is Windows 7, I’ll show the configurations accordingly.

Go to Control Panel –> Administrative Tools –> Select Data Sources (ODBC) –> under User DSN, click on Add button.

image

Select Adaptive Server Enterprise driver and give a name and description to the new DSN.

I have used same name for both Driver and Data Source Name (DSN)

Configure Data Source as following

I considered SQL Server as my source and configured accordingly.

image

Click on Next button to configure Destination.

Please go through the below figure to know the Sybase DB Configuration.

image

Select .Net Framework Data Provider for Odbc as the destination provider and set the connection string as following

Data Source=<Server Name>;Port=<Port Number(default 5000)>;Database=<Database Name>;

Uid=<Login Name>;Pwd=<Login Password>;

Next configure DSN and Driver as shown in the above figure. Follow Next button, to map the database tables and to transport data.

-

All the Best and have a Happy and safe Diwali.

Sunil Reddy Enugala

Posted in MS BI Related | Tagged: , , , , | Leave a Comment »

Execute SSRS report from SSIS package Part#2

Posted by Sunil Reddy Enugala on October 16, 2013

image

Hi, Earlier we have discussed on execution of SSRS Report from SSIS package. I hope it reached most of the people as it shows in the stats of my blog.

In my Today post, let us discuss regarding another approach of Executing SSRS Report. This approach will use Subscriptions(Standard/Data-driven) of SSRS.

If you have goon through my last post on Standard Subscription, I have shared a script which gets list of subscriptions created on that server.

Please find that same script below again.

   1: SELECT R.ScheduleID AS JobName   

   2:     ,C.NAME AS ReportName   

   3:     ,C.[Path] AS ReportPath   

   4:     ,S.SubscriptionID   

   5:     ,SUBSTRING(S.DeliveryExtension   

   6:              , 14   

   7:              , len(S.DeliveryExtension))    

   8:              AS DeleveryType   

   9:     ,S.[Description] AS Subscription_Desc  

  10:      ,S.LastStatus AS Subscription_Exec_Status  

  11:      ,C.CreationDate  

  12:      ,C.ModifiedDate  

  13:      ,S.LastRunTime  

  14:  FROM dbo.[Catalog] C  

  15:  INNER JOIN dbo.Subscriptions S   

  16:  ON S.Report_OID = C.ItemID  

  17:  INNER JOIN dbo.ReportSchedule R   

  18:  ON R.SubscriptionID = S.SubscriptionID  

  19:  --WHERE C.NAME = 'ReportsExecutionLog'   

  20:  --Pass Report Name as C.NAME

If you run the above query, the result would look like below figure.

image

I am using Data Driven Subscription which I have created/discussed in my last post, as I can pass Parameter values to the report.

As we discussed, If you create Subscriptions in SSRS, a SQL Server Job will be created in the given instance of SQL Server to

Execute the report on scheduled time.

Please go through the below figure, where you can find the SQL Server Jobs with the names resulted from the above Query.

image

These Subscription Jobs will execute my EmpDeptReport Report, for the given scheduled time while creating the subscriptions.

In my last post on Data Driven Subscription I have created two SQL Server tables, to pass the Input parameter values to both

E-Mail and FileShare type of Data Driven Subscriptions. Please go through the below figure to find the Schema.

image

 

We can use above SQL Jobs and tables to execute the EmpDeptReport Report, from a Stored Procedure / SSIS Package.

Please find the below script to execute a SQL Job.

   1: -- Exeecute the below script to start a job.  

   2: USE msdb ;

   3: GO

   4:  

   5: EXEC dbo.sp_start_job N'A151B953-4A14-4B91-83A7-377ED5451E12' ;

   6: GO

I have changed the Report parameter value in the Subscription input table and used the above script to deliver EmpDeptReport Report into a FileShare path.

image

Please go through the below figure, as soon as I executed the Subscription Job, EmpDeptReport Report was rendered in Excel format into the Shared path folder.

image

Similarly you can use the E-Mail Data Driven Subscription Job to deliver the report for given Report parameter value.

Please send me the issues/queries you have while following the above approach.

All the Best..  Smile

- Sunil Reddy Enugala

Posted in MS BI Related | Tagged: , , , , , , , | 2 Comments »

How to create Data Driven Subscription in SSRS

Posted by Sunil Reddy Enugala on August 26, 2013

Hi, In my last post we have discussed regarding Subscription in SSRS and also a small example of creating  Standard Subscription for SSRS Report.

Today, lets discuss about creating a Data Driven Subscription for a SSRS Report.

I have created a dummy report which gives the Employee Details for a given department. Please go through below dummy report.

image

Now click on the drop down button of the report for which you want to create a Data Driven Subscription and select Manage option

image

Now select the Data Sources tab and give the Connection string and Credentials details as shown in the below figure. Click on Test Connection button to ensure the given details.

image

Go to the Subscriptions tab and click in New Data-driven Subscription button as shown in the below figure.

image

We need to follow 7 steps to create a Data-driven Subscription, please go through below figures to know about those steps.

Step 1:

image

in the above step we need to mention:

  • Description of Data-driven Subscription
  • Type of delivery to the recipients
  • Data Source for the recipients information

There are three types of delivery options available for Data-driven Subscriptions

  1. Windows File Share : Delivers report to a shared folder.
  2. E-mail : Delivers report through E-mail as an attachment or URL link.
  3. Null Delivery Provider : This type of delivery is used to preload a cache with ready-to-view parameterized reports. Mainly to improve report server performance by preloading the cache.

SSRS is not confined to only 3 type of deliveries, you can create custom delivery extensions on which we can discuss through my next post.

In my example I have used Windows File Share as a delivery option and checked specify for this subscription only to declare data source for the recipients information.

Now click on Next button.

Step 2:

image

In the above step we need declare Connection string and Credentials to access data source of the recipients details. After specifying the details click on Next button.

Step 3:

image

Now I have created a table(dbo.ReportInput) with the delivery extension settings and Report parameters as column names. Please go through the below figure.

image

I have inserted details like delivery file name, delivery path, render format, report parameter value etc., into the table.

In the Query tab shown in the above figure, write the query to fetch the delivery setting details including the parameter value of the report. Click on the Validate button to validate the query. after validating successfully click on Next button.

Step 4:

image

The columns which mentioned in the select query of Step 3(Query tab) will be used  in Step 4/Step5. Check the radio button Get the values from the database: to map the respective columns of the ReportInput table.

Now click on the Next button to map the Report Parameter column.

Step 5:

image

Map the Dept_Name column of dbo.ReportInput table to the parameter of the Report as shown in above figure. Now click on Next button to schedule the Data-driven Subscription.

Step 6:

image

Check the option On a schedule created for this subscription  and click on Next button to schedule the subscription.

Step 7:

image

Provide the schedule details as shown in the above figure and click on Finish button to complete the process of configuring Data-driven Subscription with Windows File Share delivery Option.

Go through the below figures to check the status of the scheduled Data-driven Subscription.

image

image

Creating Data-delivery Subscription with Email – Delivery Option:

Everything is same still Step 2 except selecting the delivery option as E-Mail.

Step 3:

image

I have created another table to store Email recipients information, please go through the below figure.

image

Click on Validate button to verify the Query  written in the Query tab. Click on Next button.

Step 4:

image

map the columns of dbo.EmailReportInput table with the options in Step 4.

Click on Next button.

Step 5:

image

map the Dept_Name of dbo.EmailReportInput table with the option in Step 5 and Click on Next button to schedule the Subscription.

Step 6:

image

check On a schedule created for this Subscription and click on Next button.

Step 7:

image

Provide the schedule details and click on Finish button to complete the process of configuring Data-driven Subscription with E-mail delivery option.

Go through the below figures to check the status of the scheduled Data-driven Subscription.

image

Let me know if you have any issues while implementing Data-driven Subscriptions.

Sunil Reddy Enugala

Posted in MS BI Related | Tagged: , , , , , | 10 Comments »

How to create Standard Subscriptions in SSRS

Posted by Sunil Reddy Enugala on June 11, 2013

Subscription is used to execute a report on scheduled time and deliver it to the End User as an E-mail attachment or to be dropped in a file share in any specific format (like pdf, doc, etc.,). If the report server is configured for SharePoint integrated mode, you can also send a report to a SharePoint library.

Simply we can say that subscriptions can be used to schedule and then automate the delivery of a report.

SSRS supports two types of subscriptions, Standard and Data Driven Subscription.

Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing.

We cannot use the expression to specify the values for the parameter when creating subscriptions.

Creating Standard Subscription:

I have a report which gives last 24 hours Report Execution log details. Please go through below figure which shows the preview of the report.

image

Please go through the below script which uses ReportServer database to get last 24 hours Execution log details of Reports .

   1: --@Date variable has datetime value 

   2: DECLARE @Date datetime = DATEADD( DD,-1, CONVERT( CHAR(8) , CURRENT_TIMESTAMP , 112 )) 

   3: SELECT c.name,   

   4: CASE    

   5: WHEN el.RequestType = 0 THEN 'Interactive'   

   6: WHEN el.RequestType = 1 THEN 'Subscription'   

   7: WHEN el.RequestType = 2 THEN 'Refresh Cache'   

   8: END AS RequestType,    

   9: el.Format,   

  10: el.TimeStart,   

  11: el.TimeEnd,  

  12:  DATEDIFF(ss,el.TimeStart,el.TimeEnd) AS 'TotalDuration(Sec)',  

  13:  (el.TimeDataRetrieval/1000.00) AS 'Data Retrieval Time(Sec)',  

  14:  (el.Timeprocessing/1000.00) AS 'Processing Time(Sec)',  

  15:  (el.TimeRendering/1000.00) AS 'Rendering Time(Sec)',  

  16:  CASE   

  17:  WHEN el.Source=1 THEN 'LIVE'  

  18:  WHEN el.Source=2 THEN 'Cache'  

  19:  WHEN el.Source=3 THEN 'Snapshot'  

  20:  WHEN el.Source=4 THEN 'History'  

  21:  WHEN el.Source=5 THEN 'Ad hoc(Report Builder)'  

  22:  WHEN el.Source=6 THEN 'Session'  

  23:  WHEN el.Source=7 THEN   

  24:              'Report Definition Customization Extension(RDCE)'  

  25:  END AS 'Source',  

  26:  el.status,  

  27:  el.ByteCount/1024.00 AS 'Size(Kb)',  

  28:  el.[RowCount] AS 'Number of Records'  

  29:  FROM ReportServer..ExecutionLogStorage EL   

  30:  INNER JOIN ReportServer..Catalog c ON c.itemid=el.reportid  

  31:  WHERE TimeStart>= @Date

  32:  ORDER BY TimeStart DESC

I have @Date parameter in the above script and configured default value(which gives yesterday’s date as a value) as shown in the below figure.

image 

The first thing while creating subscription is to add Credentials for the DataSource of the  Report.

Open Report Manager and Click on the Data Source(ReportServerDB) of the

report(ReportsExecutionLog) to which we need to create subscription.

                       image

Check Credentials stored securely in the report server option and specify windows credentials. also select the checkbox use as Windows credentials when connecting to the data source. Click on Test Connection button to check the connection. Now click on Apply button.

image

Click on Drop down button of the Report to which we need to create subscription and select Subscribe option.

image

On the Subscription page, select the delivery option as E-Mail/Windows File Share.

Windows File Share Delivery

Select Windows File Share as delivery option and create a shared folder and specify in the path tab. Specify the credentials to access the file and click on Select Schedule to automate the Report Execution.

image

Specify the time and day of current week and select radio button to repeat the schedule.Click OK button.

image

Now we have scheduled the report, Click on check box Use Default option as we can’t pass value to the parameter of the Report. Click OK button.

image

Subscription is created and is scheduled to 03:03PM, check the status of the subscription in the below figure. It has generated pdf file of the report and placed in the mentioned shared folder path.

image

Please go through the below figure of shared path folder.

image

E-Mail option

Again click on the drop down button of the Report. Select Subscribe option.

Note: E-Mail option will be available only after setting mail configuration in the Reporting Services Configuration Manager. Please go through the below figure to know the process.

image

After setting the Sender Address and SMTP Server property values, click on Apply button to complete the mail configuration.

Select the Delivery by Option as Email. Specify the Render Format and click on Select Schedule button to automate the report execution.

image 

Specify the schedule time and click on OK button.

image

Check Use Default option to pass default value as the parameter to the report. Click on OK button to complete the Subscription configuration.

image

Subscription is created and is scheduled at 4:10PM. Check the status of the subscription in the below figure. Mail was sent to given E-mail ID.

image

Now we have created Standard Subscriptions in two possible way. Once the Subscription is created for a Report, you’ll find a job under Jobs folder in the SQL Server Management Studio as shown in the below figure.

image

As we have created two subscription, we have seen two jobs in the above figure.

To know the details of exists Subscription , please run the below script using ReportServer database.

 

   1: SELECT R.ScheduleID AS JobName   

   2:  

   3:   ,C.NAME AS ReportName   

   4:  

   5:   ,C.[Path] AS ReportPath   

   6:  

   7:   ,S.SubscriptionID   

   8:  

   9:   ,SUBSTRING(S.DeliveryExtension   

  10:  

  11:            , 14   

  12:  

  13:            , len(S.DeliveryExtension))    

  14:  

  15:            AS DeleveryType   

  16:  

  17:   ,S.[Description] AS Subscription_Desc  

  18:  

  19:   ,S.LastStatus AS Subscription_Exec_Status  

  20:  

  21:   ,C.CreationDate  

  22:  

  23:   ,C.ModifiedDate  

  24:  

  25:   ,S.LastRunTime  

  26:  

  27: FROM dbo.[Catalog] C  

  28:  

  29: INNER JOIN dbo.Subscriptions S   

  30:  

  31: ON S.Report_OID = C.ItemID  

  32:  

  33: INNER JOIN dbo.ReportSchedule R   

  34:  

  35: ON R.SubscriptionID = S.SubscriptionID  

  36:  

  37: --WHERE C.NAME = 'ReportsExecutionLog'   

  38:  

  39: --Pass Report Name as C.NAME

Let me know your comments and queries.

I’ll meet you again with my post on Data Driven Subscription.Smile

Posted in MS BI Related | 8 Comments »

Track file attributes

Posted by Sunil Reddy Enugala on June 6, 2013

Why you need to go for custom task or script task to get the source file attributes. If you are familiar with SQL Server T-SQL than you can get file details using OLE Automation Procedures.

 

Code to enable OLE Automation:

   1:  Sp_configure 'show advanced options', 1;
   2:  Go
   3:  Reconfigure;
   4:  Go
   5:  Sp_configure 'ole automation procedures', 1;
   6:  Go
   7:  Reconfigure;
   8:  Go

 

Script to check the existence of the file, if the file exists than get the file attributes.

   1:  DECLARE 
   2:  @Filename VARCHAR(100) = 'D:\DelmeSoon.txt'
   3:      --give your file name with path
   4:  ,@hr INT
   5:  ,@objFileSystem INT
   6:  ,@objFile INT
   7:  ,@ErrorObject INT
   8:  ,@ErrorMessage VARCHAR(255)
   9:  ,@Path VARCHAR(100)
  10:  ,@size INT
  11:  ,@ShortPath VARCHAR(100)
  12:  ,@Type VARCHAR(100)
  13:  ,@DateCreated DATETIME
  14:  ,@DateLastAccessed DATETIME
  15:  ,@DateLastModified DATETIME
  16:  ,@Attributes INT
  17:   
  18:  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject'
  19:      ,@objFileSystem OUT
  20:   
  21:  IF @hr <> 0
  22:  BEGIN
  23:  SET @errorMessage = 'Error in creating the 
                             file system object.'
  24:   
  25:      RAISERROR (
  26:              @errorMessage
  27:              ,16
  28:              ,1
  29:              )
  30:  END
  31:  ELSE
  32:  BEGIN
  33:      EXEC @hr = sp_OAMethod @objFileSystem
  34:          ,'GetFile'
  35:          ,@objFile out
  36:          ,@Filename
  37:   
  38:      IF @hr <> 0
  39:      BEGIN
  40:          SET @errorMessage = 'File not found '
  41:   
  42:          SELECT @Filename AS [FileName]
  43:              ,@errorMessage AS [Status]
  44:      END
  45:      ELSE
  46:      BEGIN
  47:      -- to get the file path                        
  48:          EXEC sp_OAGetProperty @objFile
  49:              ,'Path'
  50:              ,@path OUT
  51:   
  52:      -- to get the file size                        
  53:          EXEC sp_OAGetProperty @objFile
  54:              ,'size'
  55:              ,@size OUT
  56:   
  57:          IF @hr = 0
  58:              EXEC @hr = sp_OAGetProperty @objFile
  59:                  ,'ShortPath'
  60:                  ,@ShortPath OUT
  61:   
  62:          IF @hr = 0
  63:              EXEC @hr = sp_OAGetProperty @objFile
  64:                  ,'Type'
  65:                  ,@Type OUT
  66:   
  67:          IF @hr = 0
  68:              EXEC @hr = sp_OAGetProperty @objFile
  69:                  ,'DateCreated'
  70:                  ,@DateCreated OUT
  71:   
  72:          IF @hr = 0
  73:              EXEC @hr = sp_OAGetProperty @objFile
  74:                  ,'DateLastAccessed'
  75:                  ,@DateLastAccessed OUT
  76:   
  77:          IF @hr = 0
  78:              EXEC @hr = sp_OAGetProperty @objFile
  79:                  ,'DateLastModified'
  80:                  ,@DateLastModified OUT
  81:   
  82:          IF @hr = 0
  83:              EXEC @hr = sp_OAGetProperty @objFile
  84:                  ,'Attributes'
  85:                  ,@Attributes OUT
  86:   
  87:          IF @size = 0
  88:          BEGIN
  89:              SET @errorMessage = 'Empty file  '
  90:   
  91:              SELECT @Filename AS [FileName]
  92:                  ,@errorMessage AS [Status]
  93:          END
  94:          ELSE
  95:          BEGIN
  96:              SET @size = (@size / 1024.0)
  97:              SET @errorMessage = 'File Exists  '
  98:   
  99:      SELECT @Filename AS [FileName]
 100:           ,@errorMessage AS [Status]
 101:           ,@size AS [FileSize_kb]
 102:           ,@ShortPath AS 'ShortPath'
 103:           ,@Type AS 'Type'
 104:           ,@DateCreated AS 'DateCreated'
 105:          ,@DateLastAccessed AS 'DateLastAccessed'
 106:          ,@DateLastModified AS 'DateLastModified'
 107:                  ,CASE 
 108:                      WHEN @Attributes = 0
 109:                          THEN 'Normal'
 110:                      WHEN @Attributes = 1
 111:                          THEN 'ReadOnly'
 112:                      WHEN @Attributes = 2
 113:                          THEN 'Hidden'
 114:                      WHEN @Attributes = 4
 115:                          THEN 'System'
 116:                      WHEN @Attributes = 8
 117:                          THEN 'Volume'
 118:                      WHEN @Attributes = 16
 119:                          THEN 'Directory'
 120:                      WHEN @Attributes = 32
 121:                          THEN 'Archive'
 122:                      WHEN @Attributes = 64
 123:                          THEN 'Alias'
 124:                      WHEN @Attributes = 128
 125:                          THEN 'Compressed'
 126:                      END AS 'Attributes'
 127:  /*
 128:  Attribute values table
 129:  ----------------------------
 130:  Normal    0  Normal file. No attributes are set.
 131:  ReadOnly  1  Read-only file. Attribute is read/write.
 132:  Hidden    2  Hidden file. Attribute is read/write.
 133:  System    4  System file. Attribute is read/write.
 134:  Volume    8  Disk drive volume label. 
                        Attribute is read-only.
 135:  Directory 16 Folder or directory. 
                        Attribute is read-only.
 136:  Archive   32 File has changed since last backup. 
                      Attribute is read/write.
 137:  Alias     64 Link or shortcut. 
                      Attribute is read-only.
 138:  Compressed  128 Compressed file. 
                       Attribute is read-only.
 139:  */
 140:          END
 141:      END
 142:  END
 143:   
 144:  EXEC sp_OADestroy @objFileSystem
 145:   
 146:  EXEC sp_OADestroy @objFile

 

 

Let me know your comments and queries.

Posted in Distinct Categories | Leave a Comment »

OLE Automation Procedures to check the Source File Existence

Posted by Sunil Reddy Enugala on June 6, 2013

To check existence of file in a given folder, we can use OLE automation procedures. Please go through the below SQL Script to know the process.

We need to enable ole automation procedures property of the SQL Server to use it.

There are two ways to enable the property, first process is using below code.

   1:  Sp_configure 'show advanced options', 1;
   2:  Go
   3:  Reconfigure;
   4:  Go
   5:  Sp_configure 'ole automation procedures', 1;
   6:  Go
   7:  Reconfigure;
   8:  Go

Second process is to select SQL Server Properties –> Click on Facets –> Select Surface Area Configuration as a facet –> set OLE automation procedures to True.

Please go through the below script code:

   1:  DECLARE 
   2:  @Filename VARCHAR(100) = 'D:\DelmeSoon.txt'  
   3:                 --give your filename with folderpath
   4:  ,@hr INT 
   5:  ,@objFileSystem INT
   6:  ,@objFile INT
   7:  ,@ErrorObject INT
   8:  ,@ErrorMessage VARCHAR(255)
   9:  ,@Path VARCHAR(100)
  10:  ,@size INT
  11:  
  12:  
  13:   
  14:  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject'
  15:      ,@objFileSystem OUT
  16:   
  17:  IF @hr <> 0
  18:  BEGIN
  19:      SET @errorMessage = 'Error in creating the file system object.'
  20:   
  21:      RAISERROR (
  22:              @errorMessage
  23:              ,16
  24:              ,1
  25:              )
  26:  END
  27:  ELSE
  28:  BEGIN
  29:      EXEC @hr = sp_OAMethod @objFileSystem
  30:          ,'GetFile'
  31:          ,@objFile out
  32:          ,@Filename
  33:   
  34:      IF @hr <> 0
  35:      BEGIN
  36:          SET @errorMessage = 'File not found '
  37:   
  38:          SELECT @Filename AS [FileName]
  39:              ,@errorMessage AS [Status]
  40:      END
  41:      ELSE
  42:      BEGIN
  43:          -- to get the file path                        
  44:          EXEC sp_OAGetProperty @objFile
  45:              ,'Path'
  46:              ,@path OUT
  47:   
  48:          -- to get the file size                        
  49:          EXEC sp_OAGetProperty @objFile
  50:              ,'size'
  51:              ,@size OUT
  52:   
  53:          IF @size = 0
  54:          BEGIN
  55:              SET @errorMessage = 'Empty file  '
  56:   
  57:              SELECT @Filename AS [FileName]
  58:                  ,@errorMessage AS [Status]
  59:          END
  60:          ELSE
  61:          BEGIN
  62:              SET @size = (@size / 1024.0)
  63:              SET @errorMessage = 'File Exists  '
  64:   
  65:              SELECT @Filename AS [FileName]
  66:                  ,@errorMessage AS [Status]
  67:                  ,@size AS [FileSize_kb]
  68:          END
  69:      END
  70:  END
  71:   
  72:  EXEC sp_OADestroy @objFileSystem
  73:   
  74:  EXEC sp_OADestroy @objFile

Let me know your comments and Queries.

Posted in Scripts | 1 Comment »

Execute Java Class file from SSIS – Part 3

Posted by Sunil Reddy Enugala on June 4, 2013

Recently we have discussed about two processes of executing Java Class file.

Execute Java Class file from SSIS

Execute Java Class file from SSIS – Part 2

Let’s discuss about another process to execute Java Class file from SSIS.

Process 3:

  • In this process we will use C# code to execute Java Class file
  • This process is as flexible as process 1
  • Knowledge of C# is pre-requisite Drag a Script Task component to the SSIS Control Flow Designer pane

Drag a Script Task component to SSIS Control Floe designer pane.

Create five SSIS variables as follows.

                             image

  • FilePath: Path of the file which need to be deleted.

         In our example Filepath value is D:\\FileDeletion\\DellmeSoon\\DelmeSoon.txt

  • JavaClass: Path of the Java Class file. Value is D:\\FileDeletion\\DeleteFile.class
  • JavaDir: Path of the java.exe application.

Value is C:\\Program Files\\Java\\jdk1.7.0_17\\bin\\java.exe

  • WorkingDir: Folder path of the java.exe application.

We need to enable the EvaluateAsExpression property of WorkingDir variable and  set the expression as shown in the below figure.

SUBSTRING( @[User::JavaDir] , 1, LEN( @[User::JavaDir] )-FINDSTRING(REVERSE(@[User::JavaDir]),"\\",1)+1) 

                                    image

  • ExecStatement: Contains Query syntax to execute Java Class file.

We need to enable the EvaluateAsExpression property of ExecStatement variable and  set the expression as shown in the below figure.

"-classpath " + SUBSTRING( @[User::JavaClass] , 1, LEN( @[User::JavaClass] )-FINDSTRING(REVERSE(@[User::JavaClass]),"\\",1)+1) + " "+SUBSTRING (@[User::JavaClass], LEN( @[User::JavaClass] ) – FINDSTRING( REVERSE( @[User::JavaClass] ), "\\", 1) + 2,  LEN (RIGHT( @[User::JavaClass], FINDSTRING( REVERSE( @[User::JavaClass] ), "\\", 1 ) – 1 ) ) – FINDSTRING( REVERSE( @[User::JavaClass] ), ".", 1 )  ) + " "+ @[User::FilePath]

                            image

Now double click on Script task and select ExecStatement, JavaDir and WorkingDir as Script task ReadOnlyVariables as shown in below figure.

                      image

click on Edit Script to write the script to execute Java Class file as shown in below figure.

image

 

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;

namespace ST_18e16a8ea284440083cdac12d6812db0.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0"
            , Publisher = "", Description = "")]
    public partial class ScriptMain : 
    Microsoft.SqlServer.Dts.Tasks.ScriptTask
                      .VSTARTScriptObjectModelBase
    {
        private int ExecuteProcess(string cmd
                                    , string cmdParams
                                    , string workingDirectory
                                    , int timeout
                                    , out string stdOutput)
        {
            using (Process process = Process.Start(
                     new ProcessStartInfo(cmd, cmdParams)))
            {
                process.StartInfo.WorkingDirectory = workingDirectory;
                process.StartInfo.UseShellExecute = false;
                process.StartInfo.RedirectStandardOutput = true;
                process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
                process.Start();
                stdOutput = process.StandardOutput.ReadToEnd();
                return process.ExitCode;
            }
        }
        public void Main()
        {
          //  Dts.TaskResult = (int)ScriptResults.Success;
            string stdOutput;
            string varStatement = 
                        (string)Dts.Variables["ExecStatement"].Value;
            string varWorkingDir = 
                        (string) Dts.Variables["WorkingDir"].Value;
            string varJavaDir = 
                         (string)Dts.Variables["JavaDir"].Value;                
            int myTimeout = 5;
            int exitCode = new ScriptMain().ExecuteProcess(
                                             @varJavaDir
                                             ,varStatement
                                             ,@varWorkingDir
                                             ,myTimeout
                                             ,out stdOutput);
            Console.WriteLine(stdOutput);
        }
    }
}

Now build the script and close the editor. Click Ok on Script Task wizard.

                           image

Now press f5 to execute the Script task.

                       image

Let me know your comments and queries.

Posted in MS BI Related | 1 Comment »

Execute Java Class file from SSIS – Part 2

Posted by Sunil Reddy Enugala on June 2, 2013

In my previous post I have shown you how to execute Java Class file, let me show another process to execute Java Class file.

Process 2:

In this process

  • We are executing java class file using a batch file.
  • We have to hardcode the file path in the batch file.
  • No need of configuring SSIS variables.
  • Not flexible as process 1.
  • Simple than process 1 as we are hardcoding everything.

I have placed java class file in D:\FileDeletion\ path. Please go through the below figure to know about the content of batch file.

Syntax to execute Java Class file:

java –classpath <folder path of class file> <filename of class file without extension> <parameters>

image

I have placed batch file in the same folder(D:\FileDeletion\ ).

image

Drag a Execute Process task in SSIS Control Flow Designer pane, Edit the Executable and WorkingDirectory properties as shown in below figure.

image

I have placed the file which needs to be deleted in following folder path.

D:\FileDeletion\DellmeSoon\

image press f5 and see..

image

Please let me know your comments and queries.

Posted in MS BI Related | 2 Comments »

Execute Java Class File from SSIS

Posted by Sunil Reddy Enugala on June 2, 2013

Hi, recently I came across interesting requirement through my friend Goutham Aakiti. The requirement is like processing Java Class file using SSIS. I was very curious to know how to achieve this, spent some time and found that there are two ways to process Java Class File.

Let me show how we can do this.

 

Prcoess 1:

I have a Java Class File which deletes a file. Please go through the below Java code which accepts file path as argument to delete a file.

image

If you compile the above Java Code, the compiler will generate binary code file which is called as Class File. We will be using the Java Class file further to delete file.

 image

Drag a Execute Process task component to the SSIS Control Flow designer pane

Create four variable to pass the property values dynamically.

imageVariable Descriptions

FilePath: Represents the path of the file which we are about to delete.

JavaClass: Represents the path of Java Class file which we use to delete a file.

JavaDir: Represents the path of Java.exe, which we use to execute Java Class file.

WorkingDir: Represents the Folder path of Java.exe.

Only variables like FilePath, JavaClass and JavaDir needs input. We need to set variable expression for WorkingDir variable.

Please go through the below figure to know about setting of variable expression for WorkingDir variable. Click on WorkingDir variable and select properties(press f4 ). Set the EvaluateExpression value to True. Set the Expression value as below.

SUBSTRING( @[User::JavaDir] , 1, LEN( @[User::JavaDir] )-FINDSTRING(REVERSE(@[User::JavaDir]),"\\",1)+1) 

image

Please go through the below figure to know about the property values of Execute Process task.

image

Executable property needs the path value of  Java.exe, we will be passing the value through JavaDir variable as an property expression.

Arguments property needs statement to execute the Java Class File.

WorkingDirectory property needs the folder path of Java.exe, we’ll be passing the value through WorkingDir variable.

Please go through the below figure to know about the setting of above properties values. Properties like Executable and WorkingDirectory will be getting values directly from variables like JavaDir and WorkingDir.

image

We need to set an expression for Arguments property, please go through the below figure to know about the setting of expression.

"-classpath " + SUBSTRING( @[User::JavaClass] , 1, LEN( @[User::JavaClass] )-FINDSTRING(REVERSE(@[User::JavaClass]),"\\",1)+1) + " "+SUBSTRING (@[User::JavaClass], LEN( @[User::JavaClass] ) – FINDSTRING( REVERSE( @[User::JavaClass] ), "\\", 1) + 2,  LEN (RIGHT( @[User::JavaClass], FINDSTRING( REVERSE( @[User::JavaClass] ), "\\", 1 ) – 1 ) ) – FINDSTRING( REVERSE( @[User::JavaClass] ), ".", 1 )  ) + " "+ @[User::FilePath]

image  

Lets see how it works.

I want to delete a file DelmeSoon.txt from path D:FileDeletion\DellmeSoon\

image

press f5 and see, the Java Class file will deletes the.

image

Let me know your comments and queries on my post.

Posted in MS BI Related | 4 Comments »

2012 in review

Posted by Sunil Reddy Enugala on December 31, 2012

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

4,329 films were submitted to the 2012 Cannes Film Festival. This blog had 19,000 views in 2012. If each view were a film, this blog would power 4 Film Festivals

Click here to see the complete report.

Posted in MS BI Related | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 654 other followers