Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

File import to table in Unix Expand / Collapse
Author
Message
Posted Sunday, January 12, 2014 8:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 6:50 AM
Points: 49, Visits: 166
I am struck in one point as we gets files in unix box and we don't have any windows server .
Issue - We get pipe'|' separated text file, usually this can be bcp in to table if the structure exactly matching with table .
Here structure is same but some of the rows having issue with row delimiters which cause overlapping to next line.
This file opens in excel perfectly no issue.

I need to find a solution how to fix this. This we cannot say forrmat issue as this issue with only on unix env. Is there any way we can use ssis ? please advice if any other solution.
Post #1530101
Posted Sunday, January 12, 2014 8:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 5,161, Visits: 12,005
Is there any way we can use ssis ?


To do what, precisely?

As you have given no examples of the data which is giving you problems, providing any sort of useful answer is close to impossible. How does SSIS fit into the architecture of your Unix environment?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530102
Posted Sunday, January 12, 2014 8:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 6:50 AM
Points: 49, Visits: 166
Phil Parkin (1/12/2014)
Is there any way we can use ssis ?


To do what, precisely?

As you have given no examples of the data which is giving you problems, providing any sort of useful answer is close to impossible. How does SSIS fit into the architecture of your Unix environment?


Sample data looks like below, for 3rd row rowdelimiter is not correct.
|USD|1542.52|14536|xxxx
||1542.52|14536|
EUR|1542.52|14536|xxxx

I m not sure how does SSIS Fit into Unix, please advice me if SSIS fits into unix.

I can create an ksh scripts ,is ssis can be called from ksh ?
Post #1530108
Posted Sunday, January 12, 2014 9:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 5,161, Visits: 12,005
sql crazy kid (1/12/2014)
Phil Parkin (1/12/2014)
Is there any way we can use ssis ?


To do what, precisely?

As you have given no examples of the data which is giving you problems, providing any sort of useful answer is close to impossible. How does SSIS fit into the architecture of your Unix environment?


Sample data looks like below, for 3rd row rowdelimiter is not correct.
|USD|1542.52|14536|xxxx
||1542.52|14536|
EUR|1542.52|14536|xxxx

I m not sure how does SSIS Fit into Unix, please advice me if SSIS fits into unix.

I can create an ksh scripts ,is ssis can be called from ksh ?


Sorry but I know very little about Unix. But I'm pretty sure that SSIS can be used to process and push data into a Unix database, assuming the connectivity can be resolved.

But SSIS needs to be installed on a Windows server - is that a show-stopper for you? It's also quite expensive to justify if you are not already using SQL Server.

Does your sample data represent two or three rows of actual data?

If three rows, it looks like the column delimiters (not row delimiters) are incorrectly placed. That's just bad data and it should be fixed at source, not by you - in my opinion.




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530110
Posted Sunday, January 12, 2014 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 6:50 AM
Points: 49, Visits: 166
Phil Parkin (1/12/2014)
sql crazy kid (1/12/2014)
Phil Parkin (1/12/2014)
Is there any way we can use ssis ?


To do what, precisely?

As you have given no examples of the data which is giving you problems, providing any sort of useful answer is close to impossible. How does SSIS fit into the architecture of your Unix environment?


Sample data looks like below, for 3rd row rowdelimiter is not correct.
|USD|1542.52|14536|xxxx
||1542.52|14536|
EUR|1542.52|14536|xxxx

I m not sure how does SSIS Fit into Unix, please advice me if SSIS fits into unix.

I can create an ksh scripts ,is ssis can be called from ksh ?


Sorry but I know very little about Unix. But I'm pretty sure that SSIS can be used to process and push data into a Unix database, assuming the connectivity can be resolved.

But SSIS needs to be installed on a Windows server - is that a show-stopper for you? It's also quite expensive to justify if you are not already using SQL Server.

Does your sample data represent two or three rows of actual data?

If three rows, it looks like the column delimiters (not row delimiters) are incorrectly placed. That's just bad data and it should be fixed at source, not by you - in my opinion.



Here is few more details
Backend - SQL server 2008 R2,SSIS installed
OS - Unix Env

We have many process running on unix ,connecting to sql server , BCP into sql tables , all are working fine .

Above sample data is a representation of actual data . But really there is a correction required on data file , but in case we are using this in windows env this not an issue . Either with SSIS or Informatica this works perfectly with no issue as we have column to column mapping . Since that we cannot ask to fix that issue.



Post #1530114
Posted Sunday, January 12, 2014 9:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 5,161, Visits: 12,005
Here is few more details
Backend - SQL server 2008 R2,SSIS installed
OS - Unix Env

We have many process running on unix ,connecting to sql server , BCP into sql tables , all are working fine .

Above sample data is a representation of actual data . But really there is a correction required on data file , but in case we are using this in windows env this not an issue . Either with SSIS or Informatica this works perfectly with no issue as we have column to column mapping . Since that we cannot ask to fix that issue.


OK. Then please answer my questions about your sample data. At the moment, it looks like:

c1 (blank),c2,c3,c4,c5
c1 (blank),c2 (blank),c3,c4,c5 (blank)
c1 (missing),c2,c3,c4,c5

My point? If columns can be missed out randomly from your source data, there is NO WAY ON EARTH to get it fixed by SSIS, which is lacking the necessary Paranormal Transformation.

If you are saying that only certain columns are missing, under certain circumstances, you need to explain the logic of what needs to be done please.






Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530116
Posted Sunday, January 12, 2014 10:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 6:50 AM
Points: 49, Visits: 166
Phil Parkin (1/12/2014)
Here is few more details
Backend - SQL server 2008 R2,SSIS installed
OS - Unix Env

We have many process running on unix ,connecting to sql server , BCP into sql tables , all are working fine .

Above sample data is a representation of actual data . But really there is a correction required on data file , but in case we are using this in windows env this not an issue . Either with SSIS or Informatica this works perfectly with no issue as we have column to column mapping . Since that we cannot ask to fix that issue.


OK. Then please answer my questions about your sample data. At the moment, it looks like:

c1 (blank),c2,c3,c4,c5
c1 (blank),c2 (blank),c3,c4,c5 (blank)
c1 (missing),c2,c3,c4,c5

My point? If columns can be missed out randomly from your source data, there is NO WAY ON EARTH to get it fixed by SSIS, which is lacking the necessary Paranormal Transformation.

If you are saying that only certain columns are missing, under certain circumstances, you need to explain the logic of what needs to be done please.





Columns are not missing here . I have 5 column here ,looks like this.
[Just a sample data ,but this is the format we load]
col1 col2 col3 col4 col5
USD 1542.52 14536 xxxx
1542.52 14536
EUR 1542.52 14536 xxxx
Post #1530118
Posted Sunday, January 12, 2014 10:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 5,161, Visits: 12,005
Columns are not missing here . I have 5 column here ,looks like this.
[Just a sample data ,but this is the format we load]
col1 col2 col3 col4 col5
USD 1542.52 14536 xxxx
1542.52 14536
EUR 1542.52 14536 xxxx


What would this sample data look like if there were no problems with delimiters?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530122
Posted Sunday, January 12, 2014 10:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 6:50 AM
Points: 49, Visits: 166
Phil Parkin (1/12/2014)
Columns are not missing here . I have 5 column here ,looks like this.
[Just a sample data ,but this is the format we load]
col1 col2 col3 col4 col5
USD 1542.52 14536 xxxx
1542.52 14536
EUR 1542.52 14536 xxxx


What would this sample data look like if there were no problems with delimiters?


I am getting output as like below when i do bcp command on unix.
C1 C2 C3 C4 C5
<NULL> USD 1542.52 14536 xxxx
<NULL> <NULL> 1542.52 14536 EUR|1542.52|14536|xxxx
Post #1530124
Posted Sunday, January 12, 2014 10:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 5,161, Visits: 12,005
sql crazy kid (1/12/2014)
Phil Parkin (1/12/2014)
Columns are not missing here . I have 5 column here ,looks like this.
[Just a sample data ,but this is the format we load]
col1 col2 col3 col4 col5
USD 1542.52 14536 xxxx
1542.52 14536
EUR 1542.52 14536 xxxx


What would this sample data look like if there were no problems with delimiters?


I am getting output as like below when i do bcp command on unix.
C1 C2 C3 C4 C5
USD 1542.52 14536 xxxx
1542.52 14536 EUR|1542.52|14536|xxxx


I understand, but that is not the question I am asking.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530125
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse