2

Here's my folder setup.

Folder Structure

Here's the File setup

File Structure

The idea is to traverse through the folders & put FileA contents to Table FileA.dbo on the database ( also FileB,FileC etc). The FileName structure is same throughout all the folders.

I have this ssis package where I parse through the folders with a foreachloop-> dataflow.

ssis1 ssis2

I have checked that the algorithm I've formulated to get the filename is working

REVERSE(Substring(Reverse( @[User::FileName] ),5,LEN( @[User::FileName] ))) == "FileA"

It parses out the .txt extension. Below is the setup of the foreach loop I have. Foreach

To start this whole package I've .txt files inside the folder & I make the foreach container loop subfolders.

startingpoint

Since, I'm not really a SSIS package developer, this is the best I could do with some research. The problem I'm running into is, it seems to work partially.

The snapshots are mockups of the scenario I have and in reality I have over 200 folders with 50 text files each designated to dump the contents to respective named tables.

But the total number of rows I'm seeing after the package successfully executes is extremely low & can't be right. Is there anyway to get a count/list of the number of folders it traversed. Also, am I doing something wrong ?

Ideally, I'd just like to not have to start with .txt files but just have the whole thing go to folders, get the filenames ( which I think I've a working code for) & just dump all the info to the OLEDB Destinations.

Any help, link to resources is much appreciated.

Hadi
  • 36,233
  • 13
  • 65
  • 124
thestralFeather7
  • 529
  • 2
  • 10
  • 28
  • Yes this can be done using only one data flow task with dynamic source and destination. I will provided a detailed answer with screenshots, but you have to wait for a while, because it needs time – Hadi Dec 12 '17 at 18:28
  • 1
    Thanks a lot @Hadi . I'll be waiting for it – thestralFeather7 Dec 12 '17 at 19:02

1 Answers1

1

Solution overview

You can achieving this using one DataFlow Task inside the foreach loop, but the trick is that you have to Read source flat file name and the Destination SQL Table name from variables

Note: Flat files structure must be the same and SQL Tables must have the same structure

Detailed Solution

  1. Right Click on the Control Flow window and click on Variables

enter image description here

  1. Declare 2 SSIS variables:

    • FlatFilename : of type String and assign a default value a random file path (i.e. C:\MockFolder\FileA.txt)
    • 'SQLTablename: of typeString` and assign to the following expression:

This is assuming that all destination tables has the same schema dbo

 "[dbo].[" + REPLACE(RIGHT( @[User::FlatFilename] , FINDSTRING(REVERSE( @[User::FlatFilename]  ) , "\\", 1) - 1),".txt","") + "]"

enter image description here

enter image description here

  1. Add a Foreach Loop Container and a DataFlow Task inside it, click on the DataFlow Task and on the properties Tab, Set the Delay Validation property to True

enter image description here

  1. Double Click on the Foreach Loop container and select the main Directory , and the files filter *.txt also choose the fully qualified retrieve file name option

enter image description here

  1. Go To variable mapping Tab and choose the @[User::FlatFilename] variable

enter image description here

  1. Add 2 Connection manager

    • FlatFileConnection: a Flat File connection manager, and configure it by selecting randomly a File (i.e. C:\MockFolder\FileA.txt)
    • OLEDBConnection: an OLEDB connection manager, and configure it to your Destination SQL Server Database

  2. In The DataFlow Task, add a Flat File Source and an OLEDB Destination, in the OLEDB Destination select Table name from variable option and select @[User::SQLTablename] as the variable name

enter image description here

  1. Map columns between source and Destination

  2. Click on the FlatFileConnection in the connection manager windows, press F4 to show the properties Tab, click on Expressions

enter image description here

  1. Select the Connection String property assign to it the following expression:

    @[user::FlatFilename]

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    I'm just about to design a package according to your answer. But does this thing traverse subfolders though ? Pardon my relatively weak grasp on ssis knowledge but I thought we need to traverse subfolders via the Foreach loop or does this take care of it ? – thestralFeather7 Dec 12 '17 at 19:18
  • 1
    Yes just check the traverse subfolder option in the foreach loop container – Hadi Dec 12 '17 at 19:19
  • @thestralFeather7 i will be waiting for your reply – Hadi Dec 12 '17 at 19:36
  • Sir, It's taking into account the extension of the file in the eval statement. I'm getting [dbo].[fileA.txt], I should really be getting [dbo].[fileA]. Hence, I can't map the columns as it is telling me that it can't find the object in the database – thestralFeather7 Dec 12 '17 at 20:16
  • @thestralFeather7 oh i will fixed it, it is related to the expression used in `SQLTablename` variable. – Hadi Dec 12 '17 at 20:42
  • @thestralFeather7 i fixed it up, use the following expression `"[dbo].[" + REPLACE(RIGHT( @[User::FlatFilename] , FINDSTRING(REVERSE( @[User::FlatFilename] ) , "\\", 1) - 1),".txt","") + "]"` – Hadi Dec 12 '17 at 20:46
  • ok so when I run the package it error-ed out ( warnings ) with maximumerrorcountreached. [SSIS.Pipeline] Error: "OLE DB Destination" failed validation and returned validation status "VS_NEEDSNEWMETADATA". – thestralFeather7 Dec 12 '17 at 20:54
  • `VS_NEEDSNEWMETADATA` is thrown when destination table structure are differents. In my answer i assumed that table structures should be the same. Else you cannot achieve this using SSIS. You may need to build dynamic SQL command – Hadi Dec 12 '17 at 20:57
  • yep it's showing that some column needs to be removed from external columns. Now I'm not sure why that is because there are no discrepancies regarding the files – thestralFeather7 Dec 12 '17 at 21:00
  • @thestralFeather7 you should use one structure for all tables – Hadi Dec 12 '17 at 21:02
  • as in ? don't think I'm following you. As in all the tables should have the same number of columns or something ? – thestralFeather7 Dec 12 '17 at 21:04
  • Yes the columns names and count should be the same. – Hadi Dec 12 '17 at 21:06
  • yep I don't think that should be an issue though – thestralFeather7 Dec 12 '17 at 21:23
  • @thestralFeather7 at the end, even if that couldn't solved your issue, i am happy that you have learned many things about ssis: *Variables, Expressions, Dynamic Sources, dynamic destinations, ...* so this will put you on the right way. And if you find this answer helpful you can upvote it or accept it. Best wishes – Hadi Dec 12 '17 at 21:24
  • 1
    oh it has definitely helped dude. Thanks a ton. Will do. Still learning , thanks! – thestralFeather7 Dec 12 '17 at 21:26
  • 1
    hello friend. If not the server part , is there a way I can all FileA (s) to one folder, all FileB (s) to another etc after traversing through all the folders I made an updated question over here https://stackoverflow.com/questions/47798748/how-to-traverse-multiple-folders-multiple-files-dump-data-to-sql-tables-with – thestralFeather7 Dec 13 '17 at 17:17
  • 1
    In the foreach loop change `*.txt` to `FileA.txt` – Hadi Dec 13 '17 at 17:58