i also encountered the same problem before.

Here's what I did:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=205600#bm206347

I've never used microsoft datalink but I'll be glad to hear more about that and other ways to solve...

ic.

by the way, you mentioned that the problem occurs in the data Transformation Task. (just wondering). Kindly check the destination and source table. does it always include this format "DbName1.TableSource"?

thanks.

actually, you can change DataSource(server) and catalog(DbName) of sql serverConnection.

you can do it in "disconnected edit" --> connections

click on connection name (ex. Microsoft OLE DB Provider for SQL Server) and...

thanks "ray L" thats the easiest way to solve it..

and to all that reply, thanks for the help.

thanks..

but can you give me some samples...

I don't have..

I only have one package and everything is there..

I only used transform data task and execute sql query. Then I used some activeX Scripts in transformation.

I encounter problems...

thanks.. but I already tried doing that, it does not work..

I used a dynamicPropertiesTask and globalVariable..

how am I gonna use an ini files? maybe, this would work..

you could start by creating a stag table..

export your excel sheet into a new table in your database..

then from there you could easily do some query...

instead of using activeXScript why don't you use "execute Sql Task". It would be a lot easier and convient. just paste your query it in..then indicate a workflow(if needed).