Viewing 15 posts - 40,711 through 40,725 (of 59,070 total)
Sorry Jags... I just don't know that much about SSIS. Hopefully, someone will see this and lend a hand.
As a sidebar, I typically do this type of stuff in...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 7:08 am
I won't be able to get to this until I get home from work. If you study the code I've offered so far, you'll probably figure it out for...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 7:06 am
Thanks, Arun... the only thing I haven't done is test it for performance... that's because I don't normally do such formatting in SQL Server. The only time I do...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 7:03 am
igloo21 (12/10/2009)
CirquedeSQLeil (12/9/2009)
Have you considered using a maintenance plan, or an SSIS package to do this?Both seem like viable alternatives as well (in addition to Steve's suggestion).
Yes I have but...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 7:01 am
Yep... you've got it. Select/Into FROM OpenRowSet also works.
You mentioned this was for big files. I'd first see if I could get the vendor to make the delimiters...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 6:57 am
I'm not sure what it is in SSIS but, in T-SQL, CHAR(9) is the tab character. In some places, it may be just \t or "\t".
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 12:52 am
This should do it... use the SELECT as if it were a table...
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 12:47 am
You can actually do it a tiny bit simpler...
SELECT REPLACE(STR(350,16,5),' ','0')
STR returns NVARCHAR values.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 12:39 am
FredS-1001785 (12/9/2009)
This it faster for the operator to scan with their eye for what they are looking for.
Ah... finally... someone with a practical reason. Thanks, Fred.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 12:30 am
I do understand... that's why I you should post the data in the format requested... you'll get help a lot faster.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 12:28 am
You bet, Anitha. Thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 12:26 am
Why don't you split it on the way in? I'm no SSIS wizard, but I believe they have a BULK task of some sort to load and split files...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 12:26 am
I'm pretty sure that it's not working correctly at all. The default delimiter for BULK INSERT is the tab character and you're using something else. You've also not...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 12:21 am
Divid'n'Conquer...
CREATE TABLE #names (full_name varchar(254))
INSERT INTO #names VALUES ('Simpson, Homer J')
INSERT INTO #names VALUES ('Simpson, Bartholomew JoJo')
INSERT INTO #names VALUES ('Simpson,Maggie')
INSERT INTO #names VALUES ('Bouvier Simpson, Margorie "Marge"')
INSERT INTO #names...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 12:17 am
SUBSTRING(COMMENTS, 1, LEN(COMMENTS)) will always return the full length of the field. Look at it... you need to do something else.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 9, 2009 at 11:12 pm
Viewing 15 posts - 40,711 through 40,725 (of 59,070 total)