Hi

I am trying to learn SSIS by trail and error and I just need a little push
here
or pointed in the right direction :-)

I have a file dump from a Mainframe DB that I want to put into SQLServer

There a 3 files
1)Family Demographic
2)Family Property
3)Family Automobiles

TABLE1

key1 steve smith 100 Park Street NoWhere Fl xxxxx 222-222-1111


Table2

key1 100 Park Street NoWhere Fl xxxxx

key1 100 Park Street NoWhere MI xxxxx


Table3

key1 1997 Ford F150

key1 2005 Dodge Ram

There is a Key that ties all the related records accross the 3 files
There is a 1 to many relationship between
#1 and #2
#1 and #3

I want to sort the files to ensure I have the keys in order for all 3 tables
Then I want to read the files into a sql db

I am just looking for a suggestion on how to start - I have done some simple
tutorials but it was just reading a simlpe textfile into a single table

Thanks

Hello Newbie:

Do your destination tables have Foreign Key constraints on them to maintain
the referential integrity?

What would happen if you got two records in Table 1 with the same Key value?
Would that ever happen? Or suppose you had a record in Tables 2 or 3 where
the Key value did NOT exist in table 1? (some things to think about)

Here is how I would approach this:
Create a package and add 3 Data Flow componenets to the Control Flow, then
hook them up with the green arrows so they execute one after another (in
series, not in parallel). In the first one, create a Text File source to
Table 1 file and send it to an OLE DB Destination (not SQL Destination)
connected to your SQL database. If you have a clustered index on the key of
SQL table 1, then you don't need to worry about sorting it before you load it.

On your destinations, select "Table or View", not "Table or View, Fast
Load"; then connect the RED output of the destination to a text file
destination. When you set this up, set it to "Redirect Row". This will allow
you to capture any rows that could not be loaded into the SQL table(s).

Do this for each of the tables in the other two Data Flows.

Keep us posted on your progress and let us know its going.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"sippyuconn" wrote:

Hello Newbie:

Do your destination tables have Foreign Key constraints on them to maintain
the referential integrity?

What would happen if you got two records in Table 1 with the same Key value?
Would that ever happen? Or suppose you had a record in Tables 2 or 3 where
the Key value did NOT exist in table 1? (some things to think about)

Here is how I would approach this:
Create a package and add 3 Data Flow componenets to the Control Flow, then
hook them up with the green arrows so they execute one after another (in
series, not in parallel). In the first one, create a Text File source to
Table 1 file and send it to an OLE DB Destination (not SQL Destination)
connected to your SQL database. If you have a clustered index on the key of
SQL table 1, then you don't need to worry about sorting it before you load it.

On your destinations, select "Table or View", not "Table or View, Fast
Load"; then connect the RED output of the destination to a text file
destination. When you set this up, set it to "Redirect Row". This will allow
you to capture any rows that could not be loaded into the SQL table(s).

Do this for each of the tables in the other two Data Flows.

Keep us posted on your progress and let us know its going.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"sippyuconn" wrote:

Hello Newbie:

Do your destination tables have Foreign Key constraints on them to maintain
the referential integrity?

What would happen if you got two records in Table 1 with the same Key value?
Would that ever happen? Or suppose you had a record in Tables 2 or 3 where
the Key value did NOT exist in table 1? (some things to think about)

Here is how I would approach this:
Create a package and add 3 Data Flow componenets to the Control Flow, then
hook them up with the green arrows so they execute one after another (in
series, not in parallel). In the first one, create a Text File source to
Table 1 file and send it to an OLE DB Destination (not SQL Destination)
connected to your SQL database. If you have a clustered index on the key of
SQL table 1, then you don't need to worry about sorting it before you load it.

On your destinations, select "Table or View", not "Table or View, Fast
Load"; then connect the RED output of the destination to a text file
destination. When you set this up, set it to "Redirect Row". This will allow
you to capture any rows that could not be loaded into the SQL table(s).

Do this for each of the tables in the other two Data Flows.

Keep us posted on your progress and let us know its going.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"sippyuconn" wrote:

Hello Newbie:

Do your destination tables have Foreign Key constraints on them to maintain
the referential integrity?

What would happen if you got two records in Table 1 with the same Key value?
Would that ever happen? Or suppose you had a record in Tables 2 or 3 where
the Key value did NOT exist in table 1? (some things to think about)

Here is how I would approach this:
Create a package and add 3 Data Flow componenets to the Control Flow, then
hook them up with the green arrows so they execute one after another (in
series, not in parallel). In the first one, create a Text File source to
Table 1 file and send it to an OLE DB Destination (not SQL Destination)
connected to your SQL database. If you have a clustered index on the key of
SQL table 1, then you don't need to worry about sorting it before you load it.

On your destinations, select "Table or View", not "Table or View, Fast
Load"; then connect the RED output of the destination to a text file
destination. When you set this up, set it to "Redirect Row". This will allow
you to capture any rows that could not be loaded into the SQL table(s).

Do this for each of the tables in the other two Data Flows.

Keep us posted on your progress and let us know its going.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"sippyuconn" wrote:

Hello Newbie:

Do your destination tables have Foreign Key constraints on them to maintain
the referential integrity?

What would happen if you got two records in Table 1 with the same Key value?
Would that ever happen? Or suppose you had a record in Tables 2 or 3 where
the Key value did NOT exist in table 1? (some things to think about)

Here is how I would approach this:
Create a package and add 3 Data Flow componenets to the Control Flow, then
hook them up with the green arrows so they execute one after another (in
series, not in parallel). In the first one, create a Text File source to
Table 1 file and send it to an OLE DB Destination (not SQL Destination)
connected to your SQL database. If you have a clustered index on the key of
SQL table 1, then you don't need to worry about sorting it before you load it.

On your destinations, select "Table or View", not "Table or View, Fast
Load"; then connect the RED output of the destination to a text file
destination. When you set this up, set it to "Redirect Row". This will allow
you to capture any rows that could not be loaded into the SQL table(s).

Do this for each of the tables in the other two Data Flows.

Keep us posted on your progress and let us know its going.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"sippyuconn" wrote:

Hello Newbie:

Do your destination tables have Foreign Key constraints on them to maintain
the referential integrity?

What would happen if you got two records in Table 1 with the same Key value?
Would that ever happen? Or suppose you had a record in Tables 2 or 3 where
the Key value did NOT exist in table 1? (some things to think about)

Here is how I would approach this:
Create a package and add 3 Data Flow componenets to the Control Flow, then
hook them up with the green arrows so they execute one after another (in
series, not in parallel). In the first one, create a Text File source to
Table 1 file and send it to an OLE DB Destination (not SQL Destination)
connected to your SQL database. If you have a clustered index on the key of
SQL table 1, then you don't need to worry about sorting it before you load it.

On your destinations, select "Table or View", not "Table or View, Fast
Load"; then connect the RED output of the destination to a text file
destination. When you set this up, set it to "Redirect Row". This will allow
you to capture any rows that could not be loaded into the SQL table(s).

Do this for each of the tables in the other two Data Flows.

Keep us posted on your progress and let us know its going.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"sippyuconn" wrote:

Hello Newbie:

Do your destination tables have Foreign Key constraints on them to maintain
the referential integrity?

What would happen if you got two records in Table 1 with the same Key value?
Would that ever happen? Or suppose you had a record in Tables 2 or 3 where
the Key value did NOT exist in table 1? (some things to think about)

Here is how I would approach this:
Create a package and add 3 Data Flow componenets to the Control Flow, then
hook them up with the green arrows so they execute one after another (in
series, not in parallel). In the first one, create a Text File source to
Table 1 file and send it to an OLE DB Destination (not SQL Destination)
connected to your SQL database. If you have a clustered index on the key of
SQL table 1, then you don't need to worry about sorting it before you load it.

On your destinations, select "Table or View", not "Table or View, Fast
Load"; then connect the RED output of the destination to a text file
destination. When you set this up, set it to "Redirect Row". This will allow
you to capture any rows that could not be loaded into the SQL table(s).

Do this for each of the tables in the other two Data Flows.

Keep us posted on your progress and let us know its going.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"sippyuconn" wrote:

Hello Newbie:

Do your destination tables have Foreign Key constraints on them to maintain
the referential integrity?

What would happen if you got two records in Table 1 with the same Key value?
Would that ever happen? Or suppose you had a record in Tables 2 or 3 where
the Key value did NOT exist in table 1? (some things to think about)

Here is how I would approach this:
Create a package and add 3 Data Flow componenets to the Control Flow, then
hook them up with the green arrows so they execute one after another (in
series, not in parallel). In the first one, create a Text File source to
Table 1 file and send it to an OLE DB Destination (not SQL Destination)
connected to your SQL database. If you have a clustered index on the key of
SQL table 1, then you don't need to worry about sorting it before you load it.

On your destinations, select "Table or View", not "Table or View, Fast
Load"; then connect the RED output of the destination to a text file
destination. When you set this up, set it to "Redirect Row". This will allow
you to capture any rows that could not be loaded into the SQL table(s).

Do this for each of the tables in the other two Data Flows.

Keep us posted on your progress and let us know its going.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"sippyuconn" wrote:

Hi Todd

Thanks for the help

Couple questions
1)If I had an input file that has to be split to muliple tables - is that
best way to handle this is run the file thru Muliple Data Flow Tasks - 1 for
each table destination??

2)While I am importing into a table if I want to calculate a field on each
record
- say generate a new GUID for each record and put in a column - How would
I do that ???

3)I will have more questions - should I post here ???

Thanks for all your help

"Todd C" wrote: