Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Connection failing - SQL Authentication

Answered SQL Connection failing - SQL Authentication

  • Monday, February 04, 2013 12:01 AM
     
     
    Sign In to Vote
    0

    Hi all,

    I have a connection manager with provider - Native OLEDB\SQL Server Native Client 11.0.

    The following properties on my connection manager are evaluated as expressions:

    IntitialCatalog
    Password
    ServerName
    UserName

    I have used parameters to populate these values.

    When I deploy and execute from the SSIS catalog with new parameters - the connection fails.

    When I view the execution report, the reported connection string does not include the password. Is this a security feature? Or is my password not being used?

    Thanks.

     

All Replies

  • Monday, February 04, 2013 12:52 AM
    Moderator
     
     
    Sign In to Vote
    0

    Do you mean the connection string column of the SSIS Catalog execution report (SSIS 2012)?

    If I recall correctly it does not expose the password by design.

    Any ways, what is your error?

    I guess you set the expression incorrectly.

    May I suggest you use just one variable say User::ConnectionString for the purpose of setting the ConnectionString of the OLEDB?

    Arthur My Blog

     
  • Monday, February 04, 2013 3:12 AM
     
     
    Sign In to Vote
    0

    Thanks Arthur,

    I have implemented your suggested single variable for the connection manager - however, I still have issues (apologies for not posting error message in original post) - it appears it is a SQL Server version issue..

    The error I am getting is:

    Error      2              Validation error. Data Flow Task OLE DB Source [9]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Syntax error, permission violation, or other nonspecific error".                  CreateOrdersCNP732P.dtsx        0

    My local machine is running SQL Server 2012 and 2008.

    If I point my source to 2012 - it works ok, if I point to SQL 2008 or 2008R2 (on another machine) I receive the above error.

    My OLE DB Source is a SQL Command (multi-statement) that returns a single column string (the single column is an XML doc converted to a varchar).

    My multi-statement query returns OK when ran from SSMS on any SQL Server version.

    Also, not sure if this is important but I've mapped the same variable two different parameters in my query (i.e. multi-statement that uses the same parameter).

     
  • Monday, February 04, 2013 3:36 AM
     
     
    Sign In to Vote
    0

    Hi,

         I hope ,you did this settings - EvaluateAsExpression = True,property of variable

        also try setting DelayValidation = True.

        check the protection level of package - http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/11/10/ssis-error-code-dts-e-oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting.aspx

    Thanks & Regards, sathya




     
  • Monday, February 04, 2013 4:13 AM
     
      Has Code
    Sign In to Vote
    0

    SathyanarrayananS,

    Yes EvaluateAsExpression was set to true and DelayValidation doesn't help.

    I have now tried removing the expressions from connection string altogether - this was not the issue. When I point to my SQL2012 server it runs, when I point to 2008R2 or 2008 - it fails with the above error.

    My SQL Command looks like this:

    DECLARE @newid_str varchar(200) = cast(newid() as varchar(200))
DECLARE @fileDate_str varchar(10)
DECLARE @numOrderLines int = (select count(*) from F330_PO_PRODUCT 
	join F320_PO_STORE on R330_R320_RECORD_ID = R320_RECORD_ID
	join F310_PO_HEADER on R320_R310_RECORD_ID = R310_RECORD_ID
	where R310_RECORD_ID = ?)
DECLARE @numOrderLines_str varchar(10)


set @fileDate_str = convert(varchar(10), getdate(), 103)


set @numOrderLines_str = cast(@numOrderLines as nvarchar(10))



DECLARE @DocumentHeader xml = N'<DocumentHeader>
	<DocumentType>OrderImport</DocumentType>
	<HostApplication>CCSQL</HostApplication>
	<HostVersion>1</HostVersion>
	<FileName>x1.xml</FileName>
	<DateCreated>' + @fileDate_str + N'</DateCreated>
	<IssueNo>' 	+  @newid_str + N'</IssueNo>
	<QuantityOrders>1</QuantityOrders>
	<QuantityOrderLines>' + @numOrderLines_str + N'</QuantityOrderLines>
</DocumentHeader>'




DECLARE @XMLdoc as XML = (SELECT 
R310_PO_NUMBER as 'OrderNumber',
'Standard' as 'TypeOrder',
'Add' AS 'TypeAction',
RTRIM(LTRIM(R310_SENDER_ID)) as 'CodeSender',
R070_EAN_PREFIX as 'EanPrefix',
RTRIM(LTRIM(R360_PARTNER_ID)) as 'CodeTradingPartner',
RTRIM(LTRIM(R880_WAREHOUSE_CODE)) as 'CodeWarehouse',
''  as 'Reference',
''  as 'Reference2' ,
CONVERT(VARCHAR(10),R310_PO_DATE,103) as 'DateOrder',
CONVERT(VARCHAR(10),R310_NOT_BEFORE_DATE,103) as 'DateNotBefore',
CONVERT(VARCHAR(10),R310_NOT_AFTER_DATE,103) as 'DateNotAfter',

R060_RECEIVER_CODE as 'CodeReceiver',
R060_NAME as 'NameShipTo',
R060_ADDRESS_1 as 'AddressLine1ShipTo',
R060_ADDRESS_2 as 'AddressLine2ShipTo',
R060_LOCATION as 'LocationShipTo',
R060_STATE as 'StateShipTo',
R060_POSTCODE as 'PostcodeShipTo',
R060_COUNTRY as 'CountryShipTo',
R060_CONTACT as 'ContactNameShipTo',
R060_TELEPHONE as 'TelephoneNoShipTo',
R060_FACSIMILE as 'FacsimilieNoShipTo',
R060_EMAIL_ADDRESS as 'EmailAddressShipTo',
R060_SPECIAL_INSTRUCTIONS as 'InstructionsDespatch',
'' AS 'DescriptionPurpose',
'' AS 'DateAdvertised',
'' as 'EventNumber',
'' As 'DockNumber',
0 AS 'DockTime',
RTRIM(LTRIM(R310_TRANSPORTER_CODE)) as 'CodeTransporter',
RTRIM(LTRIM(R310_SERVICE_CODE)) as 'CodeService',
1 as 'Priority',
(select 
	R320_INHOUSE_STORE_CODE as 'CodeShipFor',
	R320_STORE_NAME as 'NameShipFor',
	R320_STORE_ADDRESS_1 AS 'AddressLine1ShipFor',
	R320_STORE_ADDRESS_2 AS 'AddressLine2ShipFor',
	R320_STORE_LOCATION AS 'LocationShipFor',
	R320_STORE_STATE AS 'StateShipFor',
	R320_STORE_POSTCODE AS 'PostcodeShipFor',
	R320_STORE_COUNTRY AS 'CountryShipFor',
	R320_STORE_CONTACT AS 'ContactNameShipFor',
	R320_STORE_TELEPHONE AS 'TelephoneNoShipFor',
	R320_STORE_EMAIL_ADDRESS AS 'EmailAddressShipFor',
	RTRIM(LTRIM(R320_STORE_ZONE)) AS	'StoreZone',
	RTRIM(LTRIM(R320_DEPARTMENT_CODE)) AS 'CodeDepartment',
	R320_DEPARTMENT_NAME AS 'NameDepartment',
	'' AS 'PickSlipNumber',
	(SELECT 
		R330_SEQUENCE_NO AS 'LineNumberHost',
		R330_SEQUENCE_NO AS 'IdLine',
		R370_INHOUSE_STOCK_CODE AS 'CodeProduct',
		'' AS 'CodeEan',
		R330_SKU_PRODUCT_KEYCODE AS 'CodeProductSku',
		R370_PRODUCT_DESCRIPTION AS 'Description',
		'' AS 'BatchNumber',
		'' AS 'DateExpiry',
		R330_ORDER_QUANTITY AS 'QuantityOrdered',
		CAST(R330_PRICE_U_M AS decimal(7,2)) AS 'PriceWholeSalePerUnit',
		CAST(R330_RETAIL_UNIT_PRICE AS decimal (7, 2)) AS 'PriceRetailPerUnit',
		CAST(R330_KILOGRAMS_PER_UNIT AS DECIMAL(10,4)) AS 'KilogramsPerUnit',
		CAST(R330_CUBIC_PER_UNIT AS DECIMAL(10,6)) AS 'CubicMetresPerUnit',
		RTRIM(LTRIM(R330_ORDER_LINE_NUMBER)) AS 'DeliveryIdLine'
		FROM 
		F330_PO_PRODUCT PNPOP
		JOIN F370_PRODUCT_SCANPACK PNPSCP ON PNPOP.R330_R370_RECORD_ID = PNPSCP.R370_RECORD_ID
		WHERE POS1.R320_RECORD_ID = PNPOP.R330_R320_RECORD_ID
		FOR XML PATH('OrderLine'), ELEMENTS, TYPE) AS 'OrderLines'
	from 
	F320_PO_STORE POS1
	WHERE POH.R310_RECORD_ID = POS1.R320_R310_RECORD_ID
	FOR XML PATH('OrderShipFor'), elements, type) as 'OrderShipFors'
FROM 
F310_PO_HEADER POH
JOIN F320_PO_STORE ON R320_R310_RECORD_ID = R310_RECORD_ID
JOIN F070_SENDERS ON R310_SENDER_ID = R070_SENDER_CODE
JOIN F360_TRADING_PARTNER ON R310_R360_RECORD_ID = R360_RECORD_ID 
JOIN F880_WAREHOUSES ON R880_RECORD_ID = R310_R880_RECORD_ID
JOIN F060_RECEIVERS ON R320_R060_RECORD_ID = R060_RECORD_ID 
WHERE R310_RECORD_ID = ?
FOR XML PATH('OrderHeader'), ELEMENTS, ROOT('iSupply'))


SET @XMLdoc.modify('
insert sql:variable("@DocumentHeader")
before (/iSupply/OrderHeader)[1]');

select CAST(@XMLdoc AS VARCHAR(MAX))  as myXML

    When I run this from SSMS - it works fine.

     
  • Monday, February 04, 2013 2:33 PM
    Moderator
     
     
    Sign In to Vote
    0
    The SQL parser in SSIS 2008 I suspect is not too intuitive, thus making the above SQL ode a stored procedure makes sense to me. Just use the needed input parameters mapped once.

    Arthur My Blog

     
  • Monday, February 04, 2013 10:33 PM
     
     
    Sign In to Vote
    0

    Thanks Arthur, I implemented the stored proc as suggested, again it runs fine when the source db is on 2012 but fails (with the same message) on 2008 and 2008R2.

     
  • Friday, February 08, 2013 1:57 AM
    Moderator
     
     Answered
    Sign In to Vote
    0
    There might be a potential for a missing update or service pack, check if this is so.

    Arthur My Blog