adobner
asked on
Bulk insert- Text identifier
I need to import into SQL a text file that I get daily.
I tried to do it with a DTS and it went perfect the only problem is that the name of the file changes everyday (it's a Dated file).
So I thought about doing a INSERT BULK the problem is that I can't find how to specify the Text identifier, the problem is like this one of the columns is a definition, some times the definition in it self have a coma so the INSERT BULK cuts the definition and start the next column, in this case the definition is delimited with double quotes ("), else it doesn’t have it...
Here’s an example of the import:
CNS,Cohen & Steers Inc.,19247A100, 781126
CNSL,"CONSOLIDATED COMMUNICATIONS HOLDING, INC.",209034107, 1985745
CNT,CENTERPOINT PROPERTIES CORP,151895109, 6020355
I tried to do it with a DTS and it went perfect the only problem is that the name of the file changes everyday (it's a Dated file).
So I thought about doing a INSERT BULK the problem is that I can't find how to specify the Text identifier, the problem is like this one of the columns is a definition, some times the definition in it self have a coma so the INSERT BULK cuts the definition and start the next column, in this case the definition is delimited with double quotes ("), else it doesn’t have it...
Here’s an example of the import:
CNS,Cohen & Steers Inc.,19247A100, 781126
CNSL,"CONSOLIDATED COMMUNICATIONS HOLDING, INC.",209034107, 1985745
CNT,CENTERPOINT PROPERTIES CORP,151895109, 6020355
ASKER
is there any way to do it in code?
Not following you. What do you mean?
ASKER
yes, I want to call the DTS, from Code...
not from the command line, thats the reason I wanted to use an INSERT BULK
not from the command line, thats the reason I wanted to use an INSERT BULK
>>yes, I want to call the DTS, from Code...<<
If you mean using something like VB or VB.NET, than no problem, just set the Global Variable to the name of the file before you execute the package.
If you mean using something like VB or VB.NET, than no problem, just set the Global Variable to the name of the file before you execute the package.
ASKER
I get that, the question is how do I do it, call the DTS from code?
You can save the package as Visual Basic code.
ASKER
How?
I mean, whats the code like?
what libraries?
Etc,
I never done it bofore?
If you could reffer me to proper documantation...
Thanks
I mean, whats the code like?
what libraries?
Etc,
I never done it bofore?
If you could reffer me to proper documantation...
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
All you need to do is:
1. Create a Global Variable called "FileName".
2. Add a Dynamic Properties Task object.
3. Set the source text file Datasource property to the Global Variable FileName
4. Pass the source file name to the DTS package to the Global Variable name.