Iniciar sesión
 
InicioBibliotecaAprendizajeDescargaSoporteComunidadForos
Recursos para Profesionales de TI >
> 
> 
how i can distinguish this data coming from flat file

Respondida how i can distinguish this data coming from flat file

  • martes, 01 de mayo de 2012 8:11
     
     
    Inicie sesión para votar
    0
    1RAM5000PM
    2HARISH15000TL
    3KISHORE12000kPM
    4NAVEEN13000TL
    5NARESH11000SE
     

Todas las respuestas

  • martes, 01 de mayo de 2012 8:18
    Moderador
     
     
    Inicie sesión para votar
    0
    1RAM5000PM
    2HARISH15000TL
    3KISHORE12000kPM
    4NAVEEN13000TL
    5NARESH11000SE

    Provide some more info...

    What are the columns?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • martes, 01 de mayo de 2012 8:26
     
     
    Inicie sesión para votar
    0

    in my source data is coming without any delimiters

    columns are also not there

    columns are id,name,sal,job

    • Editado v60 martes, 01 de mayo de 2012 8:28
    •  
     
  • martes, 01 de mayo de 2012 8:31
    Moderador
     
     
    Inicie sesión para votar
    0

    in my source data is coming without any delimiters

    columns are also not there

    columns are id,name,sal,job

    if there are no delimiters then you should use RaggedRight, but I don't see that in your example. You got to know where the column begins and ends...


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • martes, 01 de mayo de 2012 9:33
     
     
    Inicie sesión para votar
    0

    hi ssisjost ,

    can u tell me how can i load above data

    what options i have to configure in the flat file source

     
  • martes, 01 de mayo de 2012 13:46
    Moderador
     
     
    Inicie sesión para votar
    0

    hi ssisjost ,

    can u tell me how can i load above data

    what options i have to configure in the flat file source


    Can you tell me where the columns begin and end? Define the rules..

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • martes, 01 de mayo de 2012 14:24
     
     
    Inicie sesión para votar
    0
    if you know how can you delimete data you can use Script Componenet in data flow for transmission.

    http://ismailadar.com/ İsmail ADAR

     
  • martes, 01 de mayo de 2012 14:29
     
     
    Inicie sesión para votar
    0

    In your FlatFile Source shape make it as one column then split it using Script Transform.

    Since you have Number,Text, Number, Text Pattern you can try splitting using regular expression.

    See: http://www.dotnetperls.com/regex-split

    Randy Aldrich Paulo

    MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog


    BizTalk Message Archiving - SQL and File
    Automating/Silent Installation of BizTalk Deployment Framework using Powershell >
    Sending IDOCs using SSIS


     
  • martes, 01 de mayo de 2012 15:41
     
     Respuesta propuesta
    Inicie sesión para votar
    0

    in my source data is coming without any delimiters

    columns are also not there

    columns are id,name,sal,job

    Vijay60,

    Even when the data is without header or column delimiters as in your example, it still needs to follow a pattern so that individual columns can be identified easily. In the real world, the data providers and the data consumers have explicit understanding and create rules among themselves as to the format of the data. The data consumers create their ETL packages based on this understanding and rules. The format or column layout can not keep on changing arbitrarily.

    If for some reason, you can not control the layout of the columns in the flat-file then you'd have to write complex scripts or regex pattern matching to identify the data.

    To keep things simple, the columns in the flat-file can be padded with spaces to make them of equal width using SPACE and LEN functions or other methods at the time of generating the flat-file. I've done that for your sample data below with these assumptions for columns -
    ID=1, NAME=7, SALARY=5 and remaining as JOB.

    1RAM     5000PM
    2HARISH 15000TL
    3KISHORE12000kPM
    4NAVEEN 13000TL
    5NARESH 11000SE

    Once that is done, the flat-file can be used as Ragged-Right source as below -

    Ragged-right example


    -------------------------------------
    Please mark this as as answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam
    http://aalamrangi.wordpress.com/
     
  • viernes, 04 de mayo de 2012 11:06
     
     
    Inicie sesión para votar
    0

    To keep things simple, the columns in the flat-file can be padded with spaces to make them of equal width using SPACE and LEN functions or other methods at the time of generating the flat-file

    hi alam rangi how this can be done please tell me

     
  • viernes, 04 de mayo de 2012 12:12
    Moderador
     
     
    Inicie sesión para votar
    0

    To keep things simple, the columns in the flat-file can be padded with spaces to make them of equal width using SPACE and LEN functions or other methods at the time of generating the flat-file

    hi alam rangi how this can be done please tell me


    If you add spaces to create rows that all have the same length then you know where the columns begin and end. So you can use Ragged Right in the Flat File Connection manager.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


     
  • sábado, 05 de mayo de 2012 0:07
     
      Tiene código
    Inicie sesión para votar
    0

    Sample script below -

    use tempdb
go
CREATE TABLE #t_table (ID INT, NAME VARCHAR(7), SALARY VARCHAR(5), JOB VARCHAR(10))
INSERT INTO #t_table (ID, NAME, SALARY, JOB)
SELECT '1', 'RAM', '5000', 'PM' UNION ALL
SELECT '2', 'HARISH', '15000', 'TL' UNION ALL
SELECT '3', 'KISHORE', '12000', 'kPM' UNION ALL
SELECT '4', 'NAVEEN', '13000', 'TL' UNION ALL
SELECT '5', 'NARESH', '11000', 'SE'
SELECT * FROM #t_table
-- The process which creates the flat-file 
--    should use a query similar to the one below as source.
-- It will convert data to VARCHAR and append trailing spaces.
-- It will also concatenate all columns into seemingly one 
--   single row but you can split it into columns as mentioned
--   in the screenshot previously.
-- This is just a quick example, you'll need to decide if you
--   want leading or trailing spaces according to your data.
SELECT 
CONVERT(VARCHAR(1), ID) +
CONVERT(VARCHAR(7), LTRIM(RTRIM(NAME))) + REPLICATE (SPACE(1), 7 - LEN(LTRIM(RTRIM(NAME)))) +
CONVERT(VARCHAR(5), LTRIM(RTRIM(SALARY))) + REPLICATE (SPACE(1), 5 - LEN(LTRIM(RTRIM(SALARY)))) +
CONVERT(VARCHAR(10), LTRIM(RTRIM(JOB))) + REPLICATE (SPACE(1), 10 - LEN(LTRIM(RTRIM(JOB))))
FROM #t_table
drop table #t_table

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

     
  • sábado, 05 de mayo de 2012 10:18
     
     
    Inicie sesión para votar
    0

    so it is the process before creating flat file

    after creating file with out the above process is there is any way to pad with spaces of that columns

     
  • domingo, 06 de mayo de 2012 2:17
     
     Respondida
    Inicie sesión para votar
    0
    I don't think there is any easy way to pad data in the flat file with spaces AFTER the file has been created. As the data would be of variable length, how would you know where a data column begins or ends? That was the original issue you posted in the beginning.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)