none
Creating a flat file in SSIS RRS feed

  • Question

  • Hello,

    I am trying to create a flat file as a destination, but the way the user want in a format that I am not sure how to do it.

    This is how my data looks like:

    150882||00641604710^60977011302^00641604701^10019010301^00641604501^00409677902|LORAZEPAM INJ (MDV) |||SOLUTION|4|MG|10|ML|||||INJECTION||

    So basically, only MEDID and then all the NDC_CODE seperated by a ^ then GENERIC_NAME and then remainder of the columns. Can I able to achieve with SSIS ?

    Thanks,

    Ali.

    Thursday, September 5, 2019 10:56 PM

Answers

  • Hi Ali,

    Here is your SQL query for the Data Flow Task, OLEDB Source:

    -- DDL and sample data population, start
    DECLARE @mockTbl TABLE (
    	ID INT IDENTITY(1,1) PRIMARY KEY
    	, [MED_ID] VARCHAR(30)
    	, [GENERIC_NAME] VARCHAR(30)
    	, [NDC_CODE] VARCHAR(30)
    );
    INSERT INTO @mockTbl ([MED_ID], [GENERIC_NAME], [NDC_CODE])
    VALUES ('150882', 'ORAZEPAM INJ (MDV)', '00641604710')
    	, ('150882', 'ORAZEPAM INJ (MDV)', '60977011302')
    	, ('150882', 'ORAZEPAM INJ (MDV)', '00641604701')
    	, ('150882', 'ORAZEPAM INJ (MDV)', '10019010301')
    	, ('150882', 'ORAZEPAM INJ (MDV)', '00641604501')
    	, ('150882', 'ORAZEPAM INJ (MDV)', '00409677902')
    	, ('100000', 'Something else', '00000000001')
    	, ('100000', 'Something else', '00000000002');
    -- DDL and sample data population, end
    
    DECLARE @separator CHAR(1) = '^';
    
    SELECT DISTINCT c.MED_ID, c.GENERIC_NAME,
    	STUFF((SELECT @separator + CAST(NDC_CODE AS VARCHAR(30)) AS [text()]
    		FROM @mockTbl AS O
    		WHERE O.MED_ID = C.MED_ID
    		ORDER BY MED_ID, NDC_CODE DESC
    		FOR XML PATH('')), 1, 1, NULL) AS NDC_CODE_LIST
    FROM @mockTbl AS C;

    Output:
    MED_ID	GENERIC_NAME	NDC_CODE_LIST
    100000	Something else	00000000002^00000000001
    150882	ORAZEPAM INJ (MDV)	60977011302^10019010301^00641604710^00641604701^00641604501^00409677902

    • Proposed as answer by ArthurZModerator Friday, September 6, 2019 5:06 PM
    • Marked as answer by aliahad Thursday, September 12, 2019 6:52 PM
    Friday, September 6, 2019 3:08 PM

