Loading data to different server using SSIS package


I have sql server 2005 as my default instace. i have recently installed sql server 2008 as named instance. I have "employee" table in XYZ(database name) database on ABC(default instance name). i have same table "employee"(exactly same) in XYZ Database on ABC\KATMAI. Now i am trying to load ABC.XYZ.dbo.employee table with data from ABC\KATMAI.XYZ.dbo.employee using SSIS Package. ABC.CONFIG Table contains connectionstring to those databases, 1 for ABC.XYZ and 1 for ABC\KATMAI.XYZ .

SSIS packages contains: 1 Dataflow Component which inturn contains one Oledb source component and one oledb destination component. Connection Managers : ABC.XYZ , ABC\KATMAI.XYZ, ABC.CONFIG

Now i am using Environment variable "ssisconfig" with values Data source = ABC; Initial catalog = 'XYZ' and remaing thing...

Now when i run the package, ABC.XYZ.dbo.employee is not getting populated. What is the reason??

more ▼

asked Jan 25, 2011 at 01:29 PM in Default

swethaashwini gravatar image

223 17 18 20

Can any one of help me with your valuable suggestions/comments/answers please.
Jan 25, 2011 at 08:50 PM swethaashwini

Without knowing more, the possibilities are nearly endless as far as potential reasons go.

Is the package running to completion? Error free? Are you running the package from BIDS, command line, or SQL Server Agent

Are you logging the package execution to a table or file (a good idea to help with troubleshooting)? Are there any helpful messages in the log? Can you share them here?
Jan 25, 2011 at 09:16 PM KenJ

Let me put it this way.

I have data on one server that need to be loaded into another server.(both tables have same definition) and this need to be done by SSIS package which will be run as a job.i want to make use of Environment variable. So Please give me your suggestions
Jan 25, 2011 at 09:52 PM swethaashwini
Ah, but why do you want to use the environment variable when there are other easier ways of doing it?
Jan 25, 2011 at 11:51 PM ThomasRushton ♦
Instead of getting the connection strings from ABC.CONFIG Table can u try using an XML configuration file for the connection strings and then try and see if it works.
Jan 26, 2011 at 09:48 AM aRookieBIdev
(comments are locked)
10|1200 characters needed characters left

0 answers: sort newest
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 25, 2011 at 01:29 PM

Seen: 2738 times

Last Updated: Jan 25, 2011 at 01:41 PM