Question about import of data from flat files
-
I am writing an SSIS package to import data from flat files into a database. Each flat file represents a different store # (i.e. 70file.csv is store #70).
I'm importing all the data from each file into 1 table and need a way to identify the corresponding store for each row of data.
Thanks for your help.
HH
-
Is the store number known at all or is it only available by looking at the filename? if only from the filename are all filenames in the format [StoreNumber]file.csv?
Catherine
Kindest Regards,Catherine Eibner
cybner.com.au -
The store number is in the filename (i.e. store #70 is 70file.csv, store #71 is 71file.csv, etc.).
I think I have a way to do it, but I can't get my variables to work. I have it where I can pull the fully qualified filename (c:\temp\70file.csv) into the table with the data. This works for now, but what I really want is loading the table name's into a log table, then putting the ID of each row in the log table with it's data in the import table. Follow me?
Thanks for your help.
HGHumphrey
-
Are you using a for each loop and adding the filename as a variable which you can then use to input into the log table? You could then use that variable as part of a lookup to obtain the Log tables ID and add to your result set in the dataflow.
Let me know if you need more details and I can try to generate some screenshots/step by step instructions for how I would do.
Thanks,
Catherine
Kindest Regards,Catherine Eibner
cybner.com.au -
Yes, that is exactly what I have done. I have the foreach loop, and inside of that I have a SQL EXEC that populates the log table by executing a stored proc.
Then, it goes to another SQL EXEC to execute another stored proc to get the max rowid from the log table, and this is where it is failing. My main problem is I am not sure which "expression" to set the variable for my rowid.
To insert the filename, I'm using the "connectionString" expression, but I can't find an expression that would correspond to the rowid?
HGHumphrey
-
I have actually used this reference before and found it really helpful - maybe it will help?
Kindest Regards,Catherine Eibner
cybner.com.au -
Yeah, that's the one I used, too, to get me this far. I am going to read over it again today to see if I've missed something.
Thanks for your help.
HGHumphrey
-
no worries.
One thing to remember is that you can access the variables as parameters in the lookup tool - it is just a bit hidden:
Add a lookup task inside your dataflow
Set the table you want to get the value from (in your case it will be the Log table)
Go to the columns tab and add the column you want returned as a new Column (The ID)
Go to the advanced tab and click the Enable Memory Restriction tick box then tick the modify the SQL statement and click the parameters button
Select your variable from the For Each Loop and click OK
You should then have an additional column added to your output with the Log File ID - you can pump this into whatever destination you were already using.
It is almost midnight here so I am going to get some sleep! I can try to setup a mockup of what you are doing tomorrow and send you more detailed steps/screenshots if you dont get it sorted by then!
Good Luck,
Catherine
Kindest Regards,Catherine Eibner
cybner.com.au -
My Pictures havent uplaoded so let me know if you need the screenshots and I can email them to you directly?
Kindest Regards,Catherine Eibner
cybner.com.au -
Thanks so much for your help! I definitely would like the screenshots. Can you e-mail them to me at holland_humphrey@yahoo.com ?
Thanks!
HGHumphrey
-
OK they are on their way.
Kindest Regards,Catherine Eibner
cybner.com.au
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply