none
Read XML Code inside a text file RRS feed

  • Question

  • Hello All

    I am currently working on a project where i have to read the  text file (file contains all XML Code) and parse that and load it to the database tables. If its a XML file  i would use a XML source to  load that file. But here it is a Flat file with XMl code in it . how to load that to a Sql database. Also i have to loop through such files and load all of them 

    Can you please  help 

    Tuesday, July 16, 2019 7:40 PM

Answers

  • Hi SSIS_help15,

    • You need to create a real destination table manually in SSMS. It is a one time operation.
    • As I already suggested, it is better to create a stored procedure, and call it passing SSIS variable with a fully qualified file name value as a parameter in SSIS Execute SQL Task.

    Here is a stored procedure. You would need to substitute a real destination table name instead of RealTableName bogus table name:

    CREATE PROCEDURE usp_whateverName
    (
    	@XMLfileName VARCHAR(256)
    ) AS
    BEGIN
    SET NOCOUNT ON;
    
    DECLARE @xml XML
       , @sql NVARCHAR(MAX);
    --   , @fileName VARCHAR(256) = "'@user::FullFilePath'";
    
    SET @sql = N'SELECT @xmlOut = XmlDoc FROM OPENROWSET (BULK ' + QUOTENAME(@XMLfileName,NCHAR(39)) + ', SINGLE_BLOB) AS Tab(XmlDoc)';
    
    EXEC master.sys.sp_executesql @sql, N'@xmlOut XML OUTPUT', @xmlOut = @xml OUTPUT;
    
    ;WITH XMLNAMESPACES (DEFAULT 'urn:tradefeed-xsd')
    INSERT INTO RealTableName --@tbl
    SELECT c.value('(Timestamp)[1]', 'VARCHAR(10)') AS [Timestamp]
    	, c.value('(FirmID)[1]', 'VARCHAR(200)') AS [FirmID]
    	, c.value('(ValueNumber)[1]', 'VARCHAR(50)') AS [ValueNumber]
    	, c.value('(Context)[1]', 'VARCHAR(50)') AS [Context]
    	, c.value('(CheckID)[1]', 'VARCHAR(20)') AS [CheckID]
    	, c.value('(CurrentFlow)[1]', 'VARCHAR(20)') AS [CurrentFlow]
    FROM @xml.nodes('/TradeFeedMessage') AS t(c);
    
    END
    Monday, July 22, 2019 3:04 PM
  • Hi SSIS_help15,

    No worries.

    The file extension doesn't matter. Any extension will work. What is important is just the file content shall be a well-formed XML.

    By the way, I hope you realized that if you need to have all XML elements together, under the root as well as repeating elements underneath in the entire XML file, you can abandon the SSIS XML Source Adapter and use this method that I showed you.

    Monday, July 22, 2019 7:10 PM
  • Hi SSIS_help15,

    The SQL Server 2012 standard edition will work without any problem. It doesn't need any special options to configure.

    SQL Server Agent account needs standard permissions to have access to the directory and XML files.

    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    • Marked as answer by SSIS_help15 Tuesday, July 23, 2019 4:20 PM
    Tuesday, July 23, 2019 3:51 PM

All replies

  • Hi SSIS_help15,

    Your description is very vague.

    Please share the "...Flat file with XMl code in it..." structure before we can suggest anything on how to deal with it.

    Tuesday, July 16, 2019 7:51 PM
  • I would create a Script component and convert the flat file contents into XML, as described here:

    https://stackoverflow.com/questions/29210429/conversion-of-flat-source-file-to-xml-in-sql-server-integration-services-ssis

    Once you write the flat file contents out to an XML file you can then process it normally with an XML source.

    Hope that helps,

    John

    Tuesday, July 16, 2019 9:14 PM
  • Hi SSIS_help15,

    Could you please share the text file (file contains all XML Code)?

    May I know if you can save the text file as XML file?

    Please refer to SSIS 2012 - load a series of xml files using ForEachLoop Container into a SQL Server table.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 17, 2019 5:22 AM
  • Hello Yitzhak

     I cannot  share  the XMl code here as its company policy. I tried using XML source even though using it as a text it still giving outputs. so i am good there. but my company requires me to read the root nodes and insert into a table. I  see XML source cannot read Root nodes and XSLT is not a option. can you please provide me any other way to read root nodes in XML ? i am not at all good at programming. so i am little hesitant to try script code 

    Friday, July 19, 2019 3:31 PM
  • Thank you John 


     I tried using XML source even though using it as a text it still giving outputs. so i am good there. but my company requires me to read the root nodes and insert into a table. I  see XML source cannot read Root nodes and XSLT is not a option. can you please provide me any other way to read root nodes in XML ? i am not at all good at programming. so i am little hesitant to try script code 

    Friday, July 19, 2019 3:31 PM
  • Hello Yitzhak

     I cannot  share  the XMl code here as its company policy... but my company requires me to read the root nodes and insert into a table. I  see XML source cannot read Root nodes and XSLT is not a option. can you please provide me any other way to read root nodes in XML ? i am not at all good at programming. so i am little hesitant to try script code 

    Hi SSIS_help15,

    No worries.

    1. Just obfuscate your XML file values and share it here.
      I will show you some other ways how to handle it outside of the SSIS XML Source Adapter.
    2. Just out of curiosity, why is that the XSLT is not an option?
    Friday, July 19, 2019 3:45 PM
  • Hello Yitzhak

    Please find it here 



    ----NEW 


    <TradeFeedMessage xmlns="urn:tradefeed-xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:f="urn:tradefeed-xsd">
    <Timestamp>XXX</Timestamp>
    <FirmID>XXX</FirmID>
    <ValueNumber>XXX</ValueNumber>
    <Context>XXX</Context>
    <CheckID>XXX</CheckID>
    <CurrentFlow>XXX</CurrentFlow>
    <TradeFeed>
    <Common>
    <FirmID>XXXX</FirmID>
    <TransactionNumber>XXX</TransactionNumber>
    <SecurityIdentifierFlag>XXX</SecurityIdentifierFlag>
    <SecurityIdentifier>XXX</SecurityIdentifier>
    <SecurityCurrencyISOCode>XXX</SecurityCurrencyISOCode>
    <SecurityProductKey>XXX</SecurityProductKey>
    <BloombergIdentifier> XXX</BloombergIdentifier>
    <Ticker>XXX</Ticker>
    <CouponStrikePrice>XXX</CouponStrikePrice>
    <MaturityDateExpirationDate>XXX</MaturityDateExpirationDate>
    <BuySellCoverShortFlag>XXX</BuySellCoverShortFlag>
    <RecordType>XXX</RecordType>
    <TradeDate>XXX</TradeDate>
    <AsOfTradeDate>XXX</AsOfTradeDate>
    <SettlementDate>XXX</SettlementDate>
    <TradeAmount>XXX</TradeAmount>
    <CustomerAccountCounterparty>XXX</CustomerAccountCounterparty>
    <AccountCounterpartyShortName>XXX</AccountCounterpartyShortName>
    <SettlementLocationIndicator>X</SettlementLocationIndicator>
    <ProductSubFlag>XX</ProductSubFlag>
    <ShortNotes>
    <ShortNote>
    <Index>X</Index>
    <Text>XXX</Text>
    </ShortNote>
    <ShortNote>
    <Index>X</Index>
    <Text>XXX </Text>
    </ShortNote>
    </ShortNotes>
    <LongNotes>
    <LongNote>
    <Index>X</Index>
    <Text>XXX123 </Text>
    </LongNote>
    <LongNote>
    <Index>X</Index>
    <Text>XXX</Text>
    </LongNote>
    <LongNote>
    <Index>X</Index>
    <Text>XXXXXX</Text>
    </LongNote>
    </LongNotes>
    <TraderAccountName>XXXX</TraderAccountName>
    <TimeOfSlate>XXXX</TimeOfSlate>
    <TimeOfSalesTicket>XXXX</TimeOfSalesTicket>
    <LastLogin>XXXX</LastLogin>
    <SalespersonLogin>XXXX</SalespersonLogin>
    <MasterTicketNumber>XXXX</MasterTicketNumber>
    <PrepaymentSpeedAndTypeContractSize>XXX</PrepaymentSpeedAndTypeContractSize>
    <SettlementCurrencyISOCode>XXX</SettlementCurrencyISOCode>
    <SettlementCurrencyRate>X.0</SettlementCurrencyRate>
    <CancelDueToCorrection>X</CancelDueToCorrection>
    <InvertFlag>X</InvertFlag>
    <AutoExTradeFlag>X</AutoExTradeFlag>
    <PutCallIndicator>X</PutCallIndicator>
    <TraderToTraderTradeFlag>X</TraderToTraderTradeFlag>
    <ContractSize>XXX</ContractSize>
    <Version>XXXX</Version>
    <UniqueBloombergID>XXXXX</UniqueBloombergID>
    <SystemDate>XXX</SystemDate>
    <ImpactFlag>XXXX</ImpactFlag>
    <SettlementLocationAbbreviation>XXX</SettlementLocationAbbreviation>
    <TraderAccountNumber>XX</TraderAccountNumber>
    <TransactionType>XXX</TransactionType>
    <CustodySafekeepingNumber>XXXX</CustodySafekeepingNumber>
    <EnteredTicketUserId>XXXX</EnteredTicketUserId>
    <AllocatedTicketUserId>XXXX</AllocatedTicketUserId>
    <BloombergFunctions>
    <FunctionName>X </FunctionName>
    <FunctionName>X </FunctionName>
    <FunctionName>X </FunctionName>
    <FunctionName/>
    </BloombergFunctions>
    <OptionsDelta>X</OptionsDelta>
    <MSRBReportable>XXX</MSRBReportable>
    <QuoteTypeIndicator>X</QuoteTypeIndicator>
    <PrimeBroker>XXX</PrimeBroker>
    <StrategyTags>
    <StrategyTag>
    <Level>X</Level>
    <ID>XXX</ID>
    <Name>XXX</Name>
    </StrategyTag>
    <StrategyTag>
    <Level>X</Level>
    <ID>XXX</ID>
    <Name>XXX</Name>
    </StrategyTag>
    <StrategyTag>
    <Level>X</Level>
    <ID>XXX</ID>
    <Name>XXXX</Name>
    </StrategyTag>
    </StrategyTags>
    <SoftDollarFlag>X</SoftDollarFlag>
    <MasterAccount>XXX</MasterAccount>
    <MasterAccountName>ABCXXXTest</MasterAccountName>
    <CTMMatchStatus>XXX</CTMMatchStatus>
    <MatchDate>XXXX</MatchDate>
    <IsDirtyPrice>X</IsDirtyPrice>
    <TSAMIndicator>XX</TSAMIndicator>
    <TaxLotMethod>X</TaxLotMethod>
    <CheckID>XXXX</CheckID>
    <ExecutionPlatform>-X</ExecutionPlatform>
    <ClientAuth>X</ClientAuth>
    <LastLoginUUID>XXX</LastLoginUUID>
    <OriginalTktId>XXX</OriginalTktId>
    <RTTMIndicator>XXX</RTTMIndicator>
    <RTTMReferenceID>XXXX</RTTMReferenceID>
    <CounterpartyEncodedLongName>XXXX=</CounterpartyEncodedLongName>
    <SecondHalfTdr2TdrTrade>X</SecondHalfTdr2TdrTrade>
    <CashReversalOffsetTicketIndicator>X</CashReversalOffsetTicketIndicator>
    <OptionContractSize>XXX</OptionContractSize>
    </Common>

    </TradeFeed>
    </TradeFeedMessage>

    I need to  read the below fields 

    <Timestamp>XXX</Timestamp>
    <FirmID>XXX</FirmID>
    <ValueNumber>XXX</ValueNumber>
    <Context>XXX</Context>
    <CheckID>XXX</CheckID>
    <CurrentFlow>XXX</CurrentFlow> . right now i can read other fields and not  the above ones 

    Friday, July 19, 2019 4:32 PM
  • Hi SSIS_help15,

    Here we go. So you need to get the root node immediate child elements

    I saved your XML on the file system as e:\Temp\TradeFeed.xml

    First, test is in SSMS.

    You may need to adjust the data types.

    Eventually, when it working for you, you can use SSIS OLE DB Source Adapter.

    ;WITH XMLNAMESPACES (DEFAULT 'urn:tradefeed-xsd'), XmlFile (Contents) AS
    (
    	SELECT CAST(BulkColumn AS XML) 
    	FROM OPENROWSET(BULK 'e:\Temp\TradeFeed.xml', SINGLE_BLOB) AS XmlData
    )
    SELECT c.value('(Timestamp)[1]', 'VARCHAR(10)') AS [Timestamp]
    	, c.value('(FirmID)[1]', 'VARCHAR(200)') AS [FirmID]
    	, c.value('(ValueNumber)[1]', 'VARCHAR(50)') AS [ValueNumber]
    	, c.value('(Context)[1]', 'VARCHAR(50)') AS [Context]
    	, c.value('(CheckID)[1]', 'VARCHAR(20)') AS [CheckID]
    	, c.value('(CurrentFlow)[1]', 'VARCHAR(20)') AS [CurrentFlow]
    FROM XmlFile CROSS APPLY Contents.nodes('/TradeFeedMessage') AS t(c);

    Output:

    Timestamp	FirmID	ValueNumber	Context	CheckID	CurrentFlow
    XXX	XXX	XXX	XXX	XXX	XXX

    Or you can package it as a stored procedure passing the file name as a parameter from SSIS:

    -- Method #2
    -- dynamic XML file name as a parameter
    DECLARE @xml XML
       , @sql NVARCHAR(MAX)
       , @fileName VARCHAR(256) = 'e:\Temp\TradeFeed.xml';
    
    SET @sql = N'SELECT @xmlOut = XmlDoc FROM OPENROWSET (BULK ' + QUOTENAME(@fileName,NCHAR(39)) + ', SINGLE_BLOB) AS Tab(XmlDoc)';
    
    EXEC master.sys.sp_executesql @sql, N'@xmlOut XML OUTPUT', @xmlOut = @xml OUTPUT;
    
    ;WITH XMLNAMESPACES (DEFAULT 'urn:tradefeed-xsd')
    SELECT c.value('(Timestamp)[1]', 'VARCHAR(10)') AS [Timestamp]
    	, c.value('(FirmID)[1]', 'VARCHAR(200)') AS [FirmID]
    	, c.value('(ValueNumber)[1]', 'VARCHAR(50)') AS [ValueNumber]
    	, c.value('(Context)[1]', 'VARCHAR(50)') AS [Context]
    	, c.value('(CheckID)[1]', 'VARCHAR(20)') AS [CheckID]
    	, c.value('(CurrentFlow)[1]', 'VARCHAR(20)') AS [CurrentFlow]
    FROM @xml.nodes('/TradeFeedMessage') AS t(c);


    Friday, July 19, 2019 5:04 PM
  • Thank you Yitzhak

      

      So in ssis i am getting the file path from  variable. in that case i  should replace the XML path inside the OPENROWSET with the variable correct?   if my variable name is @user::XMLFilePath then what would be the syntax for this. pardon me if this is a basic  question.I was thinking as below . please correct me if i am wrong 

    ;WITH XMLNAMESPACES (DEFAULT 'urn:tradefeed-xsd'), XmlFile (Contents) AS
    (
    	SELECT CAST(BulkColumn AS XML) 
    	FROM OPENROWSET(BULK '"@user::XMLFilePath"', SINGLE_BLOB) AS XmlData
    )
    SELECT c.value('(Timestamp)[1]', 'VARCHAR(10)') AS [Timestamp]
    	, c.value('(FirmID)[1]', 'VARCHAR(200)') AS [FirmID]
    	, c.value('(ValueNumber)[1]', 'VARCHAR(50)') AS [ValueNumber]
    	, c.value('(Context)[1]', 'VARCHAR(50)') AS [Context]
    	, c.value('(CheckID)[1]', 'VARCHAR(20)') AS [CheckID]
    	, c.value('(CurrentFlow)[1]', 'VARCHAR(20)') AS [CurrentFlow]
    FROM XmlFile CROSS APPLY Contents.nodes('/TradeFeedMessage') AS t(c);

    Friday, July 19, 2019 5:40 PM
  • Hi SSIS_help15

    I just expanded on my previous reply, providing a Method #2.

    Here is how to insert data into a table, a preparation for the SSIS Execute SQL Task:

    DECLARE @tbl TABLE
    (
    	[Timestamp] VARCHAR(10)
    	, FirmID VARCHAR(200)
    	, ValueNumber VARCHAR(50)
    	, Context VARCHAR(50)
    	, CheckID VARCHAR(20)
    	, CurrentFlow VARCHAR(20)
    );
    
    -- Method #2
    -- dynamic XML file name as a parameter
    DECLARE @xml XML
       , @sql NVARCHAR(MAX)
       , @fileName VARCHAR(256) = 'e:\Temp\TradeFeed.xml';
    
    SET @sql = N'SELECT @xmlOut = XmlDoc FROM OPENROWSET (BULK ' + QUOTENAME(@fileName,NCHAR(39)) + ', SINGLE_BLOB) AS Tab(XmlDoc)';
    
    EXEC master.sys.sp_executesql @sql, N'@xmlOut XML OUTPUT', @xmlOut = @xml OUTPUT;
    
    ;WITH XMLNAMESPACES (DEFAULT 'urn:tradefeed-xsd')
    INSERT INTO @tbl
    SELECT c.value('(Timestamp)[1]', 'VARCHAR(10)') AS [Timestamp]
    	, c.value('(FirmID)[1]', 'VARCHAR(200)') AS [FirmID]
    	, c.value('(ValueNumber)[1]', 'VARCHAR(50)') AS [ValueNumber]
    	, c.value('(Context)[1]', 'VARCHAR(50)') AS [Context]
    	, c.value('(CheckID)[1]', 'VARCHAR(20)') AS [CheckID]
    	, c.value('(CurrentFlow)[1]', 'VARCHAR(20)') AS [CurrentFlow]
    FROM @xml.nodes('/TradeFeedMessage') AS t(c);
    
    SELECT * FROM @tbl;
    Friday, July 19, 2019 5:43 PM
  • Thank you Yitzhak 

     I will  try this and get back to you

    Friday, July 19, 2019 6:51 PM
  • Hi SSIS_help15,

    May I know if you have anything to update?

    Please remember to click "Mark as Answer" the responses that resolved your issue.

    This can be beneficial to other community members reading this thread. 

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 22, 2019 9:39 AM
  • Hello Yitzhak

    I tried this in my management studio it worked good but  when I tried this in my execute SQL task in SSIS. But its  throwing me error saying cannot parse XML. I put  the above code in  Execute Sql task  and  gave file name as ? and used paramter mapping to provide a value. it is throwing me error. 

    Can you please help? I updated the code in Bold . gave the variable attached directly without parameter mapping 

    Will the below code work ?

    My filepath along with File name  is coming from the For each loop into  FullFilePath Variable 



     

    DECLARE @tbl TABLE
    (
    	[Timestamp] VARCHAR(10)
    	, FirmID VARCHAR(200)
    	, ValueNumber VARCHAR(50)
    	, Context VARCHAR(50)
    	, CheckID VARCHAR(20)
    	, CurrentFlow VARCHAR(20)
    );
    
    -- Method #2
    -- dynamic XML file name as a parameter
    DECLARE @xml XML
       , @sql NVARCHAR(MAX)
       , @fileName VARCHAR(256) = "'@user::FullFilePath'";
    
    SET @sql = N'SELECT @xmlOut = XmlDoc FROM OPENROWSET (BULK ' + QUOTENAME(@fileName,NCHAR(39)) + ', SINGLE_BLOB) AS Tab(XmlDoc)';
    
    EXEC master.sys.sp_executesql @sql, N'@xmlOut XML OUTPUT', @xmlOut = @xml OUTPUT;
    
    ;WITH XMLNAMESPACES (DEFAULT 'urn:tradefeed-xsd')
    INSERT INTO @tbl
    SELECT c.value('(Timestamp)[1]', 'VARCHAR(10)') AS [Timestamp]
    	, c.value('(FirmID)[1]', 'VARCHAR(200)') AS [FirmID]
    	, c.value('(ValueNumber)[1]', 'VARCHAR(50)') AS [ValueNumber]
    	, c.value('(Context)[1]', 'VARCHAR(50)') AS [Context]
    	, c.value('(CheckID)[1]', 'VARCHAR(20)') AS [CheckID]
    	, c.value('(CurrentFlow)[1]', 'VARCHAR(20)') AS [CurrentFlow]
    FROM @xml.nodes('/TradeFeedMessage') AS t(c);
    
    SELECT * FROM @tbl;




    Monday, July 22, 2019 2:39 PM
  • Hi SSIS_help15,

    • You need to create a real destination table manually in SSMS. It is a one time operation.
    • As I already suggested, it is better to create a stored procedure, and call it passing SSIS variable with a fully qualified file name value as a parameter in SSIS Execute SQL Task.

    Here is a stored procedure. You would need to substitute a real destination table name instead of RealTableName bogus table name:

    CREATE PROCEDURE usp_whateverName
    (
    	@XMLfileName VARCHAR(256)
    ) AS
    BEGIN
    SET NOCOUNT ON;
    
    DECLARE @xml XML
       , @sql NVARCHAR(MAX);
    --   , @fileName VARCHAR(256) = "'@user::FullFilePath'";
    
    SET @sql = N'SELECT @xmlOut = XmlDoc FROM OPENROWSET (BULK ' + QUOTENAME(@XMLfileName,NCHAR(39)) + ', SINGLE_BLOB) AS Tab(XmlDoc)';
    
    EXEC master.sys.sp_executesql @sql, N'@xmlOut XML OUTPUT', @xmlOut = @xml OUTPUT;
    
    ;WITH XMLNAMESPACES (DEFAULT 'urn:tradefeed-xsd')
    INSERT INTO RealTableName --@tbl
    SELECT c.value('(Timestamp)[1]', 'VARCHAR(10)') AS [Timestamp]
    	, c.value('(FirmID)[1]', 'VARCHAR(200)') AS [FirmID]
    	, c.value('(ValueNumber)[1]', 'VARCHAR(50)') AS [ValueNumber]
    	, c.value('(Context)[1]', 'VARCHAR(50)') AS [Context]
    	, c.value('(CheckID)[1]', 'VARCHAR(20)') AS [CheckID]
    	, c.value('(CurrentFlow)[1]', 'VARCHAR(20)') AS [CurrentFlow]
    FROM @xml.nodes('/TradeFeedMessage') AS t(c);
    
    END
    Monday, July 22, 2019 3:04 PM
  • Thank you So much Yitzhak.

    Will this also work if this  file extension is txt? but  the data inside the txt file is  XML. i just wanted to check that. I tried reading that txt file into xml source in ssis its giving the output nodes just as it is giving for XML file.

    Sorry to keep adding additional requirements to this question. 

    Monday, July 22, 2019 7:00 PM
  • Hi SSIS_help15,

    No worries.

    The file extension doesn't matter. Any extension will work. What is important is just the file content shall be a well-formed XML.

    By the way, I hope you realized that if you need to have all XML elements together, under the root as well as repeating elements underneath in the entire XML file, you can abandon the SSIS XML Source Adapter and use this method that I showed you.

    Monday, July 22, 2019 7:10 PM
  • Hello Yitzhak 

    Thank you . the above Script worked  real good . I just wanted to know below things 

    I am going to get a SQl Server 2012 version 

    1) Will this work in SQl 2012 standard version. are  there any options/settings i need to configure for this to work 

    2) Will this work in SQl Agent jobs. do i need to configure any options or any kind of permissions i need to   make this work 

    Thank you 

    Tuesday, July 23, 2019 3:46 PM
  • Hi SSIS_help15,

    The SQL Server 2012 standard edition will work without any problem. It doesn't need any special options to configure.

    SQL Server Agent account needs standard permissions to have access to the directory and XML files.

    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    • Marked as answer by SSIS_help15 Tuesday, July 23, 2019 4:20 PM
    Tuesday, July 23, 2019 3:51 PM
  • Thank you Yitzhak.

    Tuesday, July 23, 2019 4:20 PM
  • Hello Yitzhak

    Today my company gave me access to the Dev database and i tried the above Script. but its throwing me error  as below

    "

    Msg 4834, Level 16, State 1, Line 6

    You do not have permission to use the bulk load statement."

    Can you please help ?  

    As per my research on this people are  answering online that you  need Bulk Admin permissions on the Server level for executing such query above. So my question is is that all i need or do i need  any other permissions to run the above query? so i can request all permissions at once to them.


    Monday, July 29, 2019 7:54 PM
  • Hi SSIS_help15,

    It means that your new environment needs a small configuration adjustment, to allow the OPENROWSET command to work:

    USE master
    go
    
    GRANT ADMINISTER BULK OPERATIONS TO yourAccountName

    Or via user interface (UI) in SSMS as shown here:

    You do not have permission to use the bulk load statement error



    Monday, July 29, 2019 8:14 PM