Category Archives: SQL Server

DTS Package – Check for file before importing into database

‘ Rename File from Connection
Option Explicit

Function Main()

Dim oFSO
Dim sFileName

sFilename = DTSGlobalVariables.Parent.Connections(“Text File (Source)”).DataSource

Set oFSO = CreateObject(“Scripting.FileSystemObject”)

‘ Check for file and return appropriate result

If oFSO.FileExists(sFilename) Then

‘Main = DTSTaskExecResult_Success
Main = DTSStepScriptResult_ExecuteTask

Else

‘Main = DTSTaskExecResult_Failure
Main = DTSStepScriptResult_DontExecuteTask

End If

Set oFSO = Nothing

End Function

DTS Package – Move Imported Data File to an Archive Directory

‘ Rename File from Connection
Option Explicit

Function Main()

Dim oPkg
Dim oConn
Dim oFSO
Dim oFile
Dim sFilename
Dim sFileDestination
Dim sMonth
Dim sDay
Dim sYear
Dim sHour
Dim sMinute
Dim sSecond

‘ Get reference to the current Package object
Set oPkg = DTSGlobalVariables.Parent

‘ Get reference to the named connection
Set oConn = oPkg.Connections(“Text File (Source)”)

‘ Get the filename from the connection
sFilename = oConn.DataSource

sMonth = CSTR( MONTH(Now()))
sDay = CSTR(DAY(Now()))
sYear = CSTR(YEAR(Now()))
sHour = CSTR(HOUR(Now()))
sMinute = CSTR(MINUTE(Now()))
sSecond = CSTR(SECOND(Now()))

sFileDestination = DTSGlobalVariables(“dts_ArchiveLocation”).Value & DTSGlobalVariables(“dts_FileName”)

‘MsgBox sFileDestination & sMonth & sDay & sYear & “.TXT”

‘ Rename the file
Set oFSO = CreateObject(“Scripting.FileSystemObject”)
‘oFSO.MoveFile sFilename, sFilename & “.bak”
oFSO.MoveFile sFilename, sFileDestination & sYear & sMonth & sDay & “_” & sHour & sMinute & sSecond & “.TXT”

‘ Clean Up
Set oConn = Nothing
Set oPkg = Nothing
Set oFSO = Nothing

Main = DTSTaskExecResult_Success
End Function

SQL Server Security: Database “db_datareader” Roles

The db_datareader role

The db_datareader role has the ability to run a SELECT statement against any table or view in the database. This role is often used in reporting databases where users would be coming in via a third-party reporting tool and building their own ad-hoc queries. If you need to restrict a user to only be able to read from certain tables, the db_datareader is not the right choice as it would have to be combined with the explicit use of DENY permissions on tables the user shouldn’t be able to access. A better practice would be to create a user-defined database role with the proper permissions.

One key point about the db_datareader role is that it always has the right to read all tables and views. That means if you create a new table in the database, a member of the db_datareader role has access immediately. This differs greatly from a user-defined role with which you must explicitly grant each permission. Therefore, unless you give a user-defined role permission to access a database object such as a table or view, that role can’t do so. Not only does it have access to user tables but also system tables. That means a member of the db_datareader role can execute a SELECT query against a system table even you decide to revoke public access to SELECT against these tables (keep in mind that revoking default permissions would result in an unsupported configuration so far as Microsoft is concerned).

Convert Julian Dates in SQL SERVER

  • SQL SERVER JULIAN TO DATE (WHEN DOWNLOADING JULIAN DATE)
  • date field has to be nvarchar or varchar or character.
  • declare @DateP char(10)

    select @datep=107277 — ALSO OK FOR 99 YEARS

    select
    dateadd(dd,@datep-(@datep/1000)*1000-1, convert(datetime,’01/01/’+ right(convert(varchar,@datep/1000),2))) as Date_ENT

    SQL: YYYYMMDD

    SELECT CONVERT(VARCHAR(8), GETDATE()-365, 112)

    This is a way to query and get the date in the following format:

    YYYYMMDD

    I came across some dates saved in the database in this format as a varchar and had to use this to initially get the date and then convert it from and integer to varchar.