Creating CSV Files Using BCP and Stored Procedures

Discovery2012 SQL Server, SSIS

This article focuses on the use of the Bulk Copy Program (BCP) to create CSV files. Although it is possible to create a CSV file using DTS or SSIS, using BCP is often simpler and more efficient.

I use master..sysobjects as an example table to extract.

Create a simple CSV file

The simplest way to copy data from a database table to file is to use the basic BCP command:

The basic format for the BCP command for creating a CSV file is as follows:

BCP <table> out <filename> <switches>

The switches used here are:

  • -c Output in ASCII with the default field terminator (tab) and row terminator (crlf)
  • -t override the field terminator with “,”
  • -T use a trusted connection. Note that U -P may be used for username/password
  • -S connect to this server to execute the command

Note that, like DTS/SSIS, BCP is a client utility, hence you need to supply the connection information.

For transfer of data between SQL servers, in place of -c, use -n or -N for native data format (-N = Unicode). This is much faster and avoids data conversion problems. Please refer to the previous BOL link for the complete format of the BCP command.

As BCP is a command line utility it is executed from T-SQL using xp_cmdshell. Create a directory called BCP on your c: drive and execute:

Other field and row delimiters

Often, character data includes commas which will be interpreted in the file as a field terminator. A simple way to cater for this is to use a different field delimiter which does not appear in the data. Commonly used characters are pipe (|):

And caret (^):

The terminators are not limited to a single character, so if necessary you can use (|^):

Note that this will increase the size of the file and so slow down the import/export. Another way to cater for embedded commas is to “quote encapsulate” character data – more about that later.

It is unusual to need to change the row terminator from the default of crlf (carriage return, line feed) but occasionally you will need the cr or lf on its own.

To do this use the hex value cr = 13 = 0x0D, lf = 10 = 0x0A. If you can’t remember these values they are easily obtained, as follows:

To use these in the BCP command for the row terminator the -r switch is used:

When the resulting file is opened in notepad the row terminators will not end the line – the character should appear as a non-ASCII character (a little oblong). If opened or copied to query analyser (or management studio) these will be interpreted as line breaks and the file will be more readable.

The terminator characters can be encapsulated with double quotes in the command to allow for space. I like to do this for anything other than a single character delimiter. The following example gives a crlf row terminator and | ^ field terminator.

Formatting the extracted data

If you do not require all the columns/rows, or you wish to alter the data, a simple method is to create a view. This can be used in the BCP command in the same way as a table. The following view allows you to extract just the name, the formatted create date and time and then order the results according the date created (most recent first):

Now we can quote encapsulate the extracted data by including the formatting in the view:

Note:
Quote encapsulation can also be performed by use of a format file.

Complete control – stored procedures

If you examined the BCP syntax in BOL, you might have noticed that it is possible to extract from a query by using the queryout keyword in place of out.

So, for example, an equivalent but neater version of the previous code extract would place the ORDERBY clause in the BCP statement rather than the view:

Of course, strictly speaking, the view was not necessary at all as the query could have been included in the BCP statement but that can get difficult to maintain.

The queryout method allows you to BCP from the result of a stored procedure, which opens up a lot of possibilities and offers a lot of control over the file format. For anything other than a simple table extract I would tend to use this method rather than a view. I would also format each line within the stored procedure. This means that the formatting can be tested independently from the file creation.

Employing this technique, the above extract becomes:

More complex formatting

We can now change the format of the extracted data to anything we require. For example, to include column names at the top and a rowcount at the end:

This stored procedure will format the data as required, but when it is run via the BCP command it receives an error:

“Invalid object name ‘#a’”

Using the profiler you can see that when using queryout, the query is called twice. BCP tries to get the result set format by calling the stored procedure with fmtonly. This does not execute the stored procedure but returns the format of the resultset. Unfortunately, it does not work if the stored procedure creates and uses a temp table. This problem can be circumvented by including a set fmtonly offcommand in the execution:

Note that the column terminator is not used since the resultset is a single column.

—Editor’s Note—
This was the only piece of code that I failed to get working. The stored procedure executed fine outside the BCP, but when I tried to run the above command I received an error:

SQLState = HY010, NativeError = 0
Error = [Microsoft][SQL Native Client]Function sequence error
NULL

The author was unable to replicate this error. Is anyone aware of what might be causing this on my machine?
—End Editor’s Note—

Look at this execution using the profiler and you will see set fmtonly off followed by set fmtonly on. There is a potential problem with this though: it means that the stored procedure is executed twice, once for the format and once to extract the data – due to the fmtonly setting, both these calls will produce a resultset. This needs to be taken into account when considering how long the process will take and its impact on your system.

As the stored procedure is executed twice it should not contain any updates of permanent data. Particularly if data is flagged as exported then the flagging will be performed on the first call and no data will be extracted for the second call. All in all, this method should be used with caution and comments (warnings) placed in the stored procedure for the aid of future maintenance.

To get round all of these problems, use the stored procedure to insert into a table then, from the BCP, extract from that table. This gives you added features that can be valuable on systems that are performing a lot of extracts:

  • It gives a record of the data extracted – if the file is lost it can be recreated.
  • The data extracted can be presented to a user for viewing from the database.
  • If there are problems with the file, the support can see the data without needing access to the file.

For this process, the format stored procedure, s_bcpMasterSysobjects, will insert into the table using an identifier and the BCP will extract using the same identifier. This process can be controlled be (a control) stored procedure which would allocate the identifier and pass it to the format stored procedure which inserted the extracted data into a table with that identifier. It then calls another stored procedure or in-line code to create files (via BCP) for each identifier which is not marked as complete. The identifier is flagged as complete after the file creation. In this way the file creation becomes independent of the extract of the data.

This identifier can be passed to both processes from a controlling stored procedure or the format stored procedure can get the next available identifier and the extract can flag the identifier as extracted – so it extracts everything that has not been extracted, allowing the export to be independent of the extract.

Which technique you use will depend on your system. The following code demonstrates the use of a controlling stored procedure:

Now the data is extracted via:

You can view the data extracted via:

Alternatively, you can use the following, which will also give the date that the data was extracted:

Note:
For the extract I have given three sort columns Seq1, Seq2, Seq3 which are all order ascending. This means that the extracting stored procedure needs to place the values in ascending order (see the date manipulation). This could have ascending and descending sort columns or the use could be dependent on the extract type.

For a system which is performing a lot of small exports I will usually implement this process, and a similar process for imports.

Extracting all tables from a database

This is usually performed to transfer data between servers/databases and so should use the native data format. The easiest way is to create the BCP statements from a query then execute them:

The result of this will be a series of BCP statements of the form:

which will extract all tables in the database. To import just change the “out” to “in”.

Summary

We have seen how to use BCP to perform a simple extract of a table to a CSV file, how to perform slightly more complex extracts using a view, extracting using a stored procedure to format the data and a system which allows the data to be extracted and saved in a table and viewed/exported from there.

SQL: How to find out who truncated your table in SQL Server

Discovery2012 SQL Sys Query

Someone truncated your table. You would like to get your data back, but mostly you really just want to know who did it.

Getting the data back can be as easy as restoring a backup. But having a backup does not tell you who the culprit is.

This information can however be gleaned from the SQL Server Transaction Log. If your database is in simple recovery model you’d better be quick about it, so that you can get to the information before the log gets reused.

The following query will show you who the culprit is based on data in the active transaction log:

SELECT [Current LSN],[Operation],[Context], [Transaction ID],[Transaction Name],SUSER_SNAME ([Transaction SID])[Culprit]

FROM fn_dblog (NULL,NULL)

WHERE [Transaction Name]=’TRUNCATE TABLE’

Unfortunately it only shows you that the user truncated atable but not exactly which table. So how can we determine which table this particular action relates to?

We know that SQL Server can create multiple entries in the transaction log for even the simplest operation. So we can look at all the entries with the same Transaction ID to determine exactly what happened.

SELECT[Current LSN],[Operation],[Context],[Transaction ID],[Transaction Name],SUSER_SNAME ([Transaction SID])[Culprit],[Lock Information]

