Monday, March 12, 2012

problem when selecting dynamic connection setting

Hello All,

I have a SSIS package, desgined in such a way that, there is a table called connection which hold the connection name i.e similar to my DSN connection name & a ID for each DSN there is a unique ID. Now i want if there is a 10 record in the table & i have only 5 DSN connection then i'll work for 5 & for other 5 it 'll skip the process. my task is running success fully for 1 connection. i have used foeach loop container & 3 varibable for that 2 for holding the table value & 1 for refereing the recordset

thanks

So are you saying that the table holds 10 records but only 5 of them are valid for the particular package that you are currently executing?

Then you need to filter the list of connections that you want to "use". Can you not hardcode that filter per package?

Sorry, I don't really understand the requirement.

-Jamie

|||

take a example

i have a table table1(id,DsnConnectionName,.) table has 20 records. that means there is a information for 20 DSN connection. But my machine has only 5 DSN Connection. That is in that table. i have first i'm collecting these record in a record set then connect that data flow task with foreach loop. i have 2 variable 1 hold the ID & another DSNConnectionName.

in that foreach loop container i have placed a script task which is setting the connection according to the DSN name assign in that variable. but when the dsn connection with that name is not available in my system then i gives error. for that i use try catch but due to this it;'s running & inserting data each time.

|||

Why can you not just create all the required DSNs?

Why would you try andrun something against a DSN that does not exist?

If you're not going to create the DSNs then, like I said before, you need some way of filtering the list of connections that you iterate over.

-Jamie

|||

Thanks for your keen interest in my problem.

I got the solution,

solution is very simple i used try catch mathod & set dts.taskresult = dts.results.failure for error condition

No comments:

Post a Comment