HTML Codes

To get special characters to show on an HTML web page, special codes can be used (ascii code or word) and are interpreted by the web browser.

SSIS - How to strip out double quotes from import file

Problem
When loading data using SQL Server Integration Services (SSIS) I am importing data from a CSV file. Every single one of the columns in the CSV file has double quotes around the data. When using the Data Flow Task to import the data I have double quotes around all of the imported data. How can I import the data and remove the double quotes?

Solution
This is a pretty simple solution, but the fix may not be as apparent as you would think. Let's take a look at our example.

Here is the sample CSV file as it looks in a text editor. You can see that all of the columns have double quotes around the data even where there is no data. The file is comma delimited, so this should give us enough information to import the data column by column.

To create the package we use a Data Flow Task and then use the Flat File Source as our data flow source.

When setting up the Flat File Connection for the data source we enter the information below, basically just selecting our source file.

If we do a quick preview on the dataset we can see that every column has the double quotes even the columns where there is no data. If you open the text file in Excel the double quotes are automatically stripped, so what needs to be done in SSIS to accomplish this.

On this screen you can see the highlighted area and the entry that is made for the "Text qualifier". Here we enter in the double quote mark " and this will allow SSIS to strip the double quotes from all columns.

If we do another preview we can see that the double quotes are now gone and we can move on to the next part of our SSIS package development.

As mentioned above this is a simple fix to solve this problem. If you are faced with this issue, hopefully this gives you a quick answer to get your development moving forward. This same technique can be used to strip any other text qualifier data from your files.

Malware Code Glossary

ActiveX

A type of Browser Plug-in:

ActiveX is a set of technologies from Microsoft that enables interactive content for the World Wide Web. As ActiveX security settings in Internet Explorer can allow web pages to automatically and secretly install ActiveX controls, they can be a significant security threat. ActiveX controls can access files on your hard drive.

Adware
A type of Advertising Display Software that delivers advertising content potentially in a manner or context that may be unexpected and unwanted by users. Many adware applications also perform tracking functions, and therefore may also be categorized as Tracking Technologies.

Some consumers may want to remove Adware if they object to such tracking, do not wish to see the advertising caused by the program, or are frustrated by its effects on system performance.

On the other hand, some users may wish to keep particular adware programs if their presence subsidizes the cost of a desired product or service or if they provide advertising that is useful or desired, such as ads that are competitive or complementary to what the user is looking at or searching for.

Alternate Data Stream
An extension to Microsoft's Windows NT File System (NTFS) that provides compatibility with files created using Apple's Hierarchical File System (HFS). Applications must write special code if they want to access and manipulate data stored in an alternate stream.

Some applications use these streams to evade detection.

Backdoor
Backdoors are remote administration utilities that open infected machines to external control via the Internet or a local network.

Bluetooth-Worm
A type of Worm that uses Bluetooth technology as its spreading vector. Bluetooth is a wireless communications technology.

See also Worm.

Botnet
Botnet is a term formed from two words " Robot and Network. A Bot, sometimes referred to as Zombie, is a computer that has been infected with malware that allows a remote malicious user access to the computer. With that remote access, the malicious user can control and harness the power of all such Bots into a powerful network used for criminal activity.

Botnets have been used for sending spam remotely, installing more malware without consent, and other illicit purposes.

Browser Helper Object (BHO)
A Browser Helper Object (BHO) is a program that runs automatically every time that the Internet Explorer browser is launched. It is meant to extend the functionality of the browser, but it can also track how you use the Internet.

Toolbars are a common form of BHO.

Browser Plug-in
A software component that interacts with a Web browser to provide capabilities or perform functions not otherwise included in the browser. Typical examples are plug-ins to display specific graphic formats, to play multimedia files or to add toolbars which include search or antiphishing services.

Plug-ins can also perform potentially unwanted behaviors such as redirecting search results or monitoring user browsing behavior, connections history, or installing other unwanted software like nuisance or harmful adware. Types of Browser plug-ins include:

ActiveX Controls: Microsoft Internet Explorer.
Browser Helper Object (BHOs): Microsoft Internet Explorer.
Mozilla Firefox Extensions: Mozilla Firefox.

Cookie
A piece of data that a Web site " or a third party that was commissioned or approved by the website " saves on users' computers' hard drives and retrieves when the users revisit that Web site.

Some cookies may use a unique identifier that links to information such as logon or registration data, online "shopping cart" selections, user preferences, Web sites a user has visited, et cetera.

See also Tracking Cookies.

Data Miner
A Data Miner is a program that can collect information on how you browse and use websites. The collected information can include data gathered from forms you fill and submit. Usually data miners work without your knowledge.

Dialer
A Dialer is a program which tries to connect to an expensive pay-per-minute phone number using the computer's modem. Most malicious dialers work without your awareness or permission.
Distributed Denial-of-Service (DDoS) Attack
A means of burdening or effectively shutting down a remote system by bombarding it with traffic from many other computers.

DDoS attacks are often launched using the compromised systems of Internet users, often using botnets. An attacker will exploit a vulnerability in one computer system and make it the DDoS master using Remote Control Software. Later, the intruder will use the master system to identify and manage zombies that can perform the attack.

Email-Worm
A type of Worm that uses E-mail as its spreading vector.

See also Worm.

HackTool
A utility designed to penetrate remote computers.

Hoax
A Hoax is a type of chain letter that contains false information, often spreading a false virus warning. Do note that we generally only focus on virus-related hoaxes. We can not evaluate whether non-computer related folklore stories are urban legends or true stories. Check www.snopes.com for general urban legends.

Hosts File
The Hosts File is a lot like an address book. When you type an address into your web browser, the address is translated into a numeric IP address. If the address can be found from the Hosts file, your computer will use it. If not, your computer will connect to the DNS service of your Internet Service Provider. Some malware can edit your Hosts file to hijack and redirect a web connection to a completely different site on the Internet/World Wide Web.

IM-Worm
A type of Worm that uses Instant Messaging (IM) software as its spreading vector. MSN Messenger, AOL Instant Messenger (AIM), and Yahoo Messenger are examples of IM applications.

See also Worm.

Intended
When malware is typed as Intended, it is software that contains bugs or other problems that prevent it from functioning as the author intended. It would have been malware, and future versions still might be, but the current version is defective.

IRC-Worm
A type of Worm that uses Internet Relay Chat (IRC) as its spreading vector. IRC is a form of real-time Internet chat. It is mainly designed for group communication in discussion forums known as channels.

See also Worm.

Joke
A Joke is a program with annoying or funny functionality, but it is not destructive.

Macro
Malware that is encoded as a macro embedded in a document.

Malware
Malware is a common name for all kinds of unwanted software such as viruses, worms, trojans and jokes.

Monitor
A Monitoring Tool can monitor and record all computer activities, including each keystroke you type on the keyboard.

Monitoring Tool
See Monitor.

Multipartite Virus
A Multipartite Virus is a virus composed of several parts. Every part of a multipartite virus needs to be cleaned away, to give assurance of non-infection.
Net-Worm
A Network Worm is a program that can replicate itself by sending copies in e-mail messages or over a network.

Network Worm
See Net-Worm.

On-Access Scanner
On-Access Scanner: Real-time scanner, a background process that provides a constant guard against viruses.

On-Demand Scanner
An On-Demand Scanner is a virus scanner which is started manually.

P2P-Worm
A type of Worm that uses Peer-to-peer networking software as its spreading vector.

See also Worm.

Pharming
Pharming (pronounced farming) is a scam technique that is similar to phishing. It is an attempt to steal personal information using false web sites. Pharming attacks seed false information on Internet DNS servers that provide location information. The result is that individuals are directed to web sites to seem to be genuine, but are actually false.

Phishing
In a computing context, Phishing is an impersonation of a corporation or other trusted institution. The goal of the impersonation is to extract passwords or other sensitive information from the victim. It is a form of criminal activity that utilizes social engineering techniques. Phishing is typically done using e-mail or an instant messaging program. The attempt of the message is to appear to be from an authentic source so that victim will either directly respond, or will open a URL link to a fake web site run by the criminals.

Phishing (pronounced fishing) is a scam technique using e-mail that links to false, but genuine looking web sites, most often of Banks, that attempt to steal personal information. The spam bait is used with the odds that it will be ignored by most, because it will be out of context, but with the hope that some will be hooked.

Polymorphic Virus
A Polymorphic Virus is a virus which changes itself (mutates) as it passes through host files, making disinfection a serious challenge.
Real-Time Scanner
A Real-Time Scanner is a scanner that operates in the background, allowing a user to continue working at normal speed, with no significant slowing.

Replication Mechanism
Replication Mechanisms are a mandatory part of every virus and worm. If it doesn't have a replication mechanism, it's by definition not a virus or worm.
Riskware
Riskware is software that can pose a security risk that is not malicious by its nature. Such software has usefulness that can assist a knowledgeable user, but can also be bundled and used by malicious software. Examples include: FTP servers, IRC clients, Network Sniffers, overeager DRM software, and Remote Administration. Additionally, this category includes software that cannot be included in other malware categories. Some software is not malicious, but does not provide the functionality claimed. If the functionality of software is suspect, it may be added to the riskware category.

Rogue
A suspect antispyware application that engages in doubious practices such as false positives. Illegal advertisements are sometimes used and trojans are sometimes installed to provide an infection to "clean". Rogue antispyware is often not malicious itself, but it does not provide the functionality claimed.

Also known as Scareware.

Rootkit
Rootkits are a technique that allows malware to hide from computer operating systems and from computer users. Rootkit techniques create stealth programs that run at a "lower" level than the user can see with normal software utilities. Malware attempts to use this method to avoid detection by security software.

Spy
See Trojan-Spy.

Spyware
Spyware is software that performs actions such as creating unsolicited pop-ups, hijacks home/search pages, or redirects browsing results.

The term Spyware has been used in two ways: In its narrow sense, Spyware is a term for Tracking Software deployed without adequate notice, consent, or control for the user. In its broader sense, Spyware is used as a synonym for Spyware (narrow) and Other Potentially Unwanted Technologies.

Stealth Virus
A Stealth Virus is a virus that hides itself by intercepting disk access requests. When an antivirus program tries to read files or boot sectors to find the virus, the stealth virus feeds the antivirus program a clean image of file or boot sector.

Time Bomb
A Time Bomb is a destructive action triggered at some specific date or time.

Toolbar
Toolbars are add-on applications for Web browser software. They provide additional functionality that is often not included within the browser.

Toolbar software can be used for Tracking users' online behavior.

Tracking Cookies
Tracking Cookies track your web browsing habits. They can collect information about pages and advertisements you have seen or any other activity during browsing. Different websites can share tracking cookies, and each website with the same tracking cookie can read the information and write new information into it.

A Tracking Cookie is any cookie used for tracking users' surfing habits. Tracking Cookies are a form of Tracking Technology. They are typically used by advertisers wishing to analyze and manage advertising data, but they may be used to profile and track user activity more closely.

However, tracking cookies are simply a text file, and far more limited in capability than executable software installed on users' computers. While installed software can potentially record any data or activity on a computer, cookies are simply a record of visits or activity with a single Website or its affiliated sites.

See also Cookie.

Trackware
Software that monitors user behavior, or gathers information about the user, sometimes including personally identifiable or other sensitive information.

Trojan
A Trojan or Trojan House is a software application with hidden destructive functionality. It is a program that appears to do one thing but actually does another.

Trojan-Downloader
Downloads and installs new malware or adware on the targeted computer.

Trojan-Dropper
Used to install other malware on a computer without the computer user's knowledge.

Trojan-Proxy
These Trojans function as a proxy server and provide anonymous access to the Internet from infected machines.

Trojan-PSW
This type of Trojan is designed to steal passwords.

Trojan-Spy
A type of Trojan that includes a variety of spy programs and keyloggers.

Virus
A Virus is a computer program that replicates by attaching itself to another object and/or program.

Vulnerability
Vulnerabilities open security holes that can allow other applications to connect to the computer system without your authorization or knowledge.

W32
W32 is a Platform designator for 32-bit versions of Microsoft Windows. 32-bit refers to the chip architecture that the version of Windows is designed for.

Worm
A Worm is a computer program that replicates independently by sending itself to other systems.
Worms are "network viruses", primarily replicating on networks. Usually a worm will execute itself automatically on a remote machine without any extra help from a user. However, there are worms, such as mass-mailer worms, that will not always automatically execute themselves without the help of a user.

Multiple vectors of spreading result in malware being typed as "Worm". A single vector of spreading will result in a more specific type. Examples: "Net-Worm", "IM-Worm", or "Bluetooth-Worm" .

Zombie
See: Botnet

Automating Transaction Log Backups for All Databases

Problem
Maintenance plans are a great thing, but sometimes the end results are not what you expect. The whole idea behind maintenance plans is to simplify repetitive maintenance tasks without you having to write any additional code. For the most part maintenance plans work without a problem, but every once in awhile things do not go as planned. Two of the biggest uses of maintenance plans are issuing full backups and transaction log backups. What other approaches are there to issue transaction log backups for all databases without using a maintenance plan?

Solution
With the use of T-SQL you can generate your transaction log backups and with the use of cursors you can cursor through all of your databases to back them up one by one. With the use of the DATABASEPROPERTYEX function we can also just address databases that are either in the FULL or BULK_LOGGED recovery model since you can not issue transaction log backups against databases in the SIMPLE recovery mode.

Here is the script that will allow you to backup the transaction log for each database within your instance of SQL Server that is either in the FULL or BULK_LOGGED recovery model.

You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBname_YYYDDMM_HHMMSS.TRN".

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.TRN' BACKUP LOG @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

In this script we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Next Steps

• Add this script to your toolbox
• Modify this script and make it a stored procedure to include one or many parameters
• Create a scheduled task to backup your transaction logs on a set schedule
• Take a look at this tip that does FULL backups for all databases.
• Send your improved script to tips@mssqltips.com and we will post it on the site for others to use