FROM fn_dblog (NULL,NULL)

WHERE[Transaction ID]= (Select[Transaction ID]FROMfn_dblog(null,null)WHERE[Transaction Name]=’TRUNCATE TABLE’)

GO

Here I am including the Lock Information column. Since we know that in order to truncate a table a Sch-M (Schema Modification) lock will be taken, we can use that to determine which object it was taken on.

In this case the object id is 885578193. We can then use the following to obtain the name of the table which was truncated in that transaction.

SELECT OBJECT_NAME(885578193)

If the data is no longer available in the online transaction log, and you are using the FULL recovery model and have log backups, you can use the same method with the fn_dump_dblog function instead. This will allow you to not only read the online transaction log but also log backups.

******The Correct Query : WHERE[Transaction ID] IN (Select[Transaction ID]FROM fn_dblog (null,null)WHERE[Transaction Name]=’TRUNCATE TABLE’)

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

Discovery2012 Unknown
Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions
Problem

You work in a shop that puts business or application logic in the SQL Server using stored procedures, views and functions to return values to the calling applications or perform tasks. This is not unusual in companies that use the SQL Server layer to perform business tasks, such as finance operations, or incorporate application functionality into the programmability layer. You wish to preserve secrecy on some procedures, views or functions in order to maintain security.

Solution

SQL Server stored procedures, views and functions are able to use the WITH ENCRYPTION option to disguise the contents of a particular procedure or function from discovery. The contents are not able to be scripted using conventional means in SQL Server Management Studio; nor do the definitions appear in the definition column of sys.sql_modules. This allows the cautious DBA to keep stored procedures and functions securely in source control and protecting the intellectual property contained therein. This tip will focus on encrypting and decrypting a user-defined function.

Encrypting a UDF

To encrypt a user-defined function, simply add WITH ENCRYPTION to the CREATE FUNCTION statement (after the RETURNS element). Throughout this tip, I will be building an encrypted UDF (and decrypting it) to demonstrate the principle.

First, create the UDF. Here’s mine – it’s a simple module that accepts an input string and returns the encrypted varbinary hash value. There are two vital pieces of information here that MUST NOT be given away to the user of the function – the encryption standard, and the salt.

CREATE FUNCTION dbo.getHash ( @inputString VARCHAR(20) )
RETURNS VARBINARY(8000)
AS BEGIN
    DECLARE @salt VARCHAR(32)    
 DECLARE @outputHash VARBINARY(8000)
 SET @salt = '9CE08BE9AB824EEF8ABDF4EBCC8ADB19'
 SET @outputHash = HASHBYTES('SHA2_256', (@inputString + @salt))
RETURN @outputHash
END
GO

In this example the salt is fixed and an attacker, given the encryption standard (SHA-256) and the salt, could be able to decrypt the hash into plaintext. We can view the definition of a function by finding it in SQL Server Management Studio, right-clicking and scripting out the function:

 

The attacker can also use sp_helptext, or query sys.sql_modules if he/she knows the function name:

SELECT definition
FROM sys.sql_modules 
WHERE definition LIKE ('%getHash%')

There is some protection built in; by using role-based security or sensibly allowing the least required privileges to users, the attack surface can be lessened as the VIEW DEFINITION permission is required (or ownership of the function) in SQL Server 2005 upwards. Note in earlier versions, this permission is not required. The user may also have the permission granted implicitly by holding other permissions on the object. (See ‘Metadata Visibility Configuration’ at http://msdn.microsoft.com/en-GB/library/ms187113.aspx for more detail).

We can amend the function definition like so:

ALTER FUNCTION dbo.getHash ( @inputString VARCHAR(20) )
RETURNS VARBINARY(8000) WITH ENCRYPTION 
-- rest of function here

Note that WITH ENCRYPTION occurs after RETURNS, not before. With stored procedures, the WITH ENCRYPTION option occurs immediately after the CREATE PROCEDURE x ( @somevar) statement.

With our encrypted function we can attempt to script it out in SQL Server Management Studio again, or look at sys.sql_modules. Here’s what we get:

 

Querying sys.sql_modules definition column for this function returns NULL. Executing sp_helptext returns the error:

The text for object 'dbo.getHash' is encrypted.

Note the UDF is exactly as effective as it was before – we can still call it and return a value:

 

I’m using an undocumented stored procedure here called fn_varbintohex to convert the VARBINARY output from my function into a hexadecimal format, for portability between applications and clarity – it’s not directly relevant to this example. Normally the VARBINARY output of HASHBYTES is passed directly to the calling application.

 

Decrypting a Function

Firstly, open a Dedicated Administrator Connection (DAC) to SQL Server. Using SQL Server Management Studio, this is easily done by prefixing admin: to the connection string upon connection of a query window. Note that the DAC can only be used if you are logged onto the server and using a client on that server, and if you hold the sysadmin role. You can also get to it by using SQLCMD with the -A option. Note: DAC won’t work unless you’re using TCP/IP; you’ll get this rather cryptic error (in both SQLCMD and SSMS):

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Client unable to establish 
connection because an error was encountered during handshakes before login.  Common 
causes include client attempting to connect to an unsupported version of SQL Server, 
server too busy to accept new connections or a resource limitation (memory or 
maximum allowed connections) on the server.

If you can’t access the server directly for whatever reason, you can enable remote administrative connections (a remote DAC) as follows. You’ll still need to use TCP/IP:

exec sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
exec sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE

The procedure for decryption comes in three steps. First, get the encrypted value of the procedure definition from sys.sysobjvalues (via the DAC connection). Secondly, get the encrypted value of a ‘blank’ procedure, where the definition is filled in by ‘-‘. Thirdly, get the plaintext blank procedure statement(unencrypted). Now XOR them together (XOR is the simplest of decryption procedures and forms the basis of many algorithms including MD5) as shown below to retrieve the output of the procedure. You’ll find my take on this algorithm in the code example below:

-- Connect using the DAC then execute the below

SET NOCOUNT ON
GO

ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS
BEGIN
 PRINT 'This text is going to be decrypted'
END 
GO

DECLARE @encrypted NVARCHAR(MAX)
SET @encrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'TestDecryption' )
DECLARE @encryptedLength INT
SET @encryptedLength = DATALENGTH(@encrypted) / 2

DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N'ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))
EXEC sp_executesql @procedureHeader
DECLARE @blankEncrypted NVARCHAR(MAX)
SET @blankEncrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'TestDecryption' )

SET @procedureHeader = N'CREATE PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))

DECLARE @cnt SMALLINT
DECLARE @decryptedChar NCHAR(1)
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptedMessage = ''
SET @cnt = 1
WHILE @cnt <> @encryptedLength
BEGIN
  SET @decryptedChar = 
      NCHAR(
        UNICODE(SUBSTRING(
           @encrypted, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @procedureHeader, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @blankEncrypted, @cnt, 1))
     )
  SET @decryptedMessage = @decryptedMessage + @decryptedChar
 SET @cnt = @cnt + 1
END
SELECT @decryptedMessage

If you still cannot access via DAC or prefer a code-based approach, then you can use one of a number of freeware third-party .NET-based decryption software packages to do this for you. The blog Sqljunkieshare also purports to have a method of doing this (code untested) that looks viable – you can find the link here:

http://sqljunkieshare.com/2012/03/07/decrypting-encrypted-stored-procedures-views-functions-in-sql-server-20052008-r2/ (use at your own risk)

How to: Create an external reference in different workbooks in Excel

Discovery2012 Excel

How to: Create an external reference in different workbooks in Excel

Answer ID 2526   |    Published 11/13/2007 03:09 PM   |    Updated 11/13/2007 03:09 PM

How to: Create an external reference in different workbooks in Excel 2007.

Solution
Open the destination and source workbook. To save the destination workbook, click the ‘Microsoft Office’ button and click ‘Save’. Select the cells to create the link. Enter ‘=’. Open source workbook. Select the cells that you want to link to. Press ENTER.

Details
An external reference is a reference to a cell or range on a worksheet in another Excel workbook. You can refer to the contents of cells in another workbook by creating an external reference. You can use the external references while working with large amounts of data or complex formulas.

