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

CSV file to table Expand / Collapse
Author
Message
Posted Tuesday, April 8, 2014 2:54 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
If the format keeps changing you can try using a Script task as Destination and you need to use a significant amount of code, I tried once.. I will try to post if I have it.

But in my case the data was coming from some stored procedure to a file. It was long back so I prefer saying I have to try again..


Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1559709
Posted Wednesday, April 9, 2014 12:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:06 PM
Points: 5,047, Visits: 11,797
a4apple (4/8/2014)
If the format keeps changing you can try using a Script task as Destination and you need to use a significant amount of code, I tried once.. I will try to post if I have it.

But in my case the data was coming from some stored procedure to a file. It was long back so I prefer saying I have to try again..


I think you mean a script component. If not, please expand on what you mean.

This can be done using an asynchronous script component as source (not destination - destination is the table where the data needs to be inserted) and it's not that much code.

The 'asynchronous' bit refers to the fact that the number of rows coming out of the component is different from what goes in.

Each row of the file should be read as a single string and then parsed in the script.

You will find examples if you search for them, or I can provide more input if you decide to change how you are doing things.




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 #1559796
Posted Wednesday, April 9, 2014 11:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:15 AM
Points: 99, Visits: 304
I was waiting and wondering when you would provide your input, Phil.

My co-worker resolved this by loading the data into a staging table and then using a SP to parse the records in the staging table and loading it into a final table.

Methinks that this isn't the best approach because he had to write of sql to accomplish this. And if the file changes by including an extra (or removing) column then it would take time to modify the sql. Do you think it would be "easier" to add code in the script component? And also to maintain the code in case there is a requirement to add or remove a column.

Thanks!
Post #1560109
Posted Wednesday, April 9, 2014 5:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 36,995, Visits: 31,516
herladygeekedness (4/8/2014)
I have often had a user adamant that what they provide is "good enough" so if you meet with resistance ...


Heh... I love users like that. They're the first to whine when you send them data in their same silly format. That's why I invite them to a high velocity pork-chop dinner at the first sign of such resistance.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1560213
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse