1

I have a package which will export data from a table into flat file destination. In another server the exported text file will import into a table. What actually my problem is , the package should export and import NULL as NULL and empty string as empty string.

I know we have a option in flat file source " Retain null values from the source as null values in the data flow". But it will change all the empty strings into null values.

Can anyone please suggest me to achieve this in better way.

Maximus
  • 792
  • 9
  • 19
  • Check with the length of the column as `Len(LTRIM(Column))` . If it's `0` then assign `null` .Use it in a derived column after `Flat File Source` – praveen Mar 27 '13 at 11:50
  • @praveen: I don't want to replace empty string with null. I have to keep null as null and empty as empty. – Maximus Mar 27 '13 at 11:52
  • If that is the case then don't select the property `To retain null values in the source` in `Flat file connection` . So if you have `NULL` or `` flat file will preocess the same values downstream – praveen Mar 27 '13 at 12:06
  • the problem is, while exporting null values into flatfile destination all the nulls are changed into empty string. we have only 2 options 1. import all the empty and null as empty or 2. import all empty and null as null. – Maximus Mar 27 '13 at 12:14

2 Answers2

2

BCP could be a solution here. Run BCP to export the source table to a file, and then run BCP to import the file into the target table. The call to BCP can be via xp_cmdshell, so it can be run as Execute SQL Task. It is interesting to note that BCP outputs empty string as null and null as empty string. But if we use BCP for both export and import, the values will be mapped correctly.

Jian Fu
  • 519
  • 3
  • 3
1

I would export "true" nulls as a distinct text, for example <null>, and then replace them back as nulls when importing.

AdamL
  • 12,421
  • 5
  • 50
  • 74
  • Can you also please tell me which task will you use to replace null with distinct values in every column. My table has more than hundred column. – Maximus Mar 27 '13 at 11:45