Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
trp1589
Starting Member
2 Posts |
 Posted - 2011-09-08 : 11:11:50
|
| I am upgrading from SQL2005 to SQL2008. I have a vb6 app interface that allows the user to select from a list of tasks which use to execute dts packages. I had to convert from dts to ssis and the vb6 interface would not execute the ssis pkg. I changed it so that the vb6 app would call a stored procedure and the sp would execute the ssis pkg. The ssis package I am currently working deletes data from a table for the current month and then takes a .csv file on the user's c:drive and loads it to a table in the sql database.I know the vb6 app is getting to the sp becuase I loaded the table with the month that should be delete and added a 'delete from' command right before the command to execute the ssis package and the data was deleted. When I execute directly from SQL2008 the sp gets a return code of 1 and the message: 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.However, the ssis pkg nor the .csv file are password protected.I also tried to create an SQL agent job to execute not just the ssis pkg but all the tasks that are done when my user selects her task. And everything but the execution of the ssis pkg is getting done.The code in the sp I was trying to use to execute the ssis pkg:DECLARE @SSISPackage VARCHAR(1000) DECLARE @cmd VARCHAR(8000) DECLARE @SERVERNAME VARCHAR(100)DECLARE @Result int SET @SSISPackage = '\GOAAL_GLBL1_IMPORT'SET @SERVERNAME = '"PVMX5278\MS2008_PROD"'set @cmd = 'dtexec /SQL ' + @SSISPackage + ' /SERVER ' + @servername + ' 'EXECUTE @Result = xp_cmdshell @cmd;Line of code in SQL agent job to execute ssis pkg is:EXEC xp_cmdshell 'dtsrun /s"pvmx5278\ms2008_prod,2750" /n"GOAAL_GLBL1_IMPORT" /E /L"E:\PROF_F$\8ss\Components\DTSLogs\GOAAL_GLBL1_IMPORT.log"';This lien of code is the same line use to execute another SSIS pkg. but that package doesn't do anything with a .csv file.Can anyone help? |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
|
|
trp1589
Starting Member
2 Posts |
Posted - 2011-09-09 : 16:53:36
|
| I got a solution. First I had the .csv file the SSIS pkg was using on my c:drive. This is how it worked for my vb6 app with SQL2005 & DTS pkgs. But for SQL2008 & SSIS pkgs I have to have it on the server itself. The SAs gave me a folder to place the .csv. My next issue was that the pkg was not recognizing the aliases setup for the server that was defined in the connection manger used by the OLE DB Destination of the data flow task. I also had to hard code the server and use windows auth instead of using the aliases with sql auth.for the flat file source of the data flow task. Next I was working on the pkg in BIDS on my machine. I had to be in BIDS on the server. Once all that was done and the pkg saved the pkg would work from BIDS (on the server not my machine), Integrated Services, SQL Agent, and by a stored procedure calling the pkg. |
 |
|
|
|
|
|
VB6 & SQL Agent not executing SSIS pkg