July 12, 2006 at 9:10 am
Hi
How can I load a text file into staging table in sql. Here am not sure what the file name is, user will be passing file name as a parameter and my DTS task has to load that file.
How can I accomplish this, pls help.
July 12, 2006 at 9:26 am
Not sure I understand you.Don,t you need a file name to be able to import or am I missing something ?
July 12, 2006 at 9:48 am
may be consfusing..sorryabt that.
I have bunch of files and the user has to load a single file which he wants. so that user has to select the file name and pass it as a parameter to the SP( which runs the DTS to load). so that the DTS will load the exact file what the user needs to.
July 12, 2006 at 9:50 am
jp:
You can use the Dynamic Properties task to accomplish this assuming the data layout in the files do not change. Here's an article for your research:
http://www.sqlservercentral.com/columnists/bknight/dynamicpropertiestask.asp
July 12, 2006 at 11:01 am
I read the article but didnt find any clue to accomplish my task.
Tha thing is user will be passing a file name to my load process as a parameter so that the task loads the the particular file.
July 12, 2006 at 11:59 am
Assuming that you get the file name into a global variable, you do the following:
1. Before the transform, insert a dynamic properties task. Make sure the workflow is adjusted accordingly.
2. Click the "Add" button.
3. On the left pane of the new window that pops up is the treeview. On the treeview, expand Connections, then click the name of the connection object that represents your text file.
4. You will notice on the right pane is the Datasource property. Doubleclick that.
5. A new window pops up, use the Source dropdown to select Global Variable.
6. Then use the Variable dropdown to select the global variable that the filename is in. Make sure the full path to the file is there. Use UNC instead of mapped drive letters.
7. You will then need to add another property. Repeat step #2.
8. Repeat step 3 except this time expand the name of the connection object, expand the OLEDB (or ODBC?) properties, and then select Data Source.
9. Double click the Value property on the right pane.
10. Repeat steps 5 and 6.
11. One more property: Repeat step 2.
12. This time, expand Tasks on the left pane.
13. Find the datapump task that loads your file and click on it.
14. Double click SourceObjectName.
15. Repeat steps 5 and 6.
July 12, 2006 at 12:01 pm
BTW, just how is the user passing the file name?
July 12, 2006 at 12:03 pm
Why use DTS when you don't have to ?
This whole thing is triggered by calling a stored proc, right ?
So, use the BULK INSERT t-sql command within the stored proc and pass it the SQL variable containing the filename. Keep it simple. No need for a DTS package to get this file loaded.
July 12, 2006 at 12:23 pm
You're right if this is, in fact, called by a sp. And also assuming he is not massaging the data with ActiveX on his transform.
It would be helpful to know these things.
July 13, 2006 at 3:14 pm
jp...
Use a File System Object in an ActiveX Script task in your DTS package and look for the passed-in file name.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply