Problem Reading .CSV file data from reporting services

Hi,

In our application we are using DTS to import data from .csv file. now they came up with a requirement to compare data in .csv is exact match with imported data in the table using a report.

i had hard time to implement this. we tried two options to do so..

1. reading .csv file with custom extensions and rendering it on report (problem is datasource file name was standard, where a i am planning to read it from a table).

2. We have a separate DTS to read data from .csv but when we are accessing it from reproting services i am getting "Multistep OLE DB access error".

Can some one guide me right way to implement my requirement.

Thanks,
-Geeks..

You can use the XML rendering extension to output just the data in the report. then use an Integration Services (formerly DTS) package to compare the input and output data.

Hope that helps,

-Lukasz

Problem Reading .CSV file data from reporting services

Hi,

In our application we are using DTS to import data from .csv file. now they came up with a requirement to compare data in .csv is exact match with imported data in the table using a report.

i had hard time to implement this. we tried two options to do so..

1. reading .csv file with custom extensions and rendering it on report (problem is datasource file name was standard, where a i am planning to read it from a table).

2. We have a separate DTS to read data from .csv but when we are accessing it from reproting services i am getting "Multistep OLE DB access error".

Can some one guide me right way to implement my requirement.

Thanks,
-Geeks..

You can use the XML rendering extension to output just the data in the report. then use an Integration Services (formerly DTS) package to compare the input and output data.

Hope that helps,

-Lukasz

Problem inserting data into onw of my tables

i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2

My Problem:

It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.

Total Specification Requirements:

i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.

Any Help would be graetlty appreciated

Kind Regards

Carel Greaves

Carel,

There is nothing magic in SSIS that helps you with data issues like PK/FK violations. With the few details you are providing about your package I just can think in a few tips:

Know your data. If data is being duplicate by the inserts, you need to know why. Same for the FK. I don't know how you are performing the inserts; but is you are doing it in a data flow; at the very least you could enable error redirection in your OLE Destination component and capture the row(s) that generate the exceptions; so you have a better understanding of the situation. Perhaps the 'check if row exists' logic is not accurate. See this for some approaches: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael

More info on my package.

I have a database called HISDirectory, inside HISDirectory is a Table called HISMemberData

I have two Servers with the same database. One server is situated in Australia and the other in SA.

Everytime a new member is added to HISMemberData in SA, it must take that same value and insert it into the HISMemberData Table in Australia.

the table only has 4 fields. ID, MemberID, HealthID, DateSend.

There is a KEY on MemberID and on HealthID

If i use the

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

VALEUS (123, 123, 123)

then it works fine, but i can't do a:

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

SELECT * FROM tmpTableWithCorrectValues

I have tried that method from the guys link you sent me before but it crashes with the same problem every way that i try.

Even if i could just get a SQL query to help me with the population of this table. I have created a SSIS package to import the table from the other side into a new table and then try to populate the table locally, but i just seem too stupid to get it done.

Please Help

Kind Regards

Carel Greaves

|||

Ypou should be able to solve this using the approach described in the link I gave you. You need to make sure the lookup componnet is properly set up; this is: The join conditions should uniquely identify if the row exists in the target table and the error out put should set to redirect the errors ( a row not found in the lookup is treated as an error); then the error out put are you inserts and the other output are your discard or update rows.

Make sure the PK and FK definitions are identical in both tables.

Problem inserting data into onw of my tables

i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2

My Problem:

It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.

Total Specification Requirements:

i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.

Any Help would be graetlty appreciated

Kind Regards

Carel Greaves

Carel,

There is nothing magic in SSIS that helps you with data issues like PK/FK violations. With the few details you are providing about your package I just can think in a few tips:

Know your data. If data is being duplicate by the inserts, you need to know why. Same for the FK. I don't know how you are performing the inserts; but is you are doing it in a data flow; at the very least you could enable error redirection in your OLE Destination component and capture the row(s) that generate the exceptions; so you have a better understanding of the situation. Perhaps the 'check if row exists' logic is not accurate. See this for some approaches: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael

More info on my package.

I have a database called HISDirectory, inside HISDirectory is a Table called HISMemberData

I have two Servers with the same database. One server is situated in Australia and the other in SA.

Everytime a new member is added to HISMemberData in SA, it must take that same value and insert it into the HISMemberData Table in Australia.

the table only has 4 fields. ID, MemberID, HealthID, DateSend.

There is a KEY on MemberID and on HealthID

If i use the

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

VALEUS (123, 123, 123)

then it works fine, but i can't do a:

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

SELECT * FROM tmpTableWithCorrectValues

I have tried that method from the guys link you sent me before but it crashes with the same problem every way that i try.

Even if i could just get a SQL query to help me with the population of this table. I have created a SSIS package to import the table from the other side into a new table and then try to populate the table locally, but i just seem too stupid to get it done.

Please Help

Kind Regards

Carel Greaves

|||

Ypou should be able to solve this using the approach described in the link I gave you. You need to make sure the lookup componnet is properly set up; this is: The join conditions should uniquely identify if the row exists in the target table and the error out put should set to redirect the errors ( a row not found in the lookup is treated as an error); then the error out put are you inserts and the other output are your discard or update rows.

Make sure the PK and FK definitions are identical in both tables.