help reading xml files

[Glowworm27]

I have created a ssis package that will look in a folder for xml files and import them into sql 2005.

So I have a Foreach File Loop container. I set it to look at the specified folder, and save the fully qualified filename into a user variable called "filename"

Inside the foreach loop container, I put a dataflow task.
In the dataflow task, I have an xml Source data flow item, a data conversions item, and a ole db destination item.
Also in the foreach loop container I have a file system task to rename/move the file to another folder.

I have the xml source set up to use the variable user::filename to get the xml source file physical location, and have set up all the column mappings to the destination.

In order to get the columns mapped I manually put the full path name in the variable, and it works fine. the ssis package runs, reads the xml file and imports it into the database, and the file is renamed and moved to the archive folder.

However when I deply the package to the ssis server, and create a sql agent job to run the package and scheule the package to run at certain times.

When the time arrives, the job runs, but does nothing, and does not report any errors. The file still sits in the folder as if the job never ran.

Help please
G

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[SQLBill]

In the job, at the job step that runs the package, select Advanced and create an output file. Use that to see what is going on.

It could be that the job is running under different permissions than what you used to test and can't perform all the functions.

Also, confirm the file was actually there before the job ran. We have had that issue where clients didn't get the file in place on time.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Glowworm27]

the only thing in the log file is this;

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  4:51:00 PM
Error: 2010-03-25 16:51:00.80
   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
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  4:51:00 PM
Finished: 4:51:46 PM
Elapsed:  45.531 seconds

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[SQLBill]

Aha!!! That's an easy one. Only because I'm familiar with it.

Here's what happened. You created and tested the SSIS package on Computer A (maybe your desktop?). Then you saved it keeping the Protection level as "Encrypt sensitive data with user key". However, you saved it to Computer B. SQL Server uses a hash of your login and the computer id (mac address or something) to do encryption. So the encryption on Computer B won't be the same.

When you save the SSIS package, you have a choice of different encryption methods. I suggest changing it to "Rely on server storage and roles for access control".

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875