Tuesday, August 15, 2017

Foreach Loop Container in SSIS

The Foreach Loop Container is the one of the most important part of SQL Server Integration Services in the Control Flow window because it runs a repeating control flow in a package. This is the most widely used container to do multiple tasks inside it. In a package, looping is enabled by using a Foreach enumerator. 


So, we have to understand different types of Foreach enumerators –
  1. Foreach ADO enumerator to enumerate rows in tables and specify the ADO object source variable and the enumerator mode. The variable must be of Object data type.For example, you can get the rows in an ADO recordset.
  2. Foreach ADO.NET Schema Rowset enumerator to enumerate the schema information about a data source and specify the connection to a database and the schema to enumerate. For example, you can enumerate and get a list of the tables in the AdventureWorks2014 SQL Server database.
  3. Foreach File enumerator to enumerate files in a folder. The enumerator can traverse subfolders means specify a folder and the files to enumerate, the format of the file name of the retrieved files, and whether to traverse subfolders. For example, you can read all the files that have the *.csv file name extension in the Windows folder and its subfolders.
  4. Foreach From Variable enumerator to enumerate the enumerable object that a specified variable contains means specify the variable that contains the objects to enumerate. The enumerable object can be an array, an ADO.NET DataTable, an Integration Services enumerator, and so on. For example, you can enumerate the values of an array that contains the name of servers.
  5. Foreach Item enumerator to enumerate items that are collections which defines the items in the Foreach Item collection, including columns and column data types. For example, you can enumerate the names of executables and working directories that an Execute Process task uses.
  6. Foreach Nodelist enumerator to enumerate the result set of an XML Path Language (XPath) expression and specify the source of the XML document and configure the XPath operation. For example, this expression enumerates and gets a list of all the authors in the classical period: /authors/author[@period='classical'].
  7. Foreach SMO enumerator to enumerate SQL Server Management Objects (SMO) objects that specify the connection to a database and the SMO objects to enumerate. For example, you can enumerate and get a list of the views in a SQL Server database.
  8. Foreach HDFS File Enumerator to enumerate HDFS files in the specified HDFS location and specify a folder and the files to enumerate, the format of the file name of the retrieved files, and whether to traverse subfolders.
  9. Foreach Azure Blob enumerator to enumerate blobs in a blob container in Azure Storage and specify the Azure blob container that containers blobs to be enumerated.
In the next post, we have to go through each of them with some examples.
References - Microsoft

                Wednesday, July 12, 2017

                How to Archive or Move files in SQL Server

                In SQL Server, we have to archive or moved the raw data files from the source to destination shared directories after finishing the data processing. It's very easy task if you are using any ETLs over there but if you are playing in SQL server then you need to create some T-SQL script to move these files from one location/directory to another location/directories. 
                Additional Configuration settings –
                To enable the functionality of file processing, we have to configure some advance options of SQL Server as given below –
                /*Enable the functionality of file processing*/
                EXEC master.dbo.sp_configure 'show advanced options', 1
                RECONFIGURE

                EXEC master.dbo.sp_configure 'xp_cmdshell', 1
                RECONFIGURE

                In this section, we are going to show you the easiest way to do that. So, we are using three main steps to do this such as -
                Step 1: Determine if Directory Exists or Not  
                In the section, we are going to check if directory or archived location exists or not in the drive. If it does not exist then we have to create that directory or archived location as given below –
                ---- Declare local variables
                DECLARE @Inpath VARCHAR(100)='E:\Source\',
                @fileName VARCHAR(200)='sales.csv',
                @SQLcmd nvarchar(500),
                @Result int=0,
                @folderName varchar(100)='Archived',
                @move varchar(100),
                @Destinationpath varchar(50)='E:\Destination\'

                /*
                Script 1 : How to create Archived folder and move files
                Create the archive folder and move all the files from source to archive folder*/

                --destination & archive folder
                SET @SQLcmd = 'dir '+@Destinationpath + @folderName
                -- set output in result varible
                EXEC @result = master.dbo.xp_cmdshell @SQLcmd, NO_OUTPUT
                ---- if result =0 then exists else does not exist
                IF @result <> 0
                BEGIN
                --If folder does not exist then create the archive folder
                --Make the name of the archive folder
                SET @SQLcmd = 'mkdir '+@Destinationpath + @folderName
                EXEC master.dbo.xp_cmdshell @SQLcmd
                PRINT 'Folder has been created'
                END

                Step 2: Move the specified file to the archive folder
                After checking the existence of the directories, we can easily move any specified file from the source to destination by using Move cmd command with the help of T-SQL as  given below –
                ---- Declare local variables
                DECLARE @Inpath VARCHAR(100)='E:\Source\',
                @fileName VARCHAR(200)='sales.csv',
                @SQLcmd nvarchar(500),
                @Result int=0,
                @folderName varchar(100)='Archived',
                @move varchar(100),
                @Destinationpath varchar(50)='E:\Destination\'

                Script 2 : Move the specified file to the archive folder*/
                SET @Inpath='E:\source\iris.csv';
                SET @Destinationpath=@Destinationpath + @folderName
                ---- Script with Move commond
                SET @SQLcmd = 'Move '+@Inpath+' ' +@Destinationpath + ''
                EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT

                Step 3: Move the all files to the archive folder
                After checking the existence of the directory, if you want to move all files from the source directory to destination directory then you need to put * instead of the file name in the source path as  given below –
                ---- Declare local variables
                DECLARE @Inpath VARCHAR(100)='E:\Source\',
                @fileName VARCHAR(200)='sales.csv',
                @SQLcmd nvarchar(500),
                @Result int=0,
                @folderName varchar(100)='Archived',
                @move varchar(100),
                @Destinationpath varchar(50)='E:\Destination\'

                /*
                Script 3 : Move the all files to the archive folder*/
                SET @Inpath='E:\source\*';
                SET @Destinationpath=@Destinationpath + @folderName
                SET @SQLcmd = 'Move '+@Inpath+' ' +@Destinationpath + ''
                EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT

                Query At a Glance - given below –
                /*Enable the functionality of file processing*/
                EXEC
                master.dbo.sp_configure 'show advanced options', 1
                RECONFIGURE

                EXEC
                master.dbo.sp_configure 'xp_cmdshell', 1
                RECONFIGURE

                ---- Declare local variables
                DECLARE @Inpath VARCHAR(100)='E:\Source\',
                @fileName VARCHAR(200)='sales.csv',
                @SQLcmd nvarchar(500),
                @Result int=0,
                @folderName varchar(100)='Archived',
                @move varchar(100),
                @Destinationpath varchar(50)='E:\Destination\'

                /* Script 1 : How to create Archived folder and move files
                Create the archive folder and move all the files from source to archive folder*/

                --destination & archive folder
                SET @SQLcmd = 'dir '+@Destinationpath + @folderName
                -- set output in result varible
                EXEC @result = master.dbo.xp_cmdshell @SQLcmd, NO_OUTPUT
                ---- if result =0 then exists else does not exist
                IF @result <> 0
                BEGIN
                --If folder does not exist then create the archive folder
                --Make the name of the archive folder
                SET @SQLcmd = 'mkdir '+@Destinationpath + @folderName
                EXEC master.dbo.xp_cmdshell @SQLcmd
                PRINT 'Folder has been created'
                END

                /* Script 2 : Move the specified file to the archive folder*/
                SET @Inpath='E:\source\iris.csv';
                SET @Destinationpath=@Destinationpath + @folderName
                ---- Script with Move commond
                SET @SQLcmd = 'Move '+@Inpath+' ' +@Destinationpath + ''
                EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT

                /* Script 3 : Move the all files to the archive folder*/
                SET @Inpath='E:\source\*';
                SET @Destinationpath=@Destinationpath + @folderName
                SET @SQLcmd = 'Move '+@Inpath+' ' +@Destinationpath + ''
                EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT


                Conclusion
                There are many other ways also to playing around the SQL server to accomplish this task. But by using the above script, we can do this by using T-SQL script without bothering other stuffs. 

                Wednesday, June 21, 2017

                Login to Microsoft SQL Server Error: 18456

                This is the most common error in SQL Server. If you are connected to the network and have also a domain member then this error occurs when you set the SQL Server Authentication as "Windows Authentication mode" and trying to connect with SQL Server credentials. 

                If we are trying to connect using "SQL Server Authentication" then Login to Microsoft SQL Server Error: 18456 is occurred. 

                In this case, we must have to modify our server authentication by using the following steps in the Microsoft SQL Server Management Studio in the object explorer:
                Right click on the SQL Server and click Properties as given below -

                In SQL Server Properties window, Go to the Security page and under Server authentication section choose the SQL Server and Windows Authentication mode radio button and Click OK button to close window as given below - 

                To immediate effect, just go the window services section and Restart the SQL Services as given below -

                To avoid this kind of issues, we suggest to install SQL Server within mixed mode authentication  which always allow to enable both authentication modes (SQL Server and Windows Authentication mode ). 

                Monday, June 19, 2017

                How to list files inside a folder with SQL Server

                In this section, we are going to list all the files within the folder or directory by using T-SQL. This is very common scenario if you have SSIS environment to load the multiple files from the shared location. In absence of  SSIS or other ETL tools,  you have to write T-SQL codes to list down all the files in the file directory or folder before load their data into the system one by one. 

                Database-level configuration options - Before using this code, we should change the global configuration settings for the current server as given below -
                -- To allow advanced options to be changed.
                EXEC sp_configure 'show advanced options', 1
                GO
                -- To update the currently configured value for advanced options.
                RECONFIGURE
                GO
                -- To enable the feature.
                EXEC sp_configure 'xp_cmdshell', 1
                GO
                -- To update the currently configured value for this feature.
                RECONFIGURE
                GO











                T-SQL Code through xp_DirTree command - After doing the needful database level configuration setting, we are able to run the below code to list all the files from the shared folder as given below –
                ----- Table Variable to Store the file List
                declare @TotalFiles Table(SubDirectory Varchar(200), Depth Int, Files Int, ictr int primary key identity(1,1))
                ----- Folder or Directory Path Variable
                declare @inpFilePath varchar(200)='C:\Sample Data Pack\';

                ---- Insert Values into Table variable
                INSERT INTO  @TotalFiles (SubDirectory, Depth , Files )
                EXEC Master.dbo.xp_DirTree @inpFilePath,1,1







                T-SQL Code through XP_cmdshell command - XP_cmdshell is the other option to list all the files from the shared folder but it requires more data filters as given below –
                ----- Table Variable to Store the file List
                declare @TotalFiles Table(SubDirectory Varchar(200), Depth Int, Files Int, ictr int primary key identity(1,1))
                ----- Folder or Directory Path Variable
                declare @inpFilePath varchar(200)='E:\Sample Data Pack\';

                ---- Set Dirctory command before folder path
                SET @inpFilePath= 'DIR '+ ' "' + @inpFilePath +'"'
                ---- Insert Values into Table variable
                INSERT INTO  @TotalFiles (SubDirectory)
                EXEC Master.dbo.XP_cmdshell @inpFilePath

                SELECT FilesName=Substring(SubDirectory,40,100)
                FROM @TotalFiles
                ----- Remove all directories
                where SubDirectory NOT Like '%<DIR>%'
                ----- remove bytes
                AND SubDirectory NOT Like '%bytes%'
                ---- file name will start from 40
                AND LEN(SubDirectory)>40
                ---- file name must have . extenion
                AND SubDirectory Like '%.%'


                Popular Posts