FlatFile connection and security

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

Error: 2005-06-24 12:35:03.33
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This error just started when we tried to point to a file on a network server instead of a file local to the machine running the package. The other person definitely has access rights to that network server, but the package fails anyway.

One possible solution might be to specify a username/password for the file, but I don't see how to include that in the connection string. Alternatively, this may be a more general security issue with the whole package. I first turned to BOL for some explanations and answers, but wasn't able to find anything specific to my problem.

Any help or suggestions will be greatly appreciated. Thanks!
Phil
This is because the default protection level is to encrypt with a user key. That means only a user on his/her machine can open the package.

You probably want to use Package Password here instead.|||

I am getting a similar error on a DTS package created by me and migrated by me and executed by me via Dtexec. Shouldn't ssql 2000 ownership carry over to 2005? The package is not a flat file. And, by the way, where does the "DTS Property" come in?

thanks

Ted

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:24:56 AM
Error: 2006-02-08 09:24:56.50
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
Error: 2006-02-08 09:24:56.51
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

|||

SSIS is a completely new product so I don't think it follows that DTS behaviour should make it into SSIS. Furthermore, there's been a very high profile tightening up of security within Microsoft products and the behaviour you're seeing here is probably as a result of that.

The reason you see references to "DTS" in various places is for legacy reasons. Alot of the product had already been built before the name change came about.

-Jamie

sql

FlatFile connection and security

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

Error: 2005-06-24 12:35:03.33
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This error just started when we tried to point to a file on a network server instead of a file local to the machine running the package. The other person definitely has access rights to that network server, but the package fails anyway.

One possible solution might be to specify a username/password for the file, but I don't see how to include that in the connection string. Alternatively, this may be a more general security issue with the whole package. I first turned to BOL for some explanations and answers, but wasn't able to find anything specific to my problem.

Any help or suggestions will be greatly appreciated. Thanks!
PhilThis is because the default protection level is to encrypt with a user key. That means only a user on his/her machine can open the package.

You probably want to use Package Password here instead.|||

I am getting a similar error on a DTS package created by me and migrated by me and executed by me via Dtexec. Shouldn't ssql 2000 ownership carry over to 2005? The package is not a flat file. And, by the way, where does the "DTS Property" come in?

thanks

Ted

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:24:56 AM
Error: 2006-02-08 09:24:56.50
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
Error: 2006-02-08 09:24:56.51
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

|||

SSIS is a completely new product so I don't think it follows that DTS behaviour should make it into SSIS. Furthermore, there's been a very high profile tightening up of security within Microsoft products and the behaviour you're seeing here is probably as a result of that.

The reason you see references to "DTS" in various places is for legacy reasons. Alot of the product had already been built before the name change came about.

-Jamie

Flat File Source Problem...

I have a weird thing happening.
I have an .csv file. When I try to load it into a table, I can do it easily in DTS 2000. But when I am trying to do it in SSIS 2005 with exactly the same settings (like Text qualifier, row delimiter etc.), I am getting an error: "The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly." I looked at the file and it looks complete to me.

What could be the problem ?

P.S. DTS 2000 is on 32-bit Windows, and SSIS 2005 is on 64-bit Windows 2003. Could that we a problem ?

Victor_V wrote:

I have a weird thing happening.
I have an .csv file. When I try to load it into a table, I can do it easily in DTS 2000. But when I am trying to do it in SSIS 2005 with exactly the same settings (like Text qualifier, row delimiter etc.), I am getting an error: "The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly." I looked at the file and it looks complete to me.

What could be the problem ?

P.S. DTS 2000 is on 32-bit Windows, and SSIS 2005 is on 64-bit Windows 2003. Could that we a problem ?

I suppose it could be a problem, but DTS has historically been more forgiving when processing files. Are you sure you don't have a carriage/line-feed as the last line in the file? Sometimes this happens, and SSIS will think that is an incomplete row (It is, technically).|||It is true that the parsing behavior in SSIS is very different from DTS 2000. DTS was very forgiving as Phil mentioned, and there could be an extra carriage/line-feed which is causing this. Also DTS used to process files even if not all columns were populated in a row. However, SSIS would bail out on such files.|||

How do I check for this extra carriage return character ? It's not really visible. What editor would I use ?

Also, I usually get those "pipe" delimited files from Excel files, saving them as .CSV files.

Thanks,

Victor.

|||

Victor_V wrote:

How do I check for this extra carriage return character ? It's not really visible. What editor would I use ?

Also, I usually get those "pipe" delimited files from Excel files, saving them as .CSV files.

Thanks,

Victor.

Go to the last line of the file. Does the cursor sit at the end of a row of data, or on it's own line? That's one way.|||It sits on it's own line (at the beginning of a new line)..|||

Victor_V wrote:

It sits on it's own line (at the beginning of a new line)..

Hit backspace on that line and then save the file. Try running it again. Or try a ragged-right format.|||

Actually, I just opened this file in UltraEdit in HEX view, and the last 2 characters in the file are "OD OA", which is the end of the line, just like at the end of every row...

That's very strange...

|||

Victor_V wrote:

Actually, I just opened this file in UltraEdit in HEX view, and the last 2 characters in the file are "OD OA", which is the end of the line, just like at the end of every row...

That's very strange...

That's not strange, that's the point. SSIS sees that as a row, but there aren't any columns in it.|||No, what strange is that those 2 characters ARE the last characters in the row (and each row), and there is nothing else there. That's how it looks like in UltraEdit HEX editor. But when I open the same file in Notepad and hit Ctrl+End to go to the end of the file, that's when the cursor goes to the new row, instead of stopping at the end of the last row.|||I want to be sure on a few things...

The format is: Delimited
The text qualifier is: " (if needed)
The Header row delimiter is: {CR}{LF}

Under columns:

The Row delimiter is: {CR}LF}
The Column delimiter is: Comma {,}|||

The format is: Delimited
The text qualifier is: <none>

The Header row delimiter is: {CR}{LF}

Under columns:

The Row delimiter is: {CR}LF}
The Column delimiter is: Comma {|}

|||Phil ? Anyone ? Any thoughts ?|||

Victor_V wrote:

Phil ? Anyone ? Any thoughts ?

Can you post a sample line from the csv file?|||

Here are the last 5 rows in my .csv file:

12|Community Network Services, Inc|MI|C|T1020-Low|Personal care services, per diem |Blank||Per Diem||49|13461|13461|359677.92|26.72|26.72||0|13,461|13,461|0|615013.79|99.60%||0.00%|433.3|0.07%|2031.64|0.33%|617,479|0.00|45.69|0.00|0.03|0.15|45.87|24.96|20.63|33.51|22.81|29.7|same method from prior year
12|Community Network Services, Inc|MI|C|T1020-Moderate|Personal care services, per diem |TF||Per Diem||29|7391|7391|619809.26|83.86|83.86||0|7,391|7,391|0|490943.23|99.63%||0.00%|265.36|0.05%|1554.76|0.32%|492,763|0.00|66.42|0.00|0.04|0.21|66.67|68.16|53.58|68.53|54.88|67.43|rates reflect similar to prior years
12|Community Network Services, Inc|MI|C|T2003|Non Emergency Transportation|||Trip||2|148|148||0.00|0.00||0|148|148|0|544.16|100.00%||0.00%||0.00%||0.00%|544|0.00|3.68|0.00|0.00|0.00|3.68|0|7.99|0|7.92|0|
12|Community Network Services, Inc|MI||GF Pharmacy|GF Pharmacy||||||||||||||||||||||||753,848||||||||||||
12|Community Network Services, Inc|MI||H0038|Drop-in Center||||||||||||||||||||||||447744.18||||||||||||

Thanks, Phil.

Fishing the DTS Knowledge Pool

I am having a puzzling problem with a DTS package in SQL 2000 that uses a combination of "Execute Process Tasks" and "ActiveX Script Tasks." The issue occurs with one of (the second) the ActiveX Script Task. The script invokes a COM object that was written in C#. This COM object connects to the database and writes a record set and then calls a stored procedure.
When I run this step individually, everything works fine. When I run it as part of the package, the record set is being written but the stored procedure is not invoked (SQL Profile confirms this).

I haven't really begun a serious attempt to troubleshoot the problem, though I have played around with the Transactions and OLE DB properties of the DTS Package.

Any suggestions as to why the behavior might be different, or some straightforward tips on troubleshooting would be appreciated. I have access to the COM component source.
Try the *real* DTS knowledge pool: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

This forum is limited to SSIS.

First Timer Having an Insert Problem

Ok...I am stumped

I am currently writing a custom DTS package using an ActiveX script. This is my first time writing an ActiveX script and using the VBscripting language.

What I need to do is take data from three existing MS SQL Server 2k tables and import it into a single new table after serveral numerical data manipulations.

The specific problem I am having is that when I insert my recorsets into the new table several of the records are out of their sequential order. I am confused because if I cut down on the amount of data I insert, either in the number of rows and/or number of columns, I have no problem with my insert, but when I insert all the data I need things get out of order and swap places. I am inserting using what I think is called a connection/execute command with TSQL commands, and from what I have read this is the most efficient way to go about it.

I have approximately 4800 rows to insert with 6 columns, but my program seems to error with I try to insert in excess of 4100 rows.

Does anyone have any ideas? I was told that it might have something to do with a buffer, but I have not been able to find any helpful documentation. I have included the code for my insert loop.

Thank you in Advance!

-TRoche

do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value

GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value

GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value

'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "

DestCon.Execute DestCmd

GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext

LoopDo you have a primary key on GPSIMPORT? If not, then your data structure is known as a "heap" and SQL server makes no guarantees about the order in which data is stored, or even the order in which it is retrieved in consecutive statements.

It is not a good idea to rely on the order in which data is inserted to be the order in which it is kept or retrieved. Define a primary key for your data.

blindman|||Thanks a lot Blindman!!

This seems to have worked!! I would have never found that solution.

thanks again,

TRoche|||"First timer having an insert problem."?

Maybe that's why you always remember your first....

First Timer having an INSERT Problem

Ok I am stumped....

I am currently writing a custom DTS task through the DTS designer and an ActiveX script. The goal of my Script is to access 3 tables which already exist in the DB, extract and numerically manipulate data from each table, and then insert this data into a new table.

This is my first time writing an ActiveX script and my first time using the VB scripting language, so most of my script is based on examples I could find.

To be more exact about my problem here is the description. I created six recordsets from the data I extracted from the 3 tables. When I attempt to insert all the data into my new table some of the data inserts in the wrong order. Three of my columns are time columns, so it is easy to see where the data falls out of its intended sequential order. It is also easy to see that this happens at the exact same time value, every time I run the code.

I am confused because if I write a for loop and insert less data either through fewer columns or fewer rows, my code seems to work perfectly. I have approximately 4800 rows total to insert, but my code seems to mess up if I try and insert in excess of 4100 rows. Someone suggested that I may be exceeding my allowed buffer, but I don't really know what this means or how to correct it.

Becasue my data inserts correctly if I only insert, say 2 columns, but all the rows, I think that the recordsets are being creatred correctly, and that the fault lies in my insert loop.

I have included the script for my insert loop and an example of how the data looks when it is in error.

I am very grateful for any help you might be able to provide and please let me thank you in advance for you time!

-TRoche

do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value

GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value

GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value

'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "

DestCon.Execute DestCmd

GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext

Loop

Time(x) GPSx Time(y) GPS(y)
58.9447 383421.96 58.94497 1213470.912 58.94526 488
58.99134 383421.959 58.99162 1213470.912 58.99191 489
59.04329 383421.957 59.04356 1213470.912 59.04385 490
59.0951 383421.956 59.09538 1213470.912 59.09566 490
59.14204 383421.955 59.14231 1213470.912 59.1426 490
221.19337 383447.07 221.19364 1213349.901 221.19393 479
221.24045 383447.069 221.24072 1213349.895 221.241 479
221.29253 383447.068 221.2928 1213349.889 221.29308 479
221.34434 383447.067 221.34461 1213349.883 221.3449 478'************************************************* **********
' Visual Basic ActiveX Script
'************************************************* **********

Function Main()

dim ConnSQL1 ' SQL Server connection
dim RSSQL ' SQL Server recordset
dim strSQL ' SQL String
dim rc

' SET DATA HANDLING OBJECTS
set ConnSQL1 = CreateObject("ADODB.Connection")
set RSSQL = CreateObject("ADODB.Recordset")

'OPEN DATA CONNECTION
ConnSQL1.Open = "Provider=SQLOLEDB;Data Source=server;Initial Catalog=database;UID=username;Password=asdf"

'create a select statement and put into temp table in the
'format that you are after from the three tables
'I assume you can do that
'Once you have all those records, then just insert them
'from the temp table

strSQL = "SELECT * into #temp from table " & _
"insert into newtable(f1, f2, f3, f4)" & _
"select f1, f2, f3, f4 from #temp"

RSSQL.Open strSQL, ConnSQL1
ConnSQL1.execute strSQL
RSSQL.close
Main = rc
End Function

Firing DTS through Window Based Forms in VB.net

Here is what I've got so far...for some reason it's not firing off:

<code>

PrivateSub Button1_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles Button1.Click

Dim dtsp2AsNew DTS.Package

dtsp2.LoadFromSQLServer("jfgp34", "sa", "@.jfgp#1", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "Test Northwind", "")

dtsp2.Execute()

EndSub

</code>

Any one with ideas helps out alot. Thanks in advance everyone.

RB

Check out the DTS Cookbook for .NET,http://www.sqldev.net/dts/DotNETCookBook.htm
|||This approach is more SQL server centric, and less reliant onVB.NET/C#.NET. It's basically a technique for triggering a DTS packagefrom a stored procedure, which can run from your .NET program withoutmuch effort.
http://www.mssqlcity.com/FAQ/Devel/DTSviaQA.htm
With that said, I've only used a similar technique to the one referenced in the previous post.
Jason
|||

Jason,

Thanks for the site. That looks like it is going to do the trick I'll let you know if I need any thing else.

Thanks again.

RB

Firing already made DTS Package -- from vb.net

Someone please help me with this.

I'm trying to fire off an already created DTS package. This package is stored within SQL Server's -- underneith the Data Transformation Services / Local Packages section.

HOW CAN I FIRE THIS OFF FROM A VB .NET APPLICATION

I'm familiar with strored procedures and using them in vb.net so if somone could lead me down that road I would be very much appriciated.

Thanks in advance everyone,

RB

Try this url you may find your solution. Hope this helps.

http://www.sqldts.com

|||

I have this in my stored proc already:

Here is what I am trying to run within my stored proc:

dtsrun /F'CSI_DTSPackages/GTProPaymentExtract.dts'/N'GTProPaymentExtract'/M''

PLEASE TELL ME WHAT I'M DOING WRONG!!!

Thanks in advance everyone.

RB

|||

I gave you Darren Green's site because if you can do it in DTS he may have done it. I have found another link, it may solve your problem. Hope this helps.

http://www.vbdotnetheaven.com/Code/Jun2003/2070.asp

|||

here a stored procedure I got from the DTS site. As this fires off a DTS package on the server your path will have to be a UNC path. In my case the path is source to a file I am reading data from.

CREATE PROCEDURE spExecuteDTSPKG
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ConnName varchar(25) = '',
@.XLSPath varchar(255) = '', -- Path to Source File
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 1, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS
SET NOCOUNT ON

DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000), @.Connection varchar(255)
Select @.Connection = 'Connections.Item(' + @.ConnName + ').DataSource'
Select @.ret = 0

-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT

SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

EXEC @.hr = sp_OASetProperty @.oPKG, @.Connection, @.XLSPath
If @.hr <> 0
BEGIN
PRINT '*** OASetProperty for Connection Failed***'
EXEC sp_OAGetErrorInfo @.oPkg
RETURN
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG

RETURN @.ret
GO

firing a dts package's execution

Hi all,
I am using Sql Server 2000. I need to create a very simple app which when
clicked would fire the execution of a dts package on the sql server. The app
itself would sit on the client machine. Is this possible? If so, how?
Thanks in advance.someone wrote:
> Hi all,
> I am using Sql Server 2000. I need to create a very simple app which wh
en
> clicked would fire the execution of a dts package on the sql server. The a
pp
> itself would sit on the client machine. Is this possible? If so, how?
I tried this a month back. There are a number of solutions but I found
that there were alot of security/permissions issues that prevented me
from triggering a DTS package from an application. The most promising
solution I thought was the triggering of a job from within a stored
procedure using sp_start_job. I finally got the permissions worked out
to execute the job but then the job wouldn't execute the DTS.
Ultimately I gave up and went to a scheduled job solution that ran every
10 minutes. I'm sure it's possible to trigger a DTS but my
organization's security policies (which are there for a reason) made
that difficult to accomplish.
Anyway, google for sp_start_job, you should get alot of information that
will help you out.
gorf|||someone wrote:
> Hi all,
> I am using Sql Server 2000. I need to create a very simple app which wh
en
> clicked would fire the execution of a dts package on the sql server. The a
pp
> itself would sit on the client machine. Is this possible? If so, how?
> Thanks in advance.
>
Some combination of xp_cmdshell and DTSRUN would probably do it. Both
are documented in BOL.|||someone wrote:
> Hi all,
> I am using Sql Server 2000. I need to create a very simple app which wh
en
> clicked would fire the execution of a dts package on the sql server. The a
pp
> itself would sit on the client machine. Is this possible? If so, how?
> Thanks in advance.
I've just added something to my VB.Net app something that should also
work from VB 6, and anything that can use COM. Add a reference to the
Microsoft DTSPackage Object Library. Then it's a couple of lines like:
Dim pkg As New DTS.Package2
pkg.LoadFromSQLServer("SERVERNAME", "", "",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, "",
"", "", "PACKAGENAME", "")
AddHandler pkg.OnError, AddressOf RecordExtractError
pkg.Execute()
If you need more/something else, it might help if you tell us what the
app is being developed in (incidentally I think the above can also be
written as a script file)