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,
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.
Can any one of help me with your valuable suggestions/comments/answers please.
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?
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
Ah, but
why do you want to use the environment variable when there are other easier ways of doing it?
