In this article, we are going to see how to configure CSV file as source in TIBCO Cloud Integration. Prerequisite: TIBCO Cloud Integration Subscription. CSV file (Here, Journal.csv) Steps: Provide access to your Text files. The FolderAccess.txt file controls access to the files that can be opened by TIBCO Cloud Integration or processing. Open the FolderAccess.txt file in Notepad. Location for FolderAccess.txt: Here: C:\Program Files (x86)\Scribe Software\TIBCO Cloud IntegrationAgent\Connectors\Scribe.Connector.Text Add a line in the FolderAccess.txt file for each directory path that contains text files. Open ODBC Data Source Administrator. In the System DSN tab, add a driver of Microsoft Access Text driver Add Data Source name and description. Select directory where your CSV file is stored. ‘Save as ‘the .CSV file as schema.ini file. Login to TIBCO Cloud Integration URL: https://app.scribesoft.com/ Create a new connection by selecting the connector type as Text as Source. Fill in the required details. In the Location Tab, paste the path where your .csv file is stored. In the Entities tab, give the entity name and fill the details. In the Fields tab, click on settings icon and Refresh schema. All the fields in the CSV file will appear. The data type of all fields are by default ‘Text’, you can change the datatype of the fields. Test the Connection and press ‘OK’ to save the connection.

Error in Scribe Insight: “Conversion failed when converting date and/or time from character string” when connected to SQL Adapter. When do we get this error? When we create a SQL connection using ODBC or OLEDB adapter in Scribe Insight and use the same either as Source or Target connection in Scribe DTS. What is the root cause of this error/issue? It’s the date-time format of the machine where the scribe Insight DTS is being run. How can we reproduce the error? In the windows, DateTime settings, change the format for time with separator as (.) instead of (:). Open the Scribe Workbench and create a sql connection using odbc\oledb connection, create a target connection, map some fields and run the DTS. You will get the error. Resolution: Simply change the time format to have the colon separator instead of (.) Hope this helps. I have found that having the time format other than colon separator creates error while working with other ETL tools as well.

In CRM, when a file is attached in Notes sections, it gets stored in “Annotation” Entity. Notes with Files attached have the isDocument set to 1 and without notes to 0. Since Annotation entity contains files attached in all other CRM entities, it is determined by “ObjecttypeCode”. Example for Account ObjectTypeCode is 1. ObjectId field in Annotation holds the GUID of the related entity’s Primary Key whose file is attached. Example Accounts Accountid is ObjectId of Annotation. When we create a CRM connection for Annotation, we get 2 fields as highlighted below “bodybinary” and “documentbody”. When we migrate from CRM   to SQL, we need to Map the source CRM bodybinary field to vfAttachment in Target. If you map to document body, the files gets migrated however when you open it, it looks like corrupted. (During CRM to CRM migration, ensure that both the fields objecttypecode and objectidtypecode are mapped, else migration will get fail.) If we need to pull the attached files from CRM and insert the same in SQL Table and write the same files in to Windows folder, things get bit tricky here. While creating the SQL Table what will be the datatype for column that will be storing the attached file?  The datatype you need to select is as “Image”. If you select longvarchar or text or binary, when the file is written on disk and open, you get a gibberish file with unreadable content. Please note even though the Data type of the SQL Table for DocumentBody is defined as “Image”, Scribe shows the datatype as “binary”. Also during mapping it is advised to map the documentbody field at the very end. We had encountered this issues of files getting corrupted and Scribe support team suggested this approach and it worked. With above tips, you can ensure successful migration of documents from CRM to SQL and later when writing to local drives.

In this blog article, we will explain you on how to connect Microsoft Visual Studio TFS inside of Power BI. Below are the steps to connect over Financial OData: Open the Power BI.com online account Select Get Data option from the bottom menu Click on the Get service option Select Visual Studio TFS from the list and click on “Get it Now”. New Connection window appears which asking for Account Name to connect TFS. You need to write only name of the account (beginning name of visualtstudio.com) and write specific project name or * if you want to get data for all existing projects. After Connecting it will ask for the Authentication so need to login with your id who has access on TFS. So finally, you get out of the box dashboard inside of Power BI. As per below screen shots, it gives so many individual sheets to give information on Open Bug Status, Latest Version Control, Top Updated users, Different Project Build analysis and all. We can also connect TFS from Power BI desktop tools as well, so using that we will design reports and charts based on our requirements. In the Power BI Desktop, we need to choose Visual studio team Services option from Online service. After that it will ask for same account and Project details of the TFS, and we can have list of tables objects for selecting data inside of Graphs or charts.  

In this blog article, we will explain how to move data from SQL server to Excel file using SQL Server Integration Services (SSIS). For moving data from SQL Server to Excel you need to create package, to import data from SQL Server to Excel. Step 1: – Create a Project You need to create a project for moving data from SQL Server to Excel. For this go to visual studio-> File-> New-> Project Once you click on project then choose a business intelligence option to create SSIS project. Then select Integration Services. And then Click on Integration Server Project. Specify name to the project and then click on OK. Step 2: – Create a Data Flow Task We need to add a data Flow task to control flow tab of your package. After adding the data Flow task double click on it will open the Data flow tab. Step 3: – Creating the SQL Server Source Our aim is to move data from SQL Server to Excel. So here our source is SQL Server and Destination is Excel So, drag and drop the OLE DB Source component from the Source then right click on that component then click on edit. Below window will open. Then click on new Click on new Then select your Server name, Authentication type. Select your database name means from which database you retrieving the data. Then click on Test connection to verify that our connection is right or not. And then click on OK. Again, click on OK. Then select the table from Specified database. You can also verify data by clicking on preview button then click on OK. Step 4: – Creating the Excel Destination Our destination is to put the data in excel file. So, drag and drop the Excel destination component from the Destination. Then click on OLE DB Source component and then drag blue line and drop on Excel destination. then right click on that component then click on edit. Below window will open. Then click on new then set the path where you want to create an excel file. Click on OK. Then click on another new button to select the excel sheet name. Click on ok -> ok Then click on drop down bar choose excel file then click on mapping. and then OK. Here, your package is ready execute. Before executing the package verify the source and destination. In destination, there created one excel file with name that you specified in earlier in excel destination with no data. For execute a package, go to Solution explorer ->under the project name -> SSIS Packages -> Package.dtsx. Then right click on that .dtsx and the execute package. it will show the number of rows inserted in Destination i.e. in excel file. And then check the destination. All the data is integrated in destination.

In this blog article, we will explain about the new updates of Power BI related to Dropdown slicer, Matrix Conditional Formatting and Hierarchical axis. Dropdown slicer: In Slicer, earlier there were only one option to show the data i.e. list. But now we have one more option as dropdown. Means now we have two option on Slicer as List and dropdown. This slicer is very useful when we have lots of category items in our slicer. When we create a normal list slicer, we can change it into drop down by using dropdown option. After selecting the dropdown option, the slicer will look like below: And after click on Drop down arrow Matrix Conditional Formatting: Matrix Conditional Formatting is a new feature. Using this feature we can change the various setting like we can change the colour of cell based on the value. We can set different colour to lowest and highest value in particular category. Refer the below figures for better understating. After click on Conditional formatting you will get the below window than choose whatever colour you want for minimum and maximum value and then click on ok. Once you click ok, you’ll see the formatting applied as below. Hierarchical axis: Hierarchical axis allows us to clearly see the hierarchy within the visual. We can use hierarchical labels by turning off the label concatenation in the formatting pane. Below figure show result when the concatenation label is on. To turn off the concatenation label select visualization and then click on format then expand the X-axis. After turning off the concatenation label you will see the formatting applied as below.  

In this blog article, we will explain you on how to connect dynamics 365 financials service connector inside of Power BI. Below are the steps to connect over Financial OData: Open the Power BI.com online account Select Get Data option from the bottom menu Click on the Get service option Select dynamics 365 financial from the list and click on “Get it Now” New Connection window appears which asking for OData URL to connect financial account. So for getting OData url we need to connect our D365 financial account in browser window and then search for web service. Select Power BI report selection Url from list of objects. Copy this url up to “Cronus” company name into powerbi connection window. Your password is the web service access key of the Admin user and you can get it from users tab. So, after connecting to the financial ODATA services we will get readymade dashboard created for financials. And now we can embedded this dashboard inside of Dynamics financial account. So list of reports appear and we can select required one from that. Note: As of now, Microsoft allow us to show only one graph/tiles inside of Financial embedded page, we hope more updates on this features.

This blog explains how to set Pagination with fixed number of rows per page with table headers repeated on every page of SSRS report. Steps: Insert a Tablix on report and set table Headers. Set Row Data for Tablix Create a “Parent Group” on Tablix by Expression as below Note: We have set number of records per page as 10 in Ceiling Function. Open Group Properties of “Group1” and change Page Breaks settings as below Delete existing Sorting settings of Group as highlighted and click “OK” Delete Column with header “Group1” with option “Delete columns only” from Tablix Set below Data Expression in SR.No. column of Tablix Run Report and it renders as below  

Fetch XML based SSRS reports which are deployed in Microsoft Dynamics CRM online. Case: In Report, you want to sort the Tablix date as per the date field in descending order. (In the below example “Date” column). When you deploy the report and run the report, it is observed that date order is not as expected. Resolution / Work around Steps: Login to CRM and verify the default formatting for Dates. (Settings -> Administartion -> System Settings -> Formats. Check how the Short date is displayed. (By Default todays date will be displayed) Here the format is MM-dd-YYYY. So we know that the data is stored in what format of Date. Also as a best practice, we should check if the date field is empty and handle the same and format the date field. I used below expression to achieve this. =IIF(ISNOTHING(Fields!cf_calibrationdate.Value) Or Fields!cf_calibrationdate.Value = “”, ” “, Format(Cdate(Fields!cf_calibrationdate.Value), “MM-dd-yyyy”)) Now Go to Report designer in SQL Server Date Tool and Go to Tablix Properties and Go to Sorting. You need to add the sorting options through expressions. Now the trick is since it’s a date, we will sorting using the value field as below. You can run the report in preview window and verify if the Tablix data is sorted as per the date field.

In this article, we will be going through the steps for installation of BizTalk Server 2016. Below steps are for installation of BizTalk Server (Standard Edition) 2016 with Microsoft SQL server 2016 Standard edition. The environment is Windows Server 2016 on Azure. Windows Server configuration is as below: If your computer name is longer than 15 characters, BizTalk Server configuration fails. You can rename using power shell or from Server Manager (Dashboard -> Local Server -> Properties) Before installing BizTalk Server 2016, we will need to install the SQL Server. Below are the steps for the installation of SQL Server 2016 Standard edition. Here we are installing BizTalk server and SQL server on same machine. If these are on separate machine, additional pre – requisites steps are required. What are the pre-requisites for BizTalk server 2016 installation? Account should be part of administrators group, with which you are doing the installation. IIS should be enabled. WIF (optional) If you intent to use SharePoint Services Adapter. SMTP Server (optional), EXCEL 2013 (optional), SQL Server Database Mail (optional), If you intent to use BAM Alerts (Business Activity Monitoring) Visual Studio 2015 (Optional) (For creating BizTalk Projects) During installation these pre-requisites components gets auto installed: Microsoft SQL XML 4.0 with Service Pack 1 Microsoft Office Web Components Microsoft SQL Server 2016 ADOMD.NET Setup runtime files for AMD64 platform Setup runtime files Enterprise Single Sign-On Server Enterprise Single Sign-On Administration Microsoft Document Explorer 2008 Installation of SQL Server 2016 (Standard Edition) File Size – 2.1 GB Installation of SQL Server 2016 Download SQL Server 2016 from msdn. After the download is completed, mount the ISO file and select setup.exe to install. Run as administrator to start the installation. Accept the License Terms. Click in Next Install setup files – SQL server setup files are installed in system in this step. Time Taken – 32 Min In Features Selection, select only as ticked below. Provide a name to the names Instance. If you choose Default Instance, it defaults the Instance ID as MSSQLSERVER Named instance CFS_BIZTALK Provide the Account name and Password for the SQL services. You can choose either to have windows authentication Mode or Mixed Mode (SQL + Windows Authentication Mode) Specify the SQL server administrator. Click on ACCEPT in tab for “Consent to install Microsoft R Open”. Click Next. In “Ready to install” tab — click on Install. You will need to separately download SSMS as its not part of the installation setup. ** Download and Install SQL Server Management Studio version 16.5  (https://msdn.microsoft.com/en-us/library/mt238290.aspx) File Size – 894 MB Download the BizTalk server 2016 Standard Edition from MSDN. (File Size – 727 MB) Mount the downloaded file en_biztalk_server_2016_standard_x64_dvd_9503266.iso (Right on file and select the “Mount” option. Accept the License terms and conditions Below is the list of Microsoft BizTalk Server 2016 Components that will be installed. BizTalk EDI/AS2 Runtime Documentation Server Runtime Windows Communication Foundation Adapter Windows Communication Foundation Administration Tools Administration Tools and Monitoring Enterprise Single Sign-On Administration Module Additional Software Enterprise Single Sign-On Master Secret Server Business Rules Components BAM Alert Provider BAM Client BAM-Eventing Project Build Component It may be necessary to stop one or more system services during installation. For information on the affected services, click Help. You can add or remove additional components after this installation using the “Microsoft BizTalk Server 2016” entry in the Add/Remove section of the Control Panel. During installation, you may be prompted for server reboot. To ensure you get automatically logged in you can provide the credentials using the set option. During installation, the first component is the SQL XML 4.0 SP1. It gets fails and comes back with error message as below. For resolution, if you download and install Microsoft SQL XML 4.0 with SP1, you will again get error as below. Error indicates that it is looking for .Net Framework version 2.0. (This error comes even though your system has 4.5 or higher version of .Net Framework. Resolution: Install the .Net Framework 3.5 Features (Includes 2.0 and 3.0) from Windows server manager -> Roles and Features -> .NET Framework 3.5 Features -> .NET Framework 3.5 (includes .NET 2.0 and 3.0) Select the components for installation as below: Finally, we get the window as below indicating we have installed Microsoft BizTalk Server Successfully. Tick the box for “Launch BizTalk Server Configuration” Configuring BizTalk Server 2016 Select the Custom configuration and provide the user name and password. All BizTalk services will run under these credentials. You will be greeted with below pop info window message if you choose the same windows user with which yoo installed the BTS2016. This is important step, you need to provide the encryption password and reminder text. Default location of Backup location is as below: Back up file location: C:\Program Files\Common Files\Enterprise Single Sign-On\SSO056B.bak If this is first installation, you will be creating a new BizTalk Group. After Group creation, you will be registering the run time components by selecting options as below. You can configure Business Activity Monitoring alerts in this option. (Since I have not configured the SQL Mail, I am skipping this step). This is the final and important configuration step for configuring EDI and AS2 protocol for message exchange. You will see configuration wizard with success message. Open the Overview tab, you will see as below indicating successful configuration. (I have not configured BAM Portal) You can check the version of the BizTalk server installed through Registry key at path “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0” At the end of installation, you can also review the log file for detail of installation or troubleshoot any error or warning during installation process. It is advised to export and back up the configuration of BTS2016. (Default path of back up is “C:\Program Files (x86)\Microsoft BizTalk Server 2016”)