This VB.Net code can be used to get the Last modified date of a file from HTTP header. It can be used to check the file date before the actual file is downloaded to out computer. It save some time and network resources. You can pass different header key to get its value.
But, this example is to get the last modified date of a file from Houstontx.gov website.
Dim request As HttpWebRequest = HttpWebRequest.Create _
Dim response As HttpWebResponse
response = request.GetResponse()
Dim HTTPHeader_LastModified_Datetime As String
HTTPHeader_LastModified_Datetime = response.GetResponseHeader(“Last-Modified”)
Hope you will like it.
There are many ways to get the file modified date from the FTP server. But, I like to write a VB.net code to do that task simpler. Script Task is used in SSIS.
Code: To fetch the file last modified datetime
Dim reqFTP As FtpWebRequest = DirectCast(FtpWebRequest.Create(New Uri(“ftp://SERVERName/FileName”)), FtpWebRequest)
reqFTP.Method = WebRequestMethods.Ftp.GetDateTimestamp
reqFTP.Credentials = New NetworkCredential(“USER NAME”, “PASSWORD”)
response = DirectCast(reqFTP.GetResponse(), FtpWebResponse)
Using Credentials is based on the server requirement. In this script, file date time is just displayed using Message box. We can use package variable to store the value.
I created this snippet to save my search time in future. So, you can also make use of this if needed.
The main objective of this code is to read a text file line by line and write it into another file. I know, it looks silly. because, why do we need to read a file and write to others without doing anything. But, The actual purpose is to read a text file and do some operations like update or remove. Using this method I can keep my original file unchanged. does it make sense now?
Dim strLine As String
Dim objStreamReader As StreamReader
Dim objStreamWriter As StreamWriter
objStreamReader = New StreamReader(“C:\test\source.txt”)
objStreamWriter = New StreamWriter(“C:\test\destination.txt”)
strLine = objStreamReader.ReadLine
Do While Not strLine Is Nothing
strLine = objStreamReader.ReadLine
‘Close the file.
I know there are millions of ways to do something. But, I choose this method and if you have a new logic please share it in the comment section.
Microsoft released the hotfixes for the latest version of MS SQL Server – SQL Server 2014 CU 4. The new build number for this cumulative update 4 (CU 4) is 12.0.2430.0
The full list of hotfixes provided in this release are compiled in the excel file. Download SQL Server 2014 CU 4 hot fixes excel file.
|Fix area||No Of Fixes|
|Data Quality Services (DQS)||1|
Microsoft recently send this workaround to fix the issue in SQL Server 2008 R2 SP 3 for Report Builder launch
If you installed SQL Server 2008 R2, have upgraded it to Service Pack 2 and then applied Service Pack 3, then Report Builder will fail to launch.
Client side workaround:
Install and run Report Builder 3.0 of SQL Server 2008 R2 RTM from the following location: http://www.microsoft.com/en-pk/download/details.aspx?id=6116
Install and run Report Builder of SQL Server 2014 from the following location:
Server side workaround:
Uninstall Service Pack 3 then uninstall Service Pack 2 and then reinstall Service Pack 3.
Full detail is in Here
This may be a simple thing, but sometime new developers are difficult to find this information readily. So, thought of adding it in this website.
How to declare and initialize variables in single or multiple lines?
Declaring variables – Multiple lines
DECLARE @int_i AS INT;
DECLARE @chr_Name AS VARCHAR(50);
Declare and Initialize variables – Multiple lines
DECLARE @int_i AS INT = 1;
DECLARE @chr_Name AS VARCHAR(50) = ‘Test';
Declare variables in single line
DECLARE @int_i AS INT, @chr_Name AS VARCHAR(50);
Declare and Initialize variables in single line
DECLARE @int_i AS INT = 1, @chr_Name AS VARCHAR(50) = ‘Test';
That is it, You have learned to declare and initialize variables in MS SQL Server.
This blog post is just to give a step by step instruction to work with Unpivot Transformation in SSIS.
Unpivot Transformation is used to convert unnormalized dataset into normalized dataset. Database Normalization.
For instance, we have got a data as show in the below image and we want to normalize the data to make the user table compact.
In this dataset, Column 1 contains Customer names, column 2 to 6 contains product names and each data cell represents the quantity purchased by the customer. But to store this data in the RDBMS, we need to convert this dataset into normalized structure. So, In normalizes structure this data will looks like this.
Now, we are going to implement this scenario in using SSIS.
CREATE TABLE [dbo].[tblPurchase](
[Customer] [nchar](10) NULL,
[Ham] [nchar](10) NULL,
[Soda] [nchar](10) NULL,
[Milk] [nchar](10) NULL,
[Beer] [nchar](10) NULL,
[Chips] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[tblPurchase]
(‘Kate’,’2′, ‘6’, ‘1’, ’12’,NULL),
Step 1: Create a new package
Step 2: Add a Data Flow Task in to package and Go to Data Flow environment
Step 3: Add a OLE DB source task to configure to read the table.
Step 4: Add Unpivot transformation. This task is used to convert unnormalized dataset to normalized dataset. Unpivot Transformation Editor.
Notice that the customer column is removed from the pivot input column and setup the output column as quantity and the product column as pivot key. So, we will get the output of Product and its quantity for each customer in multiple rows.
Note: All null value quantity rows will be removed by this transformation.
Step 5: Use the OLE destination column to store the unpivoted output in the normalized table. In this example, I am using the union all transformation as the destination and added data viewer to browse the output in the pipeline.
Step 6: Execute the package
This output show that the unpivoted dataset is in normalized structure.
Now, You can easily normalize any unnomalized dataset using Unpivot Transformation task in SSIS. I hope you have enjoyed reading this article. So, please share your thoughts and question in the comment section.