swethaashwini avatar image
swethaashwini asked

Loading data to different server using SSIS package

hi, 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?? Thanks,
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aRookieBIdev avatar image aRookieBIdev commented ·
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.
1 Like 1 ·
swethaashwini avatar image swethaashwini commented ·
Can any one of help me with your valuable suggestions/comments/answers please.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
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?
0 Likes 0 ·
swethaashwini avatar image swethaashwini commented ·
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
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Ah, but why do you want to use the environment variable when there are other easier ways of doing it?
0 Likes 0 ·
Show more comments

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.