MSBI

Hi All, When you are using Varchar (MAX) in declaring a Variable, Always remember, that VARCHAR(MAX) can hold only 8000 characters. When we try to print Variables, PRINT Command display only 4000 character max. When we try to execute, Variable (MAX) which is more than 8000 Character, we may not get exact Error to solve. The problems seems to be with SET Statement which accepts only 4000 Characters. In Order to Execute a Variable which is having VARCHAR (MAX) and the length of the strings are more than 8000 Character, Then best way to work is Splitting the Queries with Multiple Variable. Example, If you have Dynamic Query which is 10000 characters, and is used as a string in a variable, Then, we need to Split the Queries. Find a Logical Break Point and Then concatenate remaining variables. DECLARE @Query VARCHAR ( MAX )       DECLARE @Query1 VARCHAR ( MAX )       DECLARE @Query2 VARCHAR ( MAX )   ...

Here is the Query to fetch Last 'N' Year Data or Last 'N' Months data or Last 'N' Day data. Trick is using  DATEADD(YEAR,-2,GETDATE()). This Query will fetch Last 2 year record. Change Year to Months and Number, to get last 'N' Month data and so on. SELECT * FROM TABLE_Name WHERE DATE_COLUMN > DATEADD ( YEAR , - 2 , GETDATE ( ) )

Here is the Query to fetch Last 'N' Year Data or Last 'N' Months data or Last 'N' Day data. Trick is using  DATEADD(YEAR,-2,GETDATE()). This Query will fetch Last 2 year record. Change Year to Months and Number, to get last 'N' Month data and so on. SELECT * FROM TABLE_Name WHERE DATE_COLUMN > DATEADD ( YEAR , - 2 , GETDATE ( ) )

Had a requirement to need first top 10 records in Excel.  This can be achieved by simply, Right click on the Excel Component and go to Show Advanced Editor and in Component Properties set OpenRowSet value from the cell you need data to start to last last cell. Here I have mentioned A2:B14 and give ok and run the package. 

Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. To solve this error, go to Project >> Properties >> select Debugging option and select FALSE in Run64BitRunTime .

Hello all, In this Post, I will explain how to move Multiple Files from One Folder (Say Source) to Other folder (Say Destination). This Post will also helps you to understand How to Declare Variable and Use it. To Declare a variable, We are go to use Contexts option in repository. Lets say we have two .txt files in Path D:/Source/ . My Requirement is to move the files from Source Folder ( D:/Source/ ) to Destination Folder ( D:/Dest/ ). Step 1: Open a New job Step 2: Now right click and Create a New Contexts from Repository. Give some Name and give Next. Step 3: Now Fill in the Source Directory Details where the loop on files should happen as shown in the snippet and give finish. Step 4: Now Context is created and The values will be changing based on each file in Folder. Step 5: Click and Drag the context from Repository to Context Job Window below the Job Designer. Step 6: If we Expand the Contexts, We can find the variable SourcePath is holdi...

Hi all, As I explained in detail how to create a job in Talend and how to configure the components in my last post, I straight away will share the screenshots to load data from CSV file to Database (SQL Server). You can look into my previous post  here . Please follow the following Snippets. Drag and Drop the CSV Component from MetaData to Designer View and Select tFileInputDelimited Drag and Drop the DB Connection Component from MetaData to Designer View Drag and Drop the CSV Component from MetaData to Designer View and Select tDBOutput Give Connection and Run the package

Hello All, Of many ETL tools available in Market, One of the strong tool is Talend. Difference between other ETL tools and tools like Pentaho, Talend, Clover ETL, Adeptia Integration etc, is that they support NO SQL Cross domains, BIG Data, Hadoop etc. Other ETL tools like, SSIS, Informatica are now coming with their higher versions, which consists of Hadoop Integration. Basically We can say, there are two databases types. 1) RDBMS (Example: SQL Server, MySQL, Oracle etc) 2) Non RDBMS (Example: MongoDB, InfiniDB etc) Talend Supports Non RDBMS databases. Here I would like to share my hands on experience on Talend and how to use it and explain basic components of Talend. Approx there are 500 components we can find in Talend. So lets Kick Start from Basics. First lets try to load Data from Microsoft SQL Server to Excel. Steps: Step1: Open Talend Studio. Step 2: Right click on Job Design and Create a new Job by giving some job name. Step 3: Give the name o...