0

I have to import data from flat file to the SQL server table. The flat file location and the database of the table can differ but the table name is same across all databases. The table can differ slightly in terms of columns, so the flat file too. We have a format file for every flat file which matched to the table in the respective database. I have come across only the BULK Insert Task for this approach but I want to use Data Flow task to achieve this. Is there any way to do this? Many Thanks.

azim
  • 575
  • 4
  • 11

2 Answers2

0

I have few ideas these might help. - Create Flat File and OLEDB Connections with expressions, In expressions you can create few variables like Server Name, Database Name, Flat File source path. - And Keep destination Queries also in variables for different databases and point respective variable at the time of execution based on DB Name. - Lets give a try and let me know if any findings.

We can not do using direct source Transformations. source 1 and source 2.

As above links suggested that we can achieve this from Script Component.

Community
  • 1
  • 1
ram.bi
  • 283
  • 4
  • 15
  • Thanks Ram, the issue is like a table say abc is can have 10 columns in db1 and 12 columns in db2. The flat file will be having the same no of columns for their respective databases. The issue for me is the definition of the file, I want to keep it dynamic and get it from the format files. – azim Jan 16 '14 at 05:48
0

Steps for the solution 1.You can create file definition in a specific table in any one of the database 2.Create connection manager pointing to the database table. 3.Create a variable pointing to the file format type 3.Using presedence constrain you could either choose which format you need also create different methods in creating files you could choose the file type by condition matches presedence constraint.

Chinna
  • 71
  • 1
  • 4