BIDS fail to execute SSIS package

Hello friends,

I am new in SSIS. I have created simple dtsx package using BIDS which is connecting to remote server database and insert some rows into a table from my local flat file system. I would like to say that I am using vista operating system.

I can not run the dtsx package using BIDS as it gaves me connection access failure message.

Actual Error Message::::::

I am trying to laod flat file from local system to Remote Sql Server 2008 in SSIS. I actually regester the targeted Sql server database in my local machine which is 64 bit.

I use OLE DB Destination task to upload data into remote sql server (uning ip) But I have encountered the following error.

Information: 0x4004300A at load data, SSIS.Pipeline: Validation phase is beginning. Error: 0xC0202009 at Temp, Connection manager "": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [1326]. ". Error: 0xC020801C at load data, OLE DB Destination [26]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error: 0xC0047017 at load data, SSIS.Pipeline: component "OLE DB Destination" (26) failed validation and returned error code 0xC020801C. Error: 0xC004700C at load data, SSIS.Pipeline: One or more component failed validation.

If anyone can give me an idea will be highly appreciable.

Thanks in advance. Kausar Ahmed.

more ▼

asked Oct 21 '10 at 11:43 PM in Default

kausar gravatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Make sure that your the SQL Server name you specified exists, also check remote connections are enabled on your sql server.

For enabling remote connection on sql server 2005 - Use surface area configuration manager, and for SQL Server 2008 you can change by the following Goto start menu of SQL Server SQL -> Server Configuration Manager-> expand SQL Server Network Configuration on th left -> Choose your SQL Server instance -> on the right enable TCP/IP, by default only shared memory will be enabled
more ▼

answered Oct 22 '10 at 12:07 AM

Cyborg gravatar image

10.6k 36 39 45

+1 - also needs a restart to take the TCPIP setting change
Oct 22 '10 at 02:28 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

few things i suggest is
Try the test connection in your connection manager.
Check the credentials that is used to run the package and also the credential has access to the server.
try to ping the server from your machine.

more ▼

answered Oct 22 '10 at 02:25 AM

aRookieBIdev gravatar image

2.3k 42 54 58

Check the package encryption level if it is EncryptSensitiveWithPassword or EncryptAllWithPassword use the Decrypt option to provide the password.
Oct 22 '10 at 02:41 AM aRookieBIdev
(comments are locked)
10|1200 characters needed characters left
Your answer
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: Oct 21 '10 at 11:43 PM

Seen: 4364 times

Last Updated: Oct 21 '10 at 11:43 PM