CODEWITHSUNDEEP
ssis
HarshiRulz
HarshiRulz

Reputation: 15

how to create a SSIS package which creates three text files, using same variables but the textfile is only created when the correct data is found?

There are only 3 files that can be created : "File_1", "File_2" and "File_3". The same variable name is used in each instance (User::FileDirectory) and (User::File_name), but because the actual value of the variable changes, a new file is created.However the files are only created if there is data to go into the file. i.e. if there are no records to populate the file, it will not be created at all. When the files are created, the date the file was created should also be added to the filename. eg: File1_22102011.txt

Ok if the above was a little confusing, the following is how it works,

All the files use the same variable, but it is reset before each file is created.

• So it populates a result set in memory with the first sql selection (ID number, First_Name and Main_Name). It sets the file variable to “File_1”. If there are records in the result set, it creates and writes to this filename.

• Then it creates a new result set with the second selection(Contract No). It sets the variable to "File_2". If there are records in this new result set, a new file will be created from the variable(which now has a new value)

• Finally a third result set is created (Contract_no, ExperianNo, Entity_ID_Number, First_Name, Main_Name), and the file variable is set to "File_3". Again if there are records in the result set, then this file will be created and written to.

I have worked on a few methods to achieve this but they all have failed, So little help will be greatly appreciated.

Upvotes: 0

Views: 2922

Answers (1)

billinkc
billinkc

Reputation: 61269

While what you have works, I think it'd be rather painful to maintain.

I would approach it as 3 sequence containers running in parallel. Each container would have a data flow and two file tasks hanging off it based on success of the parent and the value of row count variable. If the row count variable is 0, delete the file. If it's greater than 0, rename it to File_n

As you can see, I have a container for the first file. The data flow creates an output a.txt file. Based on the value of the variable @RowCount1, it will either delete the empty file or rename it to File_1.

Control flow pattern

Each data flow would look like a source query, a row count transformation and a file destination with a temporary name (a.txt, b.txt, c.txt). As a file is always created, even if it's empty, we will need to delete or rename it afterwards which will be accomplished based on the file operation tasks.

In my opinion, this approach will be cleaner as it will allow you to test and debug each item in a cleaner manner rather than dealing with an in-memory dataset.

Upvotes: 1

Related Questions