All replies

  • Hi Ali,

    What is the source for the grid data? Is it a DB table?

    What is your SQL Server version?


    Thursday, September 5, 2019 11:12 PM
  • Hi Ali,

    You should use Script Component instead of Flat File Destination.

    Please refer to Creating a Destination with the Script Component.

    Check if it helps.

    Regards,

    Zoe


    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

    Friday, September 6, 2019 2:39 AM
  • Hi Yitzhak,

    So I wrote a query to extract the data and I copied and paste into excel so the users can verify the data and number of rows, after reviewing the data the only request that user have is that the flat file format should be the way I mentioned in the original post and that's where I hit the dead end. I researched online and found SQL pivot but not sure if that's the correct solution so I reached out to the forum see if anyone has experienced anything like that or am I the lucky one :-)

    Thanks,

    Ali.

    Friday, September 6, 2019 2:10 PM
  • Hi Ali,

    I asked you few questions:

    1. What is the source for the grid data? Is it a DB table?
      I am guessing, it is YES 
    2. What is your SQL Server version on the source DB?

    Depending on the answer #2, I will provide you a solution.



    Friday, September 6, 2019 2:40 PM
  • Yes, I am pulling the data from SQL server tables.

    Microsoft SQL Server 2014 (SP2-CU9) (KB4055557) - 12.0.5563.0 (X64)   Dec  7 2017 01:00:06   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) 


    • Edited by aliahad Friday, September 6, 2019 2:49 PM
    Friday, September 6, 2019 2:49 PM
  • Hi Ali,

    Here is your SQL query for the Data Flow Task, OLEDB Source:

    -- DDL and sample data population, start
    DECLARE @mockTbl TABLE (
    	ID INT IDENTITY(1,1) PRIMARY KEY
    	, [MED_ID] VARCHAR(30)
    	, [GENERIC_NAME] VARCHAR(30)
    	, [NDC_CODE] VARCHAR(30)
    );
    INSERT INTO @mockTbl ([MED_ID], [GENERIC_NAME], [NDC_CODE])
    VALUES ('150882', 'ORAZEPAM INJ (MDV)', '00641604710')
    	, ('150882', 'ORAZEPAM INJ (MDV)', '60977011302')
    	, ('150882', 'ORAZEPAM INJ (MDV)', '00641604701')
    	, ('150882', 'ORAZEPAM INJ (MDV)', '10019010301')
    	, ('150882', 'ORAZEPAM INJ (MDV)', '00641604501')
    	, ('150882', 'ORAZEPAM INJ (MDV)', '00409677902')
    	, ('100000', 'Something else', '00000000001')
    	, ('100000', 'Something else', '00000000002');
    -- DDL and sample data population, end
    
    DECLARE @separator CHAR(1) = '^';
    
    SELECT DISTINCT c.MED_ID, c.GENERIC_NAME,
    	STUFF((SELECT @separator + CAST(NDC_CODE AS VARCHAR(30)) AS [text()]
    		FROM @mockTbl AS O
    		WHERE O.MED_ID = C.MED_ID
    		ORDER BY MED_ID, NDC_CODE DESC
    		FOR XML PATH('')), 1, 1, NULL) AS NDC_CODE_LIST
    FROM @mockTbl AS C;

    Output:
    MED_ID	GENERIC_NAME	NDC_CODE_LIST
    100000	Something else	00000000002^00000000001
    150882	ORAZEPAM INJ (MDV)	60977011302^10019010301^00641604710^00641604701^00641604501^00409677902

    • Proposed as answer by ArthurZModerator Friday, September 6, 2019 5:06 PM
    • Marked as answer by aliahad Thursday, September 12, 2019 6:52 PM
    Friday, September 6, 2019 3:08 PM
  • Hi Yitzhak,

    So if I understand it correctly, create a temp table and insert values from my query and then do the STUFF command. 

    Thanks,

    Ali.

    Friday, September 6, 2019 3:57 PM
  • Hi Ali,

    You can also try CTE approach without a temp table:

    ;WITH rs AS
    (
    	-- your query is here
    	SELECT * FROM @mockTbl
    )
    SELECT DISTINCT c.MED_ID, c.GENERIC_NAME,
    	STUFF((SELECT @separator + CAST(NDC_CODE AS VARCHAR(30)) AS [text()]
    		FROM rs AS O
    		WHERE O.MED_ID = C.MED_ID
    		ORDER BY MED_ID, NDC_CODE DESC
    		FOR XML PATH('')), 1, 1, NULL) AS NDC_CODE_LIST
    FROM rs AS C;

    Friday, September 6, 2019 5:09 PM
  • Thanks for the solution, I'll let you know which one makes me successful.
    Friday, September 6, 2019 6:47 PM
  • Hi Ail,

    Have you resolved your issue? Would you please kindly give us some feedback?

    Regards,
    Zoe

    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, September 9, 2019 1:26 AM
  • Hi Ali,

    What's the latest on your end?

    Was the proposed solution helpful?

    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.


    Wednesday, September 11, 2019 3:41 AM
  • Hi Yitzhak,

    Apologies for not responding earlier, I tried the solution but only flaw I find is that NDC code is at the end and the user wants it at the third column not sure if it can be achieved or no.

    Let me know.

    Thanks,

    Ali. 

    Wednesday, September 11, 2019 6:58 PM
  • Hi Ali,

    You can put any given column in any position on the SELECT clause.

    SQL is a set oriented language, so a position doesn't matter.


    Wednesday, September 11, 2019 7:05 PM
  • But we are using SELECT DISTINCT for MED_ID since there can be multiple NDC for one MED_ID
    Wednesday, September 11, 2019 7:06 PM
  • Hi Ali,

    The same is applicable to the SELECT DISTINCT … clause.

    Beyond that, SSIS will allow you to arrange the columns as you need them while you are generating the flat file via SSIS Flat File Destination.

    Wednesday, September 11, 2019 7:23 PM
  • It is giving me that  STUFF is not a recognized built in function name

    DECLARE @separator CHAR(1) = '^';
    
    SELECT DISTINCT 
    C.MED_ID,
    C.NEW_MED_ID,
    STUFF((SELECT @separator + CAST(NDC_CODE as VARCHAR(30))) as [text()]
    from
    FORMULARY_DRUG  AS O
    where O.MED_ID = C.MED_ID
    order by MED_ID, NDC_CODE 
    for XML PATH('')), 1,1,NULL) as NDC_CODE_LIST
    C.GENERIC_NAME,
    C.BRAND_NAME,
    C.DEA_CLASS,
    C.DOSAGE_FORM,
    C.STRENGTH_VALUE,
    C.STRENGTH_UNITS_TEXT,
    C.VOLUME_VALUE,
    C.VOLUME_STRENGTH,
    C.CONCERNTRATION,
    C.CONCERNTRATION_VALUE_TEXT,
    C.THERAPUTIC_CLASS,
    C.CHARGE_NUMBER,
    C.STANDARD_ROUTE_TEXT
    from
    FORMULARY_DRUG AS C

    Wednesday, September 11, 2019 7:36 PM
  • Hi Ali,

    STUFF() function is available in SQL Server starting from SQL Serve 2008 onwards.

    What is your SQL Server version? You mentioned earlier in this thread it is SQL Server 2014.

    You can check the version via issuing SELECT @@version; in SSMS.


    Wednesday, September 11, 2019 7:55 PM
  • I did that when you first asked me the SQL version:

    Microsoft SQL Server 2014 (SP2-CU9) (KB4055557) - 12.0.5563.0 (X64)   Dec  7 2017 01:00:06   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) 


    • Edited by aliahad Wednesday, September 11, 2019 8:30 PM
    Wednesday, September 11, 2019 8:24 PM
  • Hi Ali,

    1. The STUFF() function is available in SQL Server starting from SQL Serve 2008 onwards.
      Please share a screen shot of the SSMS with the failing SQL statement and the error.
    2. Did you run in SSMS my example with the @mockTbl?

    Wednesday, September 11, 2019 8:30 PM
  • So I created a temp table 'FORMULARY_DRUG' using my original query and then I am using STUFF command that you explained it earlier:



    • Edited by aliahad Wednesday, September 11, 2019 8:43 PM
    Wednesday, September 11, 2019 8:42 PM
  • Hi Ali,

    1. The STUFF() function is available in SQL Server starting from SQL Serve 2008 onwards.
      Please share a screen shot of the SSMS with the failing SQL statement and the error.
    2. Did you run in SSMS my example with the @mockTbl?

    I see that the STUFF() function is colored in magenta color. Your SQL Server does recognize it.

    Wednesday, September 11, 2019 9:23 PM
  • SO I created a temp table instead of a regular table, does the STUFF works on temp table or do I need to create a table in SQL Server also

    • Edited by aliahad Wednesday, September 11, 2019 9:38 PM
    Wednesday, September 11, 2019 9:34 PM
  • Hi Ali,

    1. Is it possible that your SQL Server instance is 2014, but the DB is in the compatibility mode that predates 2008? Please check the DB compatibility mode.
    2. And again, please re-run my example with the @mockTbl.

    SELECT name, compatibility_level  
    FROM sys.databases WHERE name = 'YourDBname';
    Wednesday, September 11, 2019 9:42 PM
  • Hi Yitzhak,

    Please see the screen shot for compatibility below. I used your query to get result:

    Secondly, I create a table in SQL server and load all the data using a SSIS package. Now I am using the STUFF() and still the same error. 

    Thursday, September 12, 2019 4:56 PM
  • Hi Ali,

    You seems that you need to change your DB compatibility level to match SQL Server 2014:

    View or Change the Compatibility Level of a Database

    "...To change the compatibility level, select a different option from the list. The choices are SQL Server 2008 (100), SQL Server 2012 (110), SQL Server 2014 (120), SQL Server 2016 (130), and SQL Server 2017 (140)..."

    Thursday, September 12, 2019 5:01 PM
  • That also didn't help

    Thursday, September 12, 2019 5:16 PM
  • Thanks again Yitzhak for all the help.
    Thursday, September 12, 2019 6:52 PM