Insert ID from CSV File
-
Hello all,
First of all I think this is one $%^of a forum, tons of information and I thank you for that
I have a question, I'm importing data from multiple csv files. The layout is for every CSV file the same.
I use a for each file enummerator, and that works fine.
each csv file is from a different supplier, but in the content of the file there is no item that leads to the particular supplier (some ID or something). Now I first want to import all the data to a staging table from where I furthermore edit the data to other tables. (this means that there is gonna be records from multiple suppliers in the same table). How is (or is it) possible to create a unique ID per suppliers while importing, so that all records get a extra column with a supplierID on the fly. That way i can differntiate the suppliers and thus there records. Can I create a extra value (for the supplierID) in a column based on for instance the used filename or a extract from that?
My second question, after the staging table is filled with all the records I want to split the records, one in customers and one in products ( every line in the csv file exists of a customer with a particular product). Yet again it is important to remain the supplierID for the customer as well as for the product.
I hope I wrote my quesitons clear and that someone can point me in the right direction.
Thanks a lot,
Peter
-
Soulfly73 (10/6/2010)
Hello all,First of all I think this is one $%^of a forum, tons of information and I thank you for that
I have a question, I'm importing data from multiple csv files. The layout is for every CSV file the same.
I use a for each file enummerator, and that works fine.
each csv file is from a different supplier, but in the content of the file there is no item that leads to the particular supplier (some ID or something). Now I first want to import all the data to a staging table from where I furthermore edit the data to other tables. (this means that there is gonna be records from multiple suppliers in the same table). How is (or is it) possible to create a unique ID per suppliers while importing, so that all records get a extra column with a supplierID on the fly. That way i can differntiate the suppliers and thus there records. Can I create a extra value (for the supplierID) in a column based on for instance the used filename or a extract from that?
My second question, after the staging table is filled with all the records I want to split the records, one in customers and one in products ( every line in the csv file exists of a customer with a particular product). Yet again it is important to remain the supplierID for the customer as well as for the product.
I hope I wrote my quesitons clear and that someone can point me in the right direction.
Thanks a lot,
Peter
If you can determine what the supplier id should be before you enter the DataFlow then you can assign that supplier id to a variable. In the DataFlow, you can use the Derived Column component to add that value to the data.
Have you considered splitting the data during the import instead of after?
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.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]
-
Hello alvin,
Thank you for your reply.
I would like to use a part of the filename (there is the supplier ID) into the extra column for the import, but I dont know how to achieve this, how can I get the variable (filename) for the paticular dataflow to put into a extra column into the destination. (files are read in a for each file ennumerator).
I have tried to sepparate during import, but than again I don't know the routine for it to establish a reltion between the split records, (customer / product). If you have any tips on how to accomplish that I would love to give that a try.
Thank you again.
Peter
-
Not anyone a hint or tip
?? -
Are you saying that the filename contains information to be able to determine the SupplierID? If so, you can have this go to a (package level) variable, and use it in your inserts.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
hello Wayne,
Thank you for your reply.
I indeed can get the supplierId from the filename, but I cant figure it out to use it as a variable and insert it as a extra column
Thanks,
Peter
-
Soulfly73 (10/7/2010)
hello Wayne,Thank you for your reply.
I indeed can get the supplierId from the filename, but I cant figure it out to use it as a variable and insert it as a extra column
Thanks,
Peter
Add a package level string variable.
In the ForEach Loop container, click on the Collection tab, and specify to return a fully qualified filename.
In the ForEach Loop container, click on the Variable Mappings tab. Specify the new variable, an index of 0.
This variable is now available for use throughout the package.
Add another package level variable. Set it's "EvaluateAsExpression" property to True, and set the Expression to what is necessary to get the SupplierID from the filename.
In the Data Flow Task, add a derived column between your source/destination (if needed).
Specify the column name. For Expression, drag your new SupplierID variable to it.
You can now use this column in your destination.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Hello Wayne,
Thank you for the clear explanation. I will try it immediatly and will let you know the results.
Thanks again!!
Peter
-
Here's what I would do to get the SupplierId into the dataflow.
Use the ForEach loop to assign the full file name and path to a variable. My first task inside the ForEach container would probably be a Script Task in which I would use VB to extract the SupplierId from the variable I just referred to. Assign the SupplierId to an other variable which I'll call SupplierId.
The next task in the ForEach container would probably be my DataFlow. In the DataFlow, probably right after my data source, I would use a Derived Column component to add the value from the SupplierId variable as a new column.
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.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]
-
Hello alvin,
Thank you very much for your reply.
I let you know the results.
Peter
-
Alvin,
Why use a Script Task to build another variable when you can do it from the expression property of the variable?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply