Hi,
I am trying to find a feature- that was there in DTS but I can't locate it in SSIS
Using DTSRUNUI, we could generate a encrypted command line for executing the pacakge. Now using DTEXECUI, I can get the command line but is there any way in which I can get it in the encryted format??
Thanks.
When I try to run a .dtsx file or even the Dtexecui.exe I get following error:System.IO.FileNotFoundException.
Thanks
Bill
Hi all,
I have a DTEXECUI.exe related question. This util is installed on our QA server, but not our dev-server. Is this optional during installation? I can't ask our admin, he's on vacation.
Maybe important to note, is that the QA-server runs on 64 bit, where as the other servers are only 32-bit..
Regards & Season greetings,
T.
Is there an option(s) that will allow me to reduce the amount of output to the Package Execution Progress window? My package has a number of steps, but trying to keep track of progress is difficult at best given the deluge of messages this window displays.
I would prefer output similar to the SQL Import/Export wizard, which shows executing tasks on a single line with a status and a link to any error messages off to the side. Is this available somehow, or do I have to build it myself?
Thanks, any help appreciated.
I have a very simple SSIS package that reads an environment variable, assigns it to a variable that is sent in an email, and also logs it to a text file. If I execute the package on the server via DTExecUI, it works fine as expected. However, if I schedule it as part of a job, I get the following error:
Code: 0x00000002 Source: Script Task Description: The script threw an exception: Object reference not set to an instance of an object. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:08:00 AM Finished: 9:08:01 AM Elapsed: 0.859 seconds. The package execution failed. The step failed.
The script is as follows:
Imports System
Imports System.Data
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim Path As String = "C:TestEnv.txt"
Dim Env As String = Environment.GetEnvironmentVariable("SSISConfig").ToString()
Dim Sw As StreamWriter
Dts.Variables("SSISConfig").Value = Env
If File.Exists(Path) = False Then
Sw = File.CreateText(Path)
Else
Sw = File.AppendText(Path)
End If
Try
Sw.WriteLine(Env)
Finally
Sw.Close()
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I have puttered with this for a while now and can't figure out what the problem is. Any ideas?
I installed SP2 yesterday on our production server. I had some
problems with that but it seemed to have installed so that the Datbase
engine and the Integration Services is patch at least.
But after the update we can no longer bring up the "properties" of
SSIS packages. If we connect to the integration service and choose run
package the GUI just wait for a while and then nothing happens. We get
an error in the event log thou ..
Event Type: Error
Event Source: .NET Runtime 2.0 Error Reporting
Event Category: None
Event ID: 5000
Date: 2007-11-12
Time: 10:51:07
User: N/A
Description:
EventType clr20r3, P1 dtexecui.exe, P2 9.0.242.0, P3 45cd726d, P4
microsoft.sqlserver.manageddts, P5 9.0.242.0, P6 45cd7534, P7 21, P8
14, P9 qg0f5oshpmqbs2pm34uokrbhqqrfhscr, P10 NIL.
Anyone can make any sense of that?
If we open a package that we have scheduled in the SQL Agent we get an
error messages saying "No description found" .. really strange stuff
is going on here ..?
Please, any help would be appreciated ..
In a script task, I prompt the user for some parameters. I use the System::InteractiveMode variable to tell me if the package was launched via user interaction or from an automated process such as a file watcher. I display the prompts only if System::InteractiveMode is true. When I run from VS 2005 then I get the prompts, and when the package runs automated (dtexec) the prompts are not shown, so all is OK. However, when the package is run from dtexecui, I do not get the prompts because it seems the InteractiveMode value is false. Shouldn't the InteractiveMode value be true when the package is run from dtexecui since it is a user interface?
View 3 Replies View Related
I've worked for awhile to get an SSIS package that connects to Oracle to succesfully run via DTExecUI from a 64 bit SQL Server. I completely understand that DTExecUI runs in 32 bit mode, but I need to initiate the package from a stored proc so I need to be able to utilize the 32 bit DTExec from either a xp_cmdshell call or from an sp_start_job (thanks to MatthewRouche for the suggestion). Both Ora92 and 10g were installed on the SQL Server.
Here's what I've tried unssuccessfully
1. SQL Job - Just calling the package from an SSIS step. I wouldn't expect this to run because it is 64 bit DTExec.
2. SQL Job - A job that calls the 32 bit version of dtexec from cmdexec. Here is 2 examples I have tried, the first is because I came across several postings saying that there is a problem with the "(x86)" for the Oracle driver.
\server_nameE$ProgramFilesLegacyMicrosoftSQLServerX8690DTSBinnDTExec.exe /DTS "PackageName" /SERVER "SERVER_NAME" /SET Package.Variables[PLT].Value;"8H" /CONFIGFILE "\SERVERConfigFile.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
\server_nameE$Program FilesMicrosoft SQL Server (x86)90DTSBinnDTExec.exe /DTS "PackageName" /SERVER "SERVER_NAME" /SET Package.Variables[PLT].Value;"8H" /CONFIGFILE "\SERVERConfigFile.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
Both of these give me the error below:
System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
I'm assuming that it is running in 32 bit mode because this was at the beginning of the job history log:
Executed as user: "XXX". ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved
3. I then tried to call the package from a batch file on a file server that had integration services installed on. Got the same error as above.
I'm obviously missing something if I can get it to work in DTExecUI but not DTExec 32-bit.
Any ideas? Below are some postings I have already visited several times:
Windows 64 bit and Oracle
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=358365&SiteID=1
Connecting to Oracle on 64-bit (x64) machine
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159581&SiteID=1
One paragraph that stands out on this posting is:
Find the location of the application that is generating the error. Check the path to this location and see if it contains any parenthesis. If so, you must relocate the application to a directory without any parenthesis in the path.
This didn€™t work for me, unless I was doing it wrong.
Error retrieving numeric data from Oracle 10g on 64 bit Itanium SSIS server
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1183296&SiteID=1
Connection Problems to Oracle Database
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1307460&SiteID=1
If this posting was complete, it looks like what I was looking for.
I want to input a parameter to my package. I have a variable, and I tried changing its value from DTExecUI.
I did everything as mentioned in http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx but I still get an error -
Could not set "Package.Variables[User::var_input_step_Name].Properties[Value]" value to "RAW".
The variable was originally read from a XML config file. I stored the package to MSDB. I then ran dtexecui, selected my msdb package, and in Set Values, I entered property path and value. I also tried removing the "", and I even deleted the XML configuration. Still the same error.
What am I doing wrong? Any Package Settings to make?
TIA
Kar
Hi,
when i deploy my package using the DTExecUI thru the Package Execution Utility.when we execute the package . we are getting the following error.
The Product Levelis insufficient for component "Flat File Source(1)"
the package design is
FlatFileSource ----> OlEDBDestination
what could be the solution for this?
Thanks
Jegan
I have a sql 2005 DB that stores personal information including Social Security #. How can i encrypt the SSN in Sql and then Decrypt it in a asp.net 2.0 page?Thanks
View 3 Replies View Related
I am calling my rports from the server useing URL method.I need to encript this URL please help me.
I have an application that has stores sensitive data in an SQL server and I am currently handling this through my ASP.NET application using the encryption classes in C#.
One of the things we would eventually like to be able to do is use other programs (like Microsoft Access) to run advanced Querys on the tables and retrive the data. With the encyption being done in C#/ASP all that would be returned would be the encrypted data.
I wondering if there is a way to build a layer to encrypt/decrypt data at the database level, my searches haven't yeiled to much info (As well as a trip to Borders) as I haven't seen any books that even touch on this.
I don't know a lot about SQL right now (Mainly only MySQL so Stored Procs and all that stuff are really new to me) so I don't know if I would go about it this way or not? (Using a stored procedure)
Can anyone reccommened where I should start to learn about accomplishing this? Books are usually the best help but I'll take any kind of information that can be thrown my way :-)
Thanks!
Is it possible to encrypt data in one column. I always thought it wasn't possible but I need to know for sure.
View 1 Replies View RelatedHi all,
Can anyone help me regarding encrypting a password.
While i put my password in a table, it shd be encrypted form so that none of the user shd able
view that and for admin purpose i shd able to view, that is, i shd able to decrypt the password
show to the user. The password is stored in a table as a field.
waiting for ur replies.
thanks in advance.
regards,
krr
Is there any way by which a SQL Server database can be encrypted.
Regrads
Hi:
I was wondering if any of you had any idea about how I could achieve to encrypt a certain field(s) or a whole table in one of the table in my Database. Somewhat, I want to hide the password field from everyone's curious eye.
IF possible please also reply to this email :
alexiscloutier@digisoftcorp.com
Hi,We want to encrypt MS Sql Server data files - .mdf and .ldf withlogged in user certificate and make sure that MS Sql Server service(running as Local System Account) can decrypt it.Is it possible to encrypt data files with a certificate that residesin logged in user'scert store and also MS SQL Server Service 'service account's certstore?You can access 'service account's cert store through mmc -
Quote:
Hello,I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I havebeen working with SQL Server, Building ASp WebSites for many years now.I am by no means an expert - nor have I had ANY formal training. So ebarwith me if my questions seem elementary...I have some questions regarding sensitive data and encryption.There is a project that is headed my way were the social security numberis being used as the unique identifier for an account. I have alwaysused as identity column as a unique identifier. What would be the prosand cons of using the SSN as a unique identifier?#1 How do I go about encrypting the number to store in the DB. Is thisdone within SQL Server? Or before the data is inserted?#2 Is it possible to use an encrypted field as a unique identifier?My gut tells me to use the identity column , encrypt the SSN and not useit as any part of an identifier.Thank You for your Help.. Happy New Year!Please Reply to the Newsgroup.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 12 Replies View RelatedA friend of my self asked me how he can save a password not as clear text. He wanted to encrypt the password and save the encrypted string in the database.
How can he do this? Maybe somebody can help me here.
Regards Markus
Hi,
I have creating a stored procedure in sql server 2005 to encrypt a parameter value that comes from an asp.net page. this value is of type varchar.
Now i have declared a paramater as varbinary to accept this value in the stored procedure. And encrypting it using symmetric key which is encrypted by certificate.
Now when i enter a value into the textbox in asp.net page. it shows
"implicit conversion from varchar to varbinary not possible" pls tell me what is the solution? do i have to convert the value in vb code while accessing the value of the textbox or do i have to convert the value while passing the value when call stored procedure.
pls tell me the solution so that i don't jave to change any coding in vb.
some solution in sql server itselt.
Thank you
Gaurav
I have been tasked with encrypting an entire database, and I need some assistance.
Everything that I have looked at leads me to believe that the only way to encrypt a whole database is to encrypt each column seperatly. I am completely stumped and could really use some help.
Is it possible to encrypt a whole database? if so, how?
If not, what other options do I have?
Thanks in advance for the help.
I have a bunch of packages that I was working with on a development server and have now moved to a production server. The package protection level was set to DoNotSaveSensative during the development, when I loaded them onto the production server I changed the protection level to EncryptAllWithPassword. I have a few parent packages that call other packages using the execute package task. Since, I now have encrypted packages i had to supply this task with the password, that is where the problem started. The parent package fails when it tries to execute the child package, saying the password was not supplied or it is wrong. I assure you it is not wrong, so is the parent package not storing the password since it was created with no password? Next I created a new parent package and supplied it with a password to call the child package and it works fine. Do I have to recreate all of my parent packages in this way? Is there a way for the parent package to rebuild itself and store the password? Any help would be appreciated.
Nick
I am trying to encrypt a column, and then do a select and decrypt the results.
I believe that I am encrypting the data properly, but whenever I try and do a select the column comes back as NULL.
If I do a select without trying to decrypt the column (select * from table) I get the following:
Name | Dept | Encrypted_Info
-----------------------------------------------
scott | it | 0x38383131
If I try and decrypt it I get this
Name | Dept | Encrypted_Info
-----------------------------------------------
scott | it | NULL
This is the query that I put together to create the database, table, DB Master Key, Cert, encrypt the column, and then select and decrypt the result:
create database QA_Encrypted
Go
create table QA_Encrypted.dbo.Enc_Table1
(UserName varchar(50),
Dept varchar(50),
Encrypted_Info varbinary(MAX))
Go
use QA_Encrypted
Go
if not exists (select * from sys.symmetric_keys where symmetric_key_id = 101)
create Master Key encryption by password = 'Pa55w0rd'
Go
Create Certificate QACert
with subject = 'Certificate for Testing'
Go
Create symmetric Key QAEncryptionKey with Algorithm = DES
Encryption by certificate QACert
Go
open symmetric key QAEncryptionKey decryption by certificate QACert
update QA_Encrypted.dbo.Enc_Table1 set Encrypted_Info = EncryptByKey(Key_GUID('QAEncryptionKey'), 'AUTHENTICATOR')
Go
use QA_Encrypted
insert into dbo.enc_table1 values ('scott', 'it', Convert(varbinary,'8811'))
OPEN Symmetric Key QAEncryptionKey
Decryption by CERTIFICATE QACert
select * from dbo.Enc_Table1
Select Username, Dept, Convert(varchar, DecryptByKey(Encrypted_Info)) as "decrypted"
from dbo.Enc_Table1
Any help would be greatly appreciated.
Thanks in advance
Does anyone know where there is a good summary of what you can do to encrypt data going between SQL Server and it's clients, and for that matter any other security things you can do with SQL Server ? I've been asked to write a document on this for someone and quite frankly I've got too much proper work to do...
View 1 Replies View RelatedI need to encrypt one column of a Sql Server 7 database. I would like to use some sort of function to accomplish this so regular reporting tools can grab the encrypted data.
View 1 Replies View RelatedHi,
I usually work with MySQL where it is able tl encrypt a password in a database (for users /clients etc) is there a way to do somthing similar in access.
Thanking you in advance
Oli
I have programs written in sas. The programs themselves can be treated like a text file for this purpose. I need a program that will encrypt my program then when needed decyrypt it storing it into a temp file, run it and then delete it.
any thoughts or anything out there to use.
Nicole
Any example on encrypting a SSN Field using TDE?
[URL].....
encrypting a column in a sql server database. I have userid and password columns that I think should have protection on them. My thought was to encrypt these 2 columns. What happens if someone needs to know the actual values of a userid and password? Is encryption the right way to secure this data?
View 2 Replies View RelatedCREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert
OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key
OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key
What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005
Happy Coding...
Hi,
How can I encrypt / decrypt whole database (data, objects ... everything) in SQL Server 2005 Express Edition?
Quick solution from any champion of Databases would be highly entertained.
Waiting ..
Thanks a lot.
I want to store a function in SQL Server database that when called during insertion, encrypts a value and then stores encrypted text in the column.
Is there any built in Encrypting and Hashing mechanism in SQL 2005.