To create an external reference between cells in different workbooks:

1) Open Microsoft Excel workbook.
2) Open the workbook that contains the link and the workbook that contains the data that you want to link to.
3) To save the destination workbook, click the ‘Microsoft Office’ button on the top left and click ‘Save’.
4) Select the cells in which you want to create the link.
5) Enter the ‘=’ sign.
NOTE: If you want to perform calculations or functions on the link value, type the operator or function that you want to precede the link.
6) Open the source workbook and click the appropriate worksheet.
7) Select the cells that you want to link to.
8) Press ENTER. (This creates a reference between cells of different workbook. You may close the source workbook after creating a link.)
Knowledge-Paks Online and Knowledge-Paks On Site
Copyright (c) 2007 by RightAnswers, Inc. Subject to the terms set forth in the end-user license agreement. All rights reserved.

C# – SSIS Dynamic package loading

Discovery2012 C#, SSIS Script Component

C# – SSIS Dynamic package loading. Load a SSIS package using .net c#

 

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace ST_43cbf51a82014806a9fa9aeec94bdb08
{

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

class MyEventListener : DefaultEvents
{
public override bool OnError(DtsObject source, int errorCode, string subComponent,
string description, string helpFile, int helpContext, string idofInterfaceWithError)
{
// Add application-specific diagnostics here.
Console.WriteLine(“Error test in {0}/{1} : {2}”, source, subComponent, description);
return false;
}
}
public void Main()
{
string pkgLocation;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;

MyEventListener eventListener = new MyEventListener();

pkgLocation = Dts.Variables[“User::package_directory”].Value.ToString() + Dts.Variables[“User::package_name”].Value.ToString();

app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);

foreach (ConnectionManager conn in pkg.Connections)
{
Dts.Variables[“User::package_connection_string”].Value = conn.ConnectionString.ToString();
}
}
}
}

Excel Lookup against the sql table

Discovery2012 Unknown

My working excel vba function:

Function SQLookup(LkUpValue As String, LkUpFld As String, TblName As String, FldName As String, ConnStr As String)
On Error GoTo Error:
SQLookup = “Error”
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection

Dim connString As String
connString = “driver={SQL Server};” & _
“server=server_name;Trusted Connect=Yes;database=database_name”
Conn.ConnectionString = connString
Conn.Open

‘ If Conn.State = adStateOpen Then
‘ MsgBox “Welcome to Pubs!”
‘ Else
‘ MsgBox “Sorry. No Pubs today.”
‘ End If

Set RecSet = New Recordset

RecSet.Open “SELECT ” & FldName & ” FROM ” & TblName & ” where ” & LkUpFld & ” = ‘” & LkUpValue & “‘”, connString

SQLookup = RecSet.Fields(FldName)

RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing

Exit Function
Error: SQLookup = Err.Number & “|” & Err.Source & “|” & Err.Description
End Function

My working calling the excel vba function:

=SQLookup(B2 (ExcelLookupFieldName), “LookupSqlColumnName”, “dbo.TableName”, “ReturnSqlColumnName”,$E$3)

Note: $E$3 is the connection string is not being used from the cell E3, it is embeded in the funciton.

Lookup against External Database

At work I had a requirement to find fields in an SQL database corresponding to data in Excel cells.  I looked for an Excel function to do this on the internet, but could not find one. I found this surprising as this must surely be a very common requirement.

While it is possible to access an SQL database data in Excel using the built in options for getting External Data, it is an overkill for a simple lookup to access a few pieces of data.

External data in Excel

To get around this, I wrote a function to perform LOOKUP against an external SQL database. It works in a similar manner to VLOOKUP, the parameters to be passed to it are the lookup value, the field name holding the lookup value, the table name containing the data and field to be looked up.

The connection string to access the database is also passed into the function. By changing the connection string, this function can be customised to lookup data against other databases (such as Microsoft Access).

For this function to work, a reference to “Microsoft ActiveX Data Objects x.x Library ” must be made in VBA (under Tools Menu/References).

Macro References

The code for the function is as follows:

Function SQLookup(LkUpValue As String, LkUpFld As String, TblName As String, FldName As String, ConnStr As String)

Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection

SQLookup = “Error”

On Error GoTo Exit_Func

SQLookup = “No Conn”
Conn.Open ConnStr

Set RecSet = New Recordset

SQLookup = “Error”

RecSet.Open “SELECT ” & FldName & ” FROM ” & TblName & ” where ” & LkUpFld & ” like ‘” & LkUpValue & “‘”, ConnStr, , , adCmdText

SQLookup = RecSet.Fields(FldName)

RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing

Exit_Func:

End Function

As an illustration of this function, let us assume that we have an SQL database named FRUITS containing a table tblFRUIT with the following entries:

Fruit Table

An example of the connection string to this database would be as follows:

“DRIVER=SQL Server;SERVER=SQLSvr;UID=ExcelUser;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=D-SQL01;DATABASE=Fruits”

A good reference for conneciton strings is this site.

To lookup for the Quantity of Grapes from this database, the function would be:
=SQLOOKUP(“Grapes”,”Fruit”,”tblFRUIT”,”Quantity”,A1). The cell A1 contains the connection string.

This function looks for the field “Grapes” under the field “Fruit” from the database table and returns the corresponding value under the field “Quantity”, which is 31.

Like all Excel functions, the various parameters can come from other cells, see example below.

SQLookup Example

Note that this function only returns the first lookup value returned by the query in the code (same as VLOOKUP or HLOOKUP).

A word of caution though: This function is most suited to lookup for a few pieces of data from Excel and is not advisable to use this extensively on a sheet to perform many lookups. For querying large amounts of data from an External database, use Microsoft Query in Excel or the tools provided to access data from external databases.

SSIS: Package using SQL Authentication and DontSaveSensitive as ProtectionLevel

Discovery2012 SSIS

In this post i am trying to look into steps to follow while running SSIS package using SQL Authentication and DontSaveSensitive as ProtectionLevel.

I created a simple SSIS package using a connection manager going to SQL Server using SQL authentication, I have my ProtectionLevel as EncryptSensitiveWithUserKey

clip_image001

clip_image003

My package has an OLEDB Source (using connection manager above) which queries a table in database and dumps few rows in flat file.

clip_image005

clip_image006

With above settings ,when I execute my package, Its works like a charm.

clip_image007

Now, I changed the ProtectionLevel  to DontSaveSensitive

clip_image008

And package failed

clip_image009

With following error

