May 23, 2012 at 11:23 pm
HI ,
We have ssis job running on sql 2008.The job fails with below error.
Message
Executed as user: . Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 5:10:12 AM Error: 2012-05-24 05:10:13.12 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: 2012-05-24 05:10:14.18 Code: 0xC0202009 Source: DTS_X Connection manager "Microsoft OLE DB Provider for SQL Server"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available.
Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.".
End Error Error: 2012-05-24 05:10:14.18 Code: 0xC020801C Source: DTSTask_DTSDataPumpTask_1 OLE DB Destination [34]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server" failed with error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error Error: 2012-05-24 05:10:14.20 Code: 0xC0047017 Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline
Description: component "OLE DB Destination" (34) failed validation and returned error code 0xC020801C.
End Error Error: 2012-05-24 05:10:14.20 Code: 0xC004700C Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline
Description: One or more component failed validation. End Error Error: 2012-05-24 05:10:14.21 Code: 0xC0024107
Source: DTSTask_DTSDataPumpTask_1 Description: There were errors during task validation.
End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:10:12 AM Finished: 5:10:14 AM Elapsed: 2.11 seconds.
The package execution failed. The step failed..
Can any one help me please.
Thanks,
May 24, 2012 at 12:42 am
i have changed the
the Protection Level of Packages from "EncryptSensitiveWithPassword" to " Dont save sensitive"
and run the package.Bit still got the below errors.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2012-05-24 06:30:11.24 Code: 0xC020801C Source: DTSTask_DTSDataPumpTask_1 OLE DB Destination [34]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2012-05-24 06:30:11.24 Code: 0xC0047017 Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline
Description: component "OLE DB Destination" (34) failed validation and returned error code 0xC020801C. End Error Error: 2012-05-24 06:30:11.24 Code: 0xC004700C Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-05-24 06:30:11.24 Code: 0xC0024107 Source: DTSTask_DTSDataPumpTask_1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:30:09 AM F
May 24, 2012 at 1:36 am
Maybe, you configured the SQL Authentication Mode in SSIS package and you used sa user.
When the job runs and calls your package, it will use the sa user to connect to database server.
1/The first, you can run the package directly to check the user. If the package is run sucessfully, let check the job when you create.
+ Did you create the job on a server and the package connects to another?
+Permission of job?
2/ If it is failed, you have problem with sa user when trying to connect database server.
I suggest that if the package is in internal, you should use Window Authentication Mode when connecting to database server.
May 24, 2012 at 3:27 am
Since you now use DontSaveSensitive, the sa password is not stored anymore in the package. So you need to use a configuration to bring it in.
And let me add that you creating a huge security risk! You're using the sa account in SSIS packages? The sa account has full administration rights on the server, and by using it in SSIS, you let the password lying around for everyone to grab. (and yes, it is easy to crack the SSIS security).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 24, 2012 at 8:03 am
THanks you so much for help,
Wea are this Package to Load the data to Table from a Flat file.
I have changed the Authentication to windows mode and am using now " DontSaveSensitive " after that i have ran the job again and Got below errors.
1). Source: DTSTask_DTSDataPumpTask_1 OLE DB Destination [34] Description: The number of columns is incorrect. End Error Error: 2012-05-24 13:49:51.78 Code: 0xC0202025 Source: DTSTask_DTSDataPumpTask_1 OLE DB Destination [34]
2) .Description: Cannot create an OLE DB accessor. Verify that the column metadata is valid. End Error Error: 2012-05-24 13:49:51.78 Code: 0xC004701A Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline
3) .Description: component "OLE DB Destination" (34) failed the pre-execute phase and returned error code 0xC0202025. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:49:47 PM Finished: 1:49:51 PM Elapsed: 4.094 seconds. The package execution failed. The step failed.
can any one give an idea further to resolve the issue.
Thanks ...........
May 25, 2012 at 12:13 am
There seems to be something wrong with the OLE DB Destination metadata.
Does the package still work in BIDS? Is the destination table different on the server?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 25, 2012 at 12:51 am
THanks,
In Flat file we are having 6 colums and in table we have 7 columns and there is difference in columns between Flat file and Table.
when we try to open the Flat file we are not able see the data information ,and all are showing it encrpted formated.
In package for flat file ,the datatype for all the is coulms(6 columns) ------DTR-STR
In table ----datatpye for all 7 columns ---(int(Pk)--1,varchar--4,char--2)
Please suggest me do i need to check Flatfile once again as there is diff in column and columns names.
Thanks,
May 25, 2012 at 1:02 am
Normally it shouldn't be an issue if the number of columns between source and destination is different (you should remove the extra column however to improve performance) and if the names are different, you resolve this in the mapping pane of the OLE DB Destination.
But the flat file is encrypted? So you write the encrypted data straight into SQL Server?
Does the package work in BIDS?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 25, 2012 at 1:17 am
I have run the package in BIDS.it fails with below warning.
"
Warning1Validation warning. {8E1B1707-E8C6-4642-8069-D730ED0F410D} OLE DB Destination [34]: The external columns for component "OLE DB Destination" (34) are out of synchronization with the data source columns. The column "U_ID" needs to be added to the external columns. The column "U_NME" needs to be added to the external columns. The column "F_NME" needs to be added to the external columns. The column "L_NME" needs to be added to the external columns. The column "M_TYPE" needs to be added to the external columns. The column "U_TYPE" needs to be added to the external columns. The column "LIBRARY" needs to be added to the external columns. DTS_ (1).dtsx00
May 25, 2012 at 1:23 am
It seems the metadata has changed somehow.
Open the OLE DB Destination, check the mapping and click OK.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 25, 2012 at 2:44 am
Thanks so much for replying,
i have given mapping and got the below error,
Error at dtstask_dtsdatapumpTask_1[ole db destination[34]]:columns "col4" and "u_nme"
cannot convert between unicode and non-unicode string data types
Error at dtstask_dtsdatapumpTask_1[ole db destination[34]]:columns "col5" and "f_nme"
cannot convert between unicode and non-unicode string data types
Error at dtstask_dtsdatapumpTask_1[ole db destination[34]]:columns "col6" and "L_nme"
cannot convert between unicode and non-unicode string data types
Error at dtstask_dtsdatapumpTask_1[ssis.pipeline]:"component "ole db destioanton "(34) failed
validation and return validation status "VS_ISBROKEN"
error:ther were errors during task validation
plz help me ...
Many thanks
May 25, 2012 at 2:51 am
Either the source is unicode or the destination is.
But, you said the SSIS datatype is DT_STR (non-unicode) and the destination table is varchar (also non-unicode). Can you verify please?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 25, 2012 at 3:04 am
My appoliges,
Now I have checked in destination OLDB on package and the datatype is DT_WSTR for those 3 columns
the datatype which said before i.e varchar(), i have seen from Sql server-database-table-columns
Thanks,
May 25, 2012 at 3:07 am
OK, pull a data conversion component in the dataflow and convert the 3 columns to DT_STR.
Be aware that this creates copies for those columns, so you need to adjust the mappings in the OLE DB Destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 25, 2012 at 3:24 am
Thanks,
I am unable to change the datatype TO DT_STR
dataflow--advance options--EXTERNAL columns----try to change the datatype --click ok
but it is not chaNGING...
pLEASE ADVISE.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply