Hello everyone,
I'm not at all comfortable with SSIS so please forgive me if I overload you all with information here:
I need to create a data table using SSIS which does not delete the previous days data. So far all the data tables we use to write reports in Visual Studio are constructed in SSIS as follows.
1 - Excecute SQL Task - DELETE FROM STOCK
2 - Data Flow Task
3 - Data Reader Source - SELECT * FROM ODBCDATASOURCE
4 - OLE DB Destination (Creates table STOCK)
The data tables which are created this way are stored in a data warehouse and scheduled to refresh once a day, which means that any data from yesterday is lost when the updates run. So, I tried to create a table which never has its previous days' data deleted by using just the last three steps above - and it worked great in Visual Studio, no problem at all. However, when I added this SSIS Package to the Update Job in SQL Server Management Studio, the job totally rejected the packed with the message: "The command line parameters are invalid. The step failed".
I thought I could work around this problem by asking the job step to excecute a simple SQL query to insert the data from table1 into table2 (and would thus negate the need for a SSIS Packege at all), but it threw me a curve ball with some message about not being able to use proxy accounts to run T-SQL Scripts.
If anyone knows how to create a SSIS package in which the data never expires please could you impart some wisdom my way. I only need to do this once for a specific report.
Please, when answering, bear in mind that I'm a simple fellow with little understanding of the inner workings of SQL Server and its various components, so please use short sentences and simple words.
Thanks in advance,
Chris
Strange. Looks like a problem in SQL Agent.
I suggest you first attempt to execute the package using dtexec. Once you have got that working we can talk about transferring the command-line to SQL Agent.
To make it easy to build the command-line for use with dtexec, Microsoft provide a tool called dtexecui that builds the command-line for you. Just type "dtexecui" at the command prompt.
If none of that makes sense, just holler.
-Jamie
|||
Jamie,
First of all, many thanks for your reply, your help is much appreciated.
I did what you suggested and tried to run the package via dtexec, but after a couple of validation phases it gave this error message: Error: The product level is insufficient for component "DataReader Source" (1).
Any suggestions on what I've not done correctly would be appreciated.
Many thanks,
Chris
|||A common issue, normaly caused by not having a full SSIS installation on that machine.
Michael Entin's WebLog : Why do I get "product level is insufficient..." error when I run my SSIS package?
(http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx)
Product level is insufficient
(http://msdn2.microsoft.com/en-us/library/aa337371.aspx)
Darren,
Thank you, I have just run the package via remote desktop to the server and it ran successfully first time.
How do I now get the package to run as part of the SQL Server Agent Job? The package step still fails whether initiated or scheduled and whether run locally or from the server.
Thanks,
Chris
|||C.P.Hardcastle wrote:
Darren,
Thank you, I have just run the package via remote desktop to the server and it ran successfully first time.
How did you execute it? From BIDS or using dtexec?
How do I now get the package to run as part of the SQL Server Agent Job? The package step still fails whether initiated or scheduled and whether run locally or from the server.
Thanks,
Chris
What is the error message?
-Jamie
|||
Jamie,
I can execute the package successfully from either BIDS (VS2005) on my local machine, or from dtexec if I remote desktop to the server and run it from there.
The package consistently fails when run in SQL Server Agent within SQL Server Management Studio - I can be running SSMS locally or on the server and it makes no difference. When I open the job and view the job history the step that runs this package fails with the error: The command line parameters are invalid. The step failed.
All other steps (three other steps) in the job complete successfully, this is the only step that fails (I've swapped the order in which they run and this one always fails while the other three always complete successfully). I have checked and double checked that the job and steps are set up properly and I even isolated this one package into a seperate Job all of its own and it still fails.
I have even gone as far as to delete the package, recreate the package, save copy to the server etc and I always get the same results when I try to run the SS Agent Job.
Any suggestions are very welcome.
Many thanks for your help so far and for your time and energy with this,
Chris
|||Jamie,
I can execute the package successfully from either BIDS (VS2005) on my local machine, or from dtexec if I remote desktop to the server and run it from there.
The package consistently fails when run in SQL Server Agent within SQL Server Management Studio - I can be running SSMS locally or on the server and it makes no difference. When I open the job and view the job history the step that runs this package fails with the error: The command line parameters are invalid. The step failed.
All other steps (three other steps) in the job complete successfully, this is the only step that fails (I've swapped the order in which they run and this one always fails while the other three always complete successfully). I have checked and double checked that the job and steps are set up properly and I even isolated this one package into a seperate Job all of its own and it still fails.
I have even gone as far as to delete the package, recreate the package, save copy to the server etc and I always get the same results when I try to run the SS Agent Job.
Any suggestions are very welcome.
Many thanks for your help so far and for your time and energy with this,
Chris
Did you change to use the command-line subsystem as per my earlier suggestion? If so, what is the command-line that you are using?
-Jamie
|||
Jamie,
I must apologise, I really appreciate your help and I really don't want to exhaust your patience, but please understand your dealing with ineptitude of idiotic proportions here. 
Here's exactly what I did. At the command prompt I typed "dtexecui" as you suggested. This opened a new window called Execute Package Utility. I then entered the relevant information about which package to execute and it then went through a few validation phases and the Package execution Process Window returned a message that "component "OLE DB Destination" (13)" wrote 248 rows.
I once again apologise, but I'm not exactly sure what you mean by what is the command-line I'm using, but the only thing that I think you mean is this:
/DTS "/MSDB/C002_UNIDATA_STOCKPE_RD" /SERVER LYSRVBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
This command line differs slightly to the one used by the Agent Job Step, which is:
/DTS "/MSDB/C002_UNIDATA_STOCKPE_RD" /SERVER LYSRVBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
..so I copied the command line from the dtexec utility into the step for this package but it still fails.
I hope I have given you the right information here.
Thanks again,
Chris
|||Jamie,
I must apologise, I really appreciate your help and I really don't want to exhaust your patience, but please understand your dealing with ineptitude of idiotic proportions here.
Here's exactly what I did. At the command prompt I typed "dtexecui" as you suggested. This opened a new window called Execute Package Utility. I then entered the relevant information about which package to execute and it then went through a few validation phases and the Package execution Process Window returned a message that "component "OLE DB Destination" (13)" wrote 248 rows.
I once again apologise, but I'm not exactly sure what you mean by what is the command-line I'm using, but the only thing that I think you mean is this:
/DTS "/MSDB/C002_UNIDATA_STOCKPE_RD" /SERVER LYSRVBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
This command line differs slightly to the one used by the Agent Job Step, which is:
/DTS "/MSDB/C002_UNIDATA_STOCKPE_RD" /SERVER LYSRVBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
..so I copied the command line from the dtexec utility into the step for this package but it still fails.
I hope I have given you the right information here.
Thanks again,
Chris
Chris,
I think you do yourself a disservice. You've done (almost) exactly what I suggested so you're not as inept as you may think.
What you need to do first is take the command line from dtexecui, and run it using dtexec. See if that works or fails.
i.e., Something like this:
dtexec /DTS "/MSDB/C002_UNIDATA_STOCKPE_RD" /SERVER LYSRVBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
-Jamie
|||Jamie,Thanks once again for your reply and for your encouraging comments.
I'm out of the office now until Monday so I won't have the chance to try your suggestion for a few days.
I will let you know how it goes as soon as I can.
Thanks again, your help is very much appreciated.
All the best,
Chris|||Jamie,
I couldn't resist a VPN to the server from home to try your suggestion.
Unfortunately, the job still fails.
Chris|||
Jamie, I couldn't resist a VPN to the server from home to try your suggestion.
Unfortunately, the job still fails.
Chris
When using dtexec or from SQL Agent? it does not make sense that it would work from dtexecui and not dtexec.
Please provide more info.
-Jamie
|||
When using dtexec or from SQL Agent? it does not make sense that it would work from dtexecui and not dtexec.
Please provide more info.
-Jamie
Especially since dtexecui is a wrapper to dtexec.|||Jamie,
I'll give you as much information as I can.
As I said before, all our SSIS packages are createde in the same way with the same four steps: Execute SQL Task > Data Flow Task > Data Reader Source > OLE DB Destination.
With the exception that this problem package omits the Execute SQL Task stage it is identically set up to all our other packages, which work flawlessly.
After I created the package in Visual Studio I executed the package and everything turned green and all messages told me that the execution was a success. Sure enough if I viewed the table from server explorer all the data I expected to see was present.
Next I changed the protection level to Server Storage and saved a copy of the package to the server using the following:
Package Location: SSIS Package Store
Server: LYSRVBI
Windows Authentication
Package Path: /MSDB/C002_UNIDATA_STOCKPE_RD
Protectin Level: Rely on server storage and rules for access control
Next I created a new SSA job in SQL Server Management Studio and created a new step using the following:
Job Type: SSIS Package
Package Source: SSIS Package Store
Server: LYSRVBI
Use Windows Authentication
Package Path /MSDB/C002_UNIDATA_STOCKPE_RD
In the advanced section I made sure that close reporting success and close reporting failure were appropriately set depending on the execution outcome.
When I tried to run this job it consistently failed so I then followed your advice and ran the package via dtexecui after connecting to the server via remote desktop - and it worked. I copied the command line from dtexecui and edited the command line within the job to the same adding "dtexec" to the front of the command as you suggested- I left evertything else in the job/step exactly as above. When I started the job it immediately failed.
I don't think I've left anything out and I'm really baffled why it won't run as it's identical to countless other packages we run everyday with the only exception being that it omits the Execute SQL Task step and the "DELETE FROM <TABLENAME>" string within that task.
Let me know if I've made any fundamental errors please?
Thanks,
Chris
No comments:
Post a Comment