SSIS package “Package.dtsx” starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at Package, Connection manager “runeet2k8.sa”: An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available.  Source: “Microsoft SQL Native Client”  Hresult: 0x80040E4D  Description: “Login failed for user ‘sa’.”.

Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: The AcquireConnection method call to the connection manager “runeet2k8.sa” failed with error code 0xC0202009.

Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component “OLE DB Source” (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package “Package.dtsx” finished: Failure.

What we see above is expected as we have sensitive information in our package (password for SQL account) and have chosen DontSaveSensitive as protection level. The affect is Password being cleared from our connection manager hence login failure.

For this situation, We would have to save this password somewhere outside the package. I chose to save it in configuration file as shown below. I used XML Configuration file

clip_image011

clip_image012

I had to chose correct connection manager, Its called runeet2k8.sa in my case

clip_image013

This is the XML configuration file that BIDS wrote for me

<?xml version=”1.0″?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy=”Domain\UserName” GeneratedFromPackageName=”Package” GeneratedFromPackageID=”{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}” GeneratedDate=”22-12-2009 16:12:59″/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType=”Property” Path=”\Package.Connections[runeet2k8.sa].Properties[Password]” ValueType=”String”>

                  <ConfiguredValue></ConfiguredValue>

      </Configuration>

</DTSConfiguration>

 

I have to go in and type password value.

 

<?xml version=”1.0″?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy=”FAREAST\runeetv” GeneratedFromPackageName=”Package” GeneratedFromPackageID=”{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}” GeneratedDate=”22-12-2009 16:12:59″/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType=”Property” Path=”\Package.Connections[runeet2k8.sa].Properties[Password]” ValueType=”String”>

  <ConfiguredValue>Password</ConfiguredValue>

</Configuration>

</DTSConfiguration>

Now i have a package with ProtectionLevel as DontSaveSensitive and password value for connection manager saved in a configuration file outside the package. Package executed absolutely fine after this.

clip_image014

I went ahead and set this package as Integration Services Job under SQL Agent.

clip_image015

Executed the job and it ran successfully.

clip_image016

One important thing to note is that if you are moving your package from your development machine to say test server for execution. You would have to move your configuration file as well and put it at same path on target machine where its placed on your development box.

For example, on my box, the config file PackageConfig.dtsconfig  is placed at D:\Cases\Integration Services Project1\PackageConfig.dtsconfig and if I am moving my package from my development machine to somewhere else, I would have to move my config file as well and place it at location D:\Cases\Integration Services Project1\ on that machine.

If you don’t have dtsconfig at expected location, Job would fail.

If you done want to create same complex folder path for dtsconfig on target machine as its on your dev box (eg: D:\Cases\Integration Services Project1\) You could place this dtsconfig file anywhere you want and give the pointer of this path from Configuration tab  under you SSIS job step.

clip_image001[4]

SQL Query: How to retrieve the Table columns from a stored procedure

Discovery2012 SQL Sys Query

SELECT
name AS ColumnName
,system_type_name AS DataType
,CASE
WHEN is_nullable=0 THEN ‘NOT NULL’
WHEN is_nullable=1 THEN ‘NULL’
END AS IS_NULLABLE
,column_ordinal AS OrdinalPosition
,(‘Col_’ + CAST(column_ordinal AS varchar(4))) AS Alias
FROM sys.dm_exec_describe_first_result_set
(N'[dbo.Registration]’, null, 0) ;

 

SQL SERVER – How to retrieve the metadata of a stored procedure

Original author: Muhammad Imran

In my earlier articles, I wrote about sys.dm_exec_describe_first_result_set
(A new dynamic management view shipped with SQL Server 2012), it is very handy when you need to know the metadata of the first possible result set of any Transact SQL. Today, I came across an issue when I tried to get the metadata of a stored procedure in earlier version of SQL Server and wanted to use the list of columns for further manipulations. However, I could not find a proper solution like sys.dm_exec_describe_first_result_set in the earlier version of SQL Server. So I started developing the solution.

Given below are the solution.

SOLUTION 1 : For SQL Server 2012 and above using sys.dm_exec_describe_first_result_set

1
2
3
4
5
USE AdventureWorks2012;
GO
SELECT *  FROM sys.dm_exec_describe_first_result_set
(N'[dbo].[uspGetManagerEmployees] 16', null, 0) ;
 --OUTPUT

metadata of stored procedure.1.1

SOLUTION 2 : For SQL Server 2005 and above using OPENROWSET

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE AdventureWorks2012
GO
--DROP TABLE #temp1
GO
SELECT * INTO  #temp1
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=sa;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16');
GO
SELECT * FROM tempdb.sys.columns
WHERE object_id=object_id('tempdb..#temp1')
GO
--View the column name / metadata of the
--above created temporary table.
--OUTPUT

metadata of stored procedure.1.2

Regex: query examples and website links

Discovery2012 String Functions

source link:

  1. debuggex.com
  2. regex101.com

OS_PostR_RECT_620d0f5d10-b28e_COMPLETE_NP_FFP.CSV

OS_Rec_A_RECT_620d0f5d10-b28e_COMPLETE_NP_FFP.CSV

OS_PostRec_June 2017_EMMain_07L_1708201_7163015_T2O_f29c00f7f2_FFP.ctrl
OS_PostRec_June 2017_EMMain_07L_1708201_7163015_T2O_f29c00f7f2_FFP.txt
OS_PostRec_June 2017_EMMain_07L_170820163023_0ab98771e1_T2O_20170904140654_20170904_141835_FFP.txt

OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt
OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt

OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018
OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

 

2017_M04_Postrec_July_2017_Basildon & Brentwood_99E_allprov_20171003_AiC.zip
2017_M05_rec_August_2017_Basildon & Brentwood_99E_ALLprov_20171003_AiC.zip
2017_M05_rec_August_2017_Basildon & Brentwood_99E_nt2048_20171003_AiC.zip
2017_M05_rec_August_2017_Basildon & Brentwood_99E_rddRF4_20171003_AiC.zip

 

 

Regular expression to find month names:

(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))

 

(\b\d{1,2}\D{0,3})?\b(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|(Nov|Dec)(?:ember)?)\D?(\d{1,2}\D?)?\D?((19[7-9]\d|20\d{2})|\d{2})

 

Regular expression tofind Month Year:

((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))

 

 

 

RecOrPostRec:

(?:[_])(?<RecOrPostRec>(?:(Post)?(?:Rec)))(?:[_])

 

CCGCode:

(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])

 

FileType (EMMain/EPMain):

(?:[_])(?<FileType>[a-zA-Z]{6,15})(?:[_])(?:(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_]))

Save:

(?<RecOrPostRec>_Rec_)|(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))|(?<Year>[0-9]{4}_)|(?<FileType>_EMMain_)|(?<CCGCode>_[0-9][0-9a-zA-Z]{2}_)

Full regex – never change or delete:

(?:[_])(?<RecOrPostRec>(?:(Post)?(?:Rec)))(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>[a-zA-Z]{6,15})(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>(?:FFP)|(?:T2O))

Full regex – v2:

(?<ExtractSetType>[0-9a-z]+)_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[ ](?<Year>[0-9]{4})_(?<FileType>(?:[a-z]{2,15}_)?[a-z]{2,15})_(?<CCGCode>[0-9][0-9a-z]{2})_(?:(?<UniquePart>[a-zA-Z0-9_]*))_(?<ProcessorType>FFP|T2O)

AiC Rec EMMain:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

AiC PostRec EMMain:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

T2O Rec EMMain:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

T2O PostRec EMMain:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

 

 

 

AiC Rec EMError:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

AiC PostRec EMError:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

T2O Rec EMError:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

T2O PostRec EMError:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

 

 

Current version:

 

(?<ExtractSetType>[0-9a-z]+)_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[ ](?<Year>[0-9]{4})_(?<FileType>(?:[a-z0-9]{2,15}_)?[a-z0-9]{2,15})_(?<CCGCode>[0-9][0-9a-z]{2})_(?:(?<UniquePart>[a-zA-Z0-9_]*))_(?<ProcessorType>FFP|T2O)(?<Extra>\S*)(?<FileExtension>[.]+[a-z]+)

 

(?<ExtractSetType>[0-9a-z]+)_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[ ](?<Year>[0-9]{4})_(?<FileType>(?:[a-z0-9]{2,15}_)?[a-z0-9]{2,15})_(?<CCGCode>[0-9][0-9a-z]{2})_(?:(?<UniquePart1>[a-zA-Z0-9_]*))_(?<T2O>T2O)?_(?:(?<UniquePart2>[a-zA-Z0-9_]*))_(?<FFP>FFP)(?<FileExtension>[.]+[a-z]+)

 

Regular expression to select archive name:

(?<FinancialYear>[0-9]{4})_(?<FinancialMonthName>[a-z]+[0-9]{2})_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))_(?<CalendarYear>[0-9]{4})_(?<CCGName>(?:[a-z& _]+))_(?<CCGCode>[0-9][0-9a-z]{2})_(?<ExtractType>[a-z0-9]+)_(?<Date>[0-9]{8})_(?<ProcessorType>[a-z]{3})(?<FileExtension>[.]+zip)

 

Regular expression to select RECT files:

(OS)_((Post)([a-zA-Z])|(Rec)(_[A-Z]))_RECT_([a-zA-z0-9_])*FFP.csv

(?<ExtractSetType>OS)_((?<ConcilationPostRec>Post)([a-z])*|(?<ConcilationRec>Rec)(_[a-z])*)_(?<FileType>RECT)_([a-z0-9-_])*(?<ProcessorTypeRECT>COMPLETE_NP_FFP).(?<FileExtension>csv)