I'm getting ready to deploy my SSIS packages to a real server (woohoo) after testing on my local SQL box. What files have to be deployed to the SQL server? I'm creating a folder for each package (easier for these guys to maintain when my contract is up). Does only the .dtsx and .dtsConfig file go or is there any other files that have to be copied over to the SQL server?
Hi Guys,
I have a problam. I wanna deploy my application that it has data.mdf file.
When I install and test it I get this message:
---------------------------
Microsoft SQL Server Login
---------------------------
Connection failed:
SQLState: '42000'
SQL Server Error: 5133
[Microsoft][SQL Native Client][SQL Server]Directory lookup for the file "C:Program FilesMyDicDatabaseData.mdf" failed with the operating system error 5(Access is denied.).
Connection failed:
SQLState: '42000'
SQL Server Error: 1832
[Microsoft][SQL Native Client][SQL Server]Could not attach file 'C:Program FilesMyDicDatabaseData.mdf' as database 'MyDic'.
---------------------------
OK
---------------------------
I have to mention that:
SQL Server: SQL Server 2005 Express
Operating System: Windows XP SP2
and I'm Local Admin.
Some parts of my code are:
lstrcpy(szDataDir,
TEXT("DRIVER={SQL Native Client};SERVER=.\sqlexpress;Trusted_Connection=Yes;DATABASE=MyDic;AttachDBFileName="));
GetCurrentDirectory(sizeof(szCurrentDir), szCurrentDir);
lstrcat(szCurrentDir, TEXT("\Database\Data.mdf;"));
lstrcat(szDataDir, szCurrentDir);
SQLDriverConnect(hdbc, hWnd, szDataDir, SQL_NTS, szConnStrOut, sizeof(szConnStrOut), &cbConnStrOut, SQL_DRIVER_COMPLETE);
How can I fix it?
Regards
As the subject, I want to deploy my program with visual studio 2005 and
pocket pc 2003, but failed when it comes with big file(bigger than 1M).
Enviroment vs2005, pocket pc 2003, activesync4.1, .net cf 2.0.
Error message here:
Deployment and/or registration failed with error: 0x8973190e. Error
writing file
'%CSIDL_PROGRAM_FILES%SQLCEResultSetSampleCSNorthwind.sdf'. Error
0x8007274c:
Thanks in advance.
Hi all,
How can I deploy my website sql database files, which I have done using Visual web developer. (Visual Studio .net 2005)
Connection srting all these are different in this VWD.
<connectionStrings>
<add name="GoalConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
Hi, we are deploying in an external server a report model and some reports developed with it.
We already have a procedure to deploy the model and reports one by one using upload files feature, we are worried about not to deployed all reports we have to (mistake in manual process). Is there a way to automatize this process?, a command, a program o somethink like this?.
Thanks in advance.
Julio
Hello everyone,
This is my first time posting here, I hope this questions has not been asked before. I tried to search for it but I came not with nothing.
Recreating the error :
I am using VS2005. I created a Pocket PC 2003 project.
I have downloaded the SQL Server Compact Edition and installed it. I get the System.Data.SqlServerCe.dll file from the installation directory.
I reference to that DLL using Add Reference in VS2005.
Build it. In the Bin folder, a long list of files suddenly appears.
System.data.dll
System.data.oracleClient.dll
system.web.dll
system.enterpriseservices.dll
system.enterpriseservices.wrapper.dll
system.transactions.dll
and the rest of your original files in Bin
The worst of it all, all of these files are deployed into the Emulator! Causing it to run out of memory and unable to deploy.
Something is not right here, I just cannot figure it out! If this happens, each mobile devices can hold one applications. Thats not the way it should be, right?
If you have solved this before, do help. I am at my wits end at the moment.
Thanking you in advance.
Sincerely,
Lasker
Hi,
Every one
When I open Dts Package of my Server A , from Server B Machine -(Enterprise Manager) it is giving me error
Microsoft Data Transformation Services. (DTS PACKAGE)
ERROR - The Parameter is incorrect.
but at the same time I can create new package from Server B Machine (Enterprise Manager), & after that i am able to open that new created package but still not other packages.
I don't know what is going on
If any one has any clue or suggestion please suggets this
Thanks.
Nirmal
I would like to deploy several reports to production server, Do i need to install reporting services entire software in order to run the reports or is it possible to just have runtime files installed on it to run the reports.
please help, i have almost 100 reports to be deployed on this server which is located in other country.
Thanks for the helpful information.
(i am using SQL server 2005 / reporting services 2005.)
Can i run a package outside of IDE if we donot have Integration Services installed locally.
View 4 Replies View RelatedAm getting errors trying to deploy a dtsx created by ms (the reporting services execution log one) to which I have made zero changes, but it is not working (2 errors shown below)
error from deployment wizard:
===================================
Could not save the package "H:SSISRSlogRSExecutionLog_UpdateinDeploymentRSExecutionLog_Update.dtsx" to SQL Server "xxxxxxxxxxx". (Package Installation Wizard)
===================================
The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServer(Package package, IDTSEvents events, String serverName, String serverUserName, String serverPassword)
at Microsoft.SqlServer.Dts.Deployment.DtsInstaller.SavePackageToSqlServer(WizardInputs wizardInputs, String packagePassword, Boolean bUseSeverEncryption, String serverName, String userName, String password, String packageFilePath, List`1 configFileNames)
at Microsoft.SqlServer.Dts.Deployment.DtsInstaller.InstallPackagesToSqlServer(WizardInputs wizardInputs)
===================================
The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToSQLServer(IDTSPackage90 Package, IDTSEvents90 pEvents, String ServerName, String ServerUserName, String ServerPassword)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServer(Package package, IDTSEvents events, String serverName, String serverUserName, String serverPassword)
error from sql management studio
===================================
Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)
I have a legncy DTS package when upgrading to SQL 2005, I used "SQL Business Intelligence Development Studio" to create a project, the control flow is "Execute 2000 DTS Package", by right click "Execute Task" of the control flow, the color changed from yellow to red, the message said "package Execution completed". When I try to schedule this package in SQL Server Agent, the job failed. When I run dtexec, got following error message.
C:>dtexec /file "C:Documents and SettingslyangMy DocumentsVisual Studio 200
5ProjectsTraingDTSTraingDTSDTSTraining.dtsx"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Source: Execute DTS 2000 Package Task
Execute DTS 2000 Package Task is initiated: 0% complete
End Progress
Error: 2008-05-08 09:13:35.74
Code: 0x00000000
Source: Execute DTS 2000 Package Task
Description: System.Runtime.InteropServices.COMException (0x80040427): Execut
ion was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteT
hread()
End Error
Progress: 2008-05-08 09:13:35.74
Source: Execute DTS 2000 Package Task
Execute DTS 2000 Package Task is completed: 100% complete
End Progress
Warning: 2008-05-08 09:13:35.74
Code: 0x80019002
Source: DTSTraining
Description: The Execution method succeeded, but the number of errors raised
(1) reached the maximum allowed (1); resulting in failure. This occurs when the
number of errors reaches the number specified in MaximumErrorCount. Change the M
aximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Can anyone tell me what does the error message mean and how to solve them?
Thanks very much in advance!
Lisa
Hi,
I have a set of packages, which I need to run through Sql Agent to automate it. This package runs Ok when I use DtexecUI but when I run it through SQl Agent with the same options it fails.
THe error it fails on is the old 0xC0202009, acquire connection failed error. The component on which it fails is an access database with the location and query fed by variables that pick up this from a table in a sql server database. THe access database is on a shared drive on a different machine
Does anyone have any idea as to why this should be happening
Thanks
Nathan
Hello,
I am trying to use the Import export wizard to created a package,
using the provide source query option. If i just copy the query from a text file
and try to paste , sql only accepts it partially. so i saved it as a sql file
and then opened it in the window. However, when i click on 'next' or 'parse' , i
get the below error.
TITLE: SQL Server Import and Export Wizard
------------------------------
The statement could not be parsed.
------------------------------
ADDITIONAL INFORMATION:
Deferred
prepare could not be completed.
Query timeout expired (Microsoft SQL Native Client)
The query is pretty big, but it executes successfully in the Management Studion Query Explorer window. I had no problem creating a package using DTS with the same query in Sql 2000. I also tried to migrate the package already existing in Sql 2000, but even though i can migrate it successfully , the package does not execute in Sql 2005. Also i tried other queries which are as big as this one, again the query source window during import/export does not seem to accept large queries??? I depend heavily on large queries for my packages, which i run daily. I have not had any issues with this is sql 2000. Can someone help me with this???
Thanks in advance.
Ram
I have an SSIS package that executes a stored procedure. In that stored procedure is a try/catch block. If the try isn't successful, it goes to the catch block which does a rollback. So when I execute the SSIS package, it tells me that the stored procedure was ran successfully because there essentianlly were no errors and everything ran fine, but in reality, everytime it goes into the catch block and does a rollback, I want the SSIS package to fail as well. How would I send back a failure to the SSIS package from the stored procedure?
View 13 Replies View RelatedHi,
now i am currently using SSIS Package using BUI, The Source and Destination File we Given Manullay Connect the Server name ,And Table . Instead of given Manual . How to create Global Variable Connection Manager.
Suppose Today i am Working Developement Server. Latter i will be changed Production Server Database. At That time we have to Going to Modify all the Connection .Instead of This How to Create the Connection Manager Gloabe Variable . and How to Use .Please Any one give Sample For Connection Manager variable for Different Server.
Thanks & Regards,
Jeyakumar.M
chennai
Hi !
I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.
For Exmaple :
Source Table Name : tbl_source
following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip
table contain only one records:GRD1,SRD1,FRD1,100,Product
I want Insert the Destiantion table the Follwing Condition. using Conditional Split.
1)Cond1 (!(ISNULL(GRD1))
2)Cond2 !(ISNULL(SRD1))
3)Cond3 !(ISNULL(FRD1))
I need the Following output
Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)
Coulmn Name , Column Value , ID
Row 1 GRD GRD1 100
Row 2 SRD SRD1 100
Row 3 FRD FRD1 100
How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.
Thanks & regards
M.Jeyakumar
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedIn the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
Any Suggestions?
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
Dears,I have devoloped an application ASP.NET 2.0.Before I have builded the aspnetdb throught the command then I built some tables and stored procedures on this db. (My db is sql express 2005 and my hoster db is sql 2005 workgroup)My hoster doesn't allow connection Management studio express, doesn't allow attach or restore functionalities.Than I have built my script db (contains Tables, Views and Stored Procedures), I have substituted the dbo with my user account in the script because my hoster doesn't allow the dbo access.I have also transfered my site files (.aspx, img, etc.) on the server.When I try to access the db I receive this error (for example when I push on the button create user):The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. Please could you help me? Thank you.
View 1 Replies View RelatedHi All,
Can anyone tell me,is it possible to create an exe or msi for sql stored procedures,tables and triggers?I want to deploy the database objects(stored proc,tables,views and functions) as an exe file..just like publishing and deploying the asp.net application.Is it possible for sql server database objects.Pls,let me know.
Thank U
I'm deploying an ASP .Net application on which 20 users will be working at the same time. The database will start at less than 100mb. Is SQL EXPRESS 2005 powerfull enough to use for such an application. Thanks in advance for your answers
View 1 Replies View RelatedI have a report that I am trying to deploy. I right-clicked my report in the solutions explorer, then select "deploy" but then I get the following error:
Microsoft Report Designer
The project cannot be deployed because no target server is specified. Provide a value for the TargetServerURL property in the property page for this project.
So then I go to the properties page but there isn't a TargetServerURL field! There is only a file name and full path field. Does anyone know what is wrong? Could it be that I need IIS installed on my machine?
More info:
Software installed from http://www.microsoft.com/express/sql/download/default.aspx:
-Microsoft SQL Server 2005 Express Edition
-SQL Server Management Studio Express
-Install Microsoft SQL Server 2005 Express Edition with Advanced Services
-Microsoft SQL Server 2005 Express Edition Toolkit
Windows XP Home Edition
Hi, all,
I am looking for a way to deploy SSIS pkg from dev to test/prod.
Both BOL and MSDN/TecNet samples are complicated. Do I miss the old good days of DTS!
Thanks!
Hi all,I'm new to SQLServer (Express edition) so I was wondering: if the webhost supports SQLServer 2005 do I just need to move the .MDF file to mydirectory on the web host to be able to use it?Thanks,Lorenzo
View 3 Replies View RelatedHi all,
I have created a database in MS-Access. I want to deploy that database on the local server, how can i do it?
Let me clear, i have created a DSN for my database. I want to deploy a report on RServer(on SQL Server2005). This DSN will be the datasource for the report. When i am trying to deploy this report with this DSN i am getting an error message
"Cannot create a connection to data source 'DataSource2'. (rsErrorOpeningConnection)". Could any one help me?
Thanks,
Hi, there;
I created a report using Report Sevice, but I cannot deploy it because of the security issue. If I try to open IIS, I got "Access Denied" message.
I checked another two developers' user settings, they are in the same user group as I am, but they can create/deploy report service.
So what should I need to create/deploy my report service?
cheers.
I have created a report project file on my local machine.Now I'm trying to deploy those reports to a development server. I have installed sql server and tools in my local machine but never configured reporting services. When I try to deploy those reports, it asks username and password for reporting services login.
But usually as far as I know, i should be able to deploy my reports without entering password etc. Even if I enter username and password, still they dont work.
Is that becase I install SQL server in my local machine?
Does anyone know why?
Thanks
I am trying to deploy a project to localhost, but I get the following error. I have only one package in this project.
"Could not save the package "C:......lah.dtsx" to SQL Server "localhost".
I tried deploying other projects that I've successfully deployed before and those also fail with the same message. Am I missing something really basic here?
Hi,
I have a web project and created a setup project for it. And I have to create reports using sql server reporting services. For this I have created an reportserver project and created two reports.
I have to create a setup file to install these reports in a remote machine.
How to do this?
where this .rdl and .rds files will be stored in the reportserver.
Regards,
Murali
I'm attemping to deploy a crypto class that we use on a desktop app to SQL Server. I'll use the encryptstring/decryptstring found in this class as User Defined Functions on SQL Server. When I try to deploy the class I'm getting this error:
Error 1 Method, property or field 'EncryptString' of class 'EncryptingDecryptingOASISPWDS.Crypto' in assembly 'EncryptingDecryptingOASISPWDS' is not static. EncryptingDecryptingOASISPWDS
Here is the code:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Security.Cryptography
Imports System.Text 'for UnicodeEncoding
Imports System.IO 'for MemoryStream, FileStream, Stream, Path
Imports System.Threading 'for Thread.Sleep
Partial Public Class Crypto
'Private Const CodeVersion As String = "01.00.00"
'class member variables
Private m_sPassPhrase As String
'class private variables
Private mbytKey() As Byte 'crypto key
Private mbytIV() As Byte 'Initialization Vector
Private mbKeyIsSet As Boolean = False
Private mksKeyNotSetException As String = "Crypto passphrase is not set."
Private mksPassphraseTooSmall As String = "PassPhrase length must be at least {0} characters."
'--- class constructors
Public Sub New()
'no passphrase is useful for GetHashString/ValidPassword methods
End Sub
Public Sub New(ByVal CryptoPassPhrase As String)
PassPhrase = CryptoPassPhrase
End Sub
'--- class public properties
'generate and store encryption key & iv from passphrase
Public Property PassPhrase() As String
Get
Return m_sPassPhrase
End Get
Set(ByVal Value As String)
Const iMinLength As Integer = -1 '-1 disables min length
m_sPassPhrase = Value.Trim
'enforce a rule on minimum length if desired here
If (Value.Length > iMinLength) Or (iMinLength = -1) Then
Dim sha2 As New SHA256Managed()
'256 bits = 32 byte key
mbytKey = sha2.ComputeHash(BytesFromString(m_sPassPhrase))
'convert to Base64 for Initialization Vector, take last 16 chars
Dim sKey As String = Convert.ToBase64String(mbytKey)
mbytIV = Encoding.ASCII.GetBytes(sKey.Remove(0, sKey.Length - 16))
mbKeyIsSet = True
sha2 = Nothing
Else
mbKeyIsSet = False
Throw New Exception(String.Format(mksPassphraseTooSmall, (iMinLength + 1).ToString))
End If
End Set
End Property
'decrypt a stream
Public Function DecryptStream(ByVal EncryptedStream As MemoryStream) As MemoryStream
If mbKeyIsSet Then
Try
'create Crypto Service Provider, set key, transform and crypto stream
Dim oCSP As New RijndaelManaged()
oCSP.Key = mbytKey
oCSP.IV = mbytIV
Dim ct As ICryptoTransform = oCSP.CreateDecryptor()
Dim cs As CryptoStream = New CryptoStream(EncryptedStream, ct, CryptoStreamMode.Read)
'get bytes from encrypted stream
Dim byteArray(EncryptedStream.Length - 1) As Byte
Dim iBytesIn As Integer = cs.Read(byteArray, 0, EncryptedStream.Length)
cs.Close()
'create and write the decrypted output stream
Dim plainStream As New MemoryStream()
plainStream.Write(byteArray, 0, iBytesIn)
Return plainStream
Catch ex As Exception
Return Stream.Null
End Try
Else
Throw New Exception(mksKeyNotSetException)
End If
End Function
'decrypt a string - wrapper without Base64 flag (True by default)
Public Function DecryptString(ByVal EncryptedString As String) As String
Return _DecryptString(EncryptedString, True)
End Function
'decrypt a string - wrapper with Base64 flag
Public Function DecryptString(ByVal EncryptedString As String, ByVal Base64 As Boolean) As String
Return _DecryptString(EncryptedString, Base64)
End Function
'encrypt a stream
Public Function EncryptStream(ByVal PlainStream As MemoryStream) As MemoryStream
Try
'open stream for encrypted data
Dim encStream As New MemoryStream()
'create Crypto Service Provider, set key, transform and crypto stream
Dim oCSP As New RijndaelManaged()
oCSP.Key = mbytKey
oCSP.IV = mbytIV
Dim ct As ICryptoTransform = oCSP.CreateEncryptor()
Dim cs As CryptoStream = New CryptoStream(encStream, ct, CryptoStreamMode.Write)
'get input stream into byte array
Dim byteArray() As Byte = PlainStream.ToArray()
'write input bytes to crypto stream and close up
cs.Write(byteArray, 0, PlainStream.Length)
cs.FlushFinalBlock()
cs.Close()
Return encStream
Catch ex As Exception
Return Stream.Null
End Try
End Function
'encrypt a string - wrapper without Base64 flag (True by default)
<Microsoft.SqlServer.Server.SqlFunction()> _
Function EncryptString(ByVal PlainText As String) As String
Return _EncryptString(PlainText, True)
End Function
''encrypt a string - wrapper with Base64 flag
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Function EncryptString2(ByVal PlainText As String, ByVal Base64 As Boolean) As String
Return _EncryptString(PlainText, Base64)
End Function
'calculates the hash of InputValue, returns a string
'- SHA1 hash is always 20 bytes (160 bits)
Public Function GetHashString(ByVal InputValue As String) As String
Try
Dim inputBytes() As Byte = BytesFromString(InputValue)
Dim hashValue() As Byte = New SHA1Managed().ComputeHash(inputBytes)
Return BytesToHexString(hashValue)
Catch ex As Exception
Return String.Empty
End Try
End Function
'returns True if hash of Passphrase matches HashValue
Public Function ValidPassword(ByVal Passphrase As String, ByVal HashValue As String) As Boolean
Return (GetHashString(Passphrase) = HashValue)
End Function
'internal string decryption
Private Function _DecryptString(ByVal EncryptedString As String, ByVal Base64 As Boolean) As String
Try
'put string in byte array depending on Base64 flag
Dim byteArray() As Byte
If Base64 Then
byteArray = Convert.FromBase64String(EncryptedString)
Else
byteArray = BytesFromString(EncryptedString)
End If
'create the streams, decrypt and return a string
Dim msEnc As New MemoryStream(byteArray)
Dim msPlain As MemoryStream = DecryptStream(msEnc)
Return BytesToString(msPlain.GetBuffer)
Catch ex As Exception
Return String.Empty
End Try
End Function
'internal string encryption
Private Function _EncryptString(ByVal PlainText As String, ByVal Base64 As Boolean) As String
Try
'put string in byte array
Dim byteArray() As Byte = BytesFromString(PlainText)
'create streams and encrypt
Dim msPlain As New MemoryStream(byteArray)
Dim msEnc As MemoryStream = EncryptStream(msPlain)
'return string depending on Base64 flag
If Base64 Then
Return Convert.ToBase64String(msEnc.ToArray)
Else
Return BytesToString(msEnc.ToArray)
End If
Catch ex As Exception
Return String.Empty
End Try
End Function
'returns a Unicode byte array from a string
Private Function BytesFromString(ByVal StringValue As String) As Byte()
Return (New UnicodeEncoding()).GetBytes(StringValue)
End Function
'returns a hex string from a byte array
Private Function BytesToHexString(ByVal byteArray() As Byte) As String
Dim sb As New StringBuilder(40)
Dim bValue As Byte
For Each bValue In byteArray
sb.AppendFormat(bValue.ToString("x2").ToUpper)
Next
Return sb.ToString
End Function
'returns a Unicode string from a byte array
Private Function BytesToString(ByVal byteArray() As Byte) As String
Return (New UnicodeEncoding()).GetString(byteArray)
End Function
'Return True when the file is available for writing
'- returns False if output file locked, for example
Private Function CheckWriteAccess(ByVal FileName As String) As Boolean
'2 second delay with 10,200
Dim iCount As Integer = 0 'retry count
Const iLimit As Integer = 10 'retries
Const iDelay As Integer = 200 'msec
While (iCount < iLimit)
Try
Dim fs As FileStream
fs = New FileStream(FileName, FileMode.Append, _
FileAccess.Write, FileShare.None)
fs.Close()
Return True
Catch ex As Exception
Thread.Sleep(iDelay)
Finally
iCount += 1
End Try
End While
Return False
End Function
End Class