October 1, 2008 at 3:49 pm
I have a need for part of my project to import all text files that have the naming convention of EntRptXXX.txt into a Staging database. This would have been fine if the data looked the same in each textfile but it doesn't. Each textfile corresponds exactly to a table in the Staging database, but they are structured differently.
Is there a way for me to dynamically map the textfile to the various tables. If, for some reason the table does not exist, can I dynamically create some sort of temporary holding table (with maybe a bunch of varchar(255)'s). Each textfile has the column names in the first line.
Thanks in advance
Andre
October 1, 2008 at 4:02 pm
How can you tell which file maps to which table?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 1, 2008 at 4:08 pm
The file name is identical to the table name. So table EntRptATM.txt maps to EntRptATM table.
October 1, 2008 at 4:21 pm
Yes there is a way, using dynamic SQL.
I once did something similar. I had a SQL query that would assign to a variable the name of the first file ready to be uploaded. That variable would then be passed to a stored procedure that would dynamically generate the command need to load that file into the correct table. The BCP used format files that were named the same as the data file, except for the extension, .txt vs .fmt.
If you can figure out how to load a file from the command line using BCP then the rest is not too difficult. In the stored procedure, take that command and replace the table name with ' + @Variable + ' to create a string that you can execute with xp_cmdshell.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 1, 2008 at 4:52 pm
That's a good idea, and could be a very clever/simple way (my favorite combination) of accomplishing this.
Could something similar be used for dynamic table creation?
October 1, 2008 at 4:58 pm
Well, that depends ....
Can you provide more details about what you mean?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 1, 2008 at 5:12 pm
The scenario is this. A textfile, with column headings in the first row, cannot find a corresponding table name. If the table does not exist, I would like the process to create a table with those column names and maybe a data type of varchar(255), or something. Also, the reason I was looking at SSIS was the branching and error handling.
Not too familiar with BCP, but it shows a lot of promise. And again, there's that clever/simple angle.
October 1, 2008 at 5:17 pm
I'm sure that could be made to work in SSIS with the help of a Script task.
You could use VB.NET to read the first row and build a "create table" SQL command from that.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply