Is this Possible in SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Is this Possible in SSIS

Author

Topic

Sunny155
Starting Member

15 Posts

Posted - 2009-04-02 : 23:13:55

Hi guys i am new to SSIS i was wondering if the follwoing task can be accomplished via ssis-

I hae a text file with records -

PINV1;16092005;053000;EVS;0123456789;0001;0001
PINV2;0000000101;00010
PINV3;1;12345678901234;1010101543426;06/2007;15.00;03462528536718;101
PINV3;2;12345678901235;1010101543427;06/2007;15.00;03462528546719;101
PINV3;3;12345678901236;1010101543428;06/2007;15.00;03462528556720;101
PINV4;0030;E

i want to extract data from only PINV3

than
match the last section of PINV3 - (101) with a another databse column
and if matched extract data from another column of matched record say column productid, also to extract the remainging columns from PINV3 -
- 3rd and 4th column - (12345678901234) & (1010101543426).

than insert it into other table - 1. matched and extracted record from table productid, 3rdd and 4th column from pinv3 and aslo insert the filename of PINV3 data like 12451511858.txt

Than generate a report in text formt-
some static text,
Date :
Time:
File Name:
Number of successful Insertion:
Number of failed Insertion:
Number of Failed Matchings:
Number of Insertion for nProductId:
Number of Insertion for nProductId:
Number of Insertion for nProductId:
Number of Insertion for nProductId:
Number of Insertion for nProductId:
Number of Insertion for nProductId:
Number of Insertion for nProductId:
Number of Insertion for nProductId:
Number of Insertion for nProductId:
End

i have to give product id for every successful match in the report.

Pls advise is it possible via SSIS SQL 2005 ?

Sunny155
Starting Member

15 Posts

Posted - 2009-04-02 : 23:18:22

also i almost forgot i have to do one more check.
IF the records matched with another databas columns where more than one column had the same value for ex . i match 101 from pinv3 with a databse column called productid and if theres multiple columns with match than i have to look in another column for a value say 1 and if theres a value in that particular record's column than i have to proceed as descibed above.

afaa
Starting Member

16 Posts

Posted - 2009-04-03 : 12:14:23

I think it's doable. For the PINV3 filter, you can use a Conditional Split to just take the records with PINV3 only. Then use the Lookup task to lookup in another database column. Just use the last column in the source to join it with whatever table you want and add any condition in case there are duplicates just like a regular sql join.

Sunny155
Starting Member

15 Posts

Posted - 2009-04-06 : 04:03:34

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources