July 1, 2009 at 5:12 pm
I'm getting the following error when i run a SSIS package job.
Executed as user: PCIBO1S\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Argument "Data Source" for option "connection" is not valid. The command line parameters are invalid
Does anyone know how to fix this?
Thanks
July 1, 2009 at 7:02 pm
We are going to need the command line that is being called to see the options.
At first blush I would say the command line calling dtexex is incorrectly built, but exactly how is unclear given we don't have enough information.
CEWII
July 1, 2009 at 7:03 pm
As a side note, I just realized that I think you can use DTEXECUI to build a valid command line, that you could use as an example..
CEWII
July 2, 2009 at 10:17 am
Is this the correct command line?
/FILE "C:\Documents and Settings\bschaeffer\My Documents\Visual Studio 2008\Projects\RatingsCompareBetweenCMSOasis\RatingsCompareBetweenCMSOasis\SumRatings.dtsx" /CONNECTION "CMSReno.DPSQL";"\"uid=DPSQL;Dsn=CMSReno;system=TARGET;dbq=cmsdta70 cmsdta70;dftpkglib=QGPL;languageid=ENU;pkg=QGPL/DEFAULT(IBM),2,0,1,0,512;\"" /CONNECTION "PCIDI1S.di_bts";"\"Data Source=PCIDI1S;Initial Catalog=di_bts;Provider=SQLOLEDB.1;Integrated Security=SSPI;Application Name=SSIS-Package-{9A034DEC-F3F0-465E-8BA9-D97521746871}PCIDI1S.di_bts;Auto Translate=False;\"" /CONNECTION "Win Oasis";"\"Data Source=RCCDS1C;User ID=sa;Initial Catalog=WinOasis;Provider=SQLNCLI10.1;\"" /CHECKPOINTING OFF /REPORTING E
July 2, 2009 at 12:04 pm
I can't see anything that is obviously wrong..
But given the error message I would look specifically at the connection: PCIDI1S and "Win Oasis"
Also you are using two different connection types for those, are they both connecting to a SQL server.. One of the providers is "Provider=SQLOLEDB.1" and the other is "Provider=SQLNCLI10.1", what version of the native client driver do you have installed.. Something about that provider name strikes me as wrong..
CEWII
July 6, 2009 at 2:39 pm
I got ride of the first error deleting data connections i didn't need.
But now i get another error. I'm pretty sure it has to do with the password for the remaining data connections disappearing after i save it and run it through SSMS.
Any ideas whey the password for the data connections would go blank?
Executed as user: PCIBO1S\SYSTEM. ...e Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:00:00 AM Error: 2009-07-06 05:00:00.73 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 Error: 2009-07-06 05:00:00.73 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 Error: 2009-07-06 05:00:00.75 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 Error: 2009-07-06 05:00:09.03 Code: 0xC0047062 Source: CMS DF ADO NET Source [1] Description: System.Data.Odbc.OdbcException: ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user DPSQL on server TARGET is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 10.1.69.5 ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user DPSQL on server TARGET is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 10.1.69.5 at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction) End Error Error: 2009-07-06 05:00:09.03 Code: 0xC0047017 Source: CMS DF SSIS.Pipeline Description: component "ADO NET Source" (1) failed validation and returned error code 0x80131937. End Error Error: 2009-07-06 05:00:09.03 Code: 0xC004700C Source: CMS DF SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2009-07-06 05:00:09.03 Code: 0xC0024107 Source: CMS DF Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:00:00 AM Finished: 5:00:09 AM Elapsed: 8.344 seconds. The package execution failed. The step failed.
July 6, 2009 at 8:26 pm
If you saved the package and opened it as a different user then it cannot remember the password because it would be encrypted just for that user.. Try to use connections that do not require passwords, it will be substantially easier..
But since you are using an ODBC connection to some IBM end-point I'm not sure you have a choice.. Be sure that you edit the package as the same user every time. If you can't do that then use package configurations where you should be able to have the connection information unencrypted, or you can change the mode, but if you do you will have to enter an encryption key every time you open it..
CEWII
July 7, 2009 at 11:57 am
Ok. Thanks for your help. I found where to make that change and it's working now.
July 7, 2009 at 12:22 pm
Right click on control flow and select Package Configurations. From the Package Configurations Organzier window click add. I prefer to use the XML file but if you want to use environmental variables you need to get it added to the machine, the easiest way is Settings|Control Panel|System|Advanced Tab|Environmental Variables and then add it to the system variables OR login as the user that SSIS runs as and add it to that users environmental variables. You might see why I like to use XML config files.. The entire connection string needs to be in the variable. Once the variable is there you might have to logout and back in for it to become active. It wiould then be in the dropdown, select it, click next. You can now select your connection, you do this by expanding connection managers and finding the connection, expand its properties and then click on ConnectionString, click Next, give it a name. then click Finish, your package will now read the connection string for that connection from the environmental variable.. I do still however recommend using XML config files, but thats me..
CEWII
March 30, 2011 at 8:12 am
Hello
I'm developing a SSIS package that exports data from SQL Server to Excel. The package works fine in test environment and i scheduled it in the same as a SQL Job. It works fine.
Then the moved it to Live then the package works fine. When the schedule the job in test environment (with the package poitning to live).
I'm not sure where i went wrong. I had created the package and scheduled with the same user account.
Can anyone help me in this?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply