Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS Login Issue/Error Help

Author  Topic 

tooba
Posting Yak Master

224 Posts

Posted - 2014-11-14 : 12:56:52
Hi,

I need help. I created very simple SSIS Package (read data from csv file and insert into sql table. Now I want to use or execute this .dtsx package from web services in C# Code. Everytime user try to run having login issue problem. I tried two things
1) Change OLE DB DESTINATION User/P.W to (USE WINDOW AUTHENTICATION)
2) Change OLE DB DESTINATION user/P.w to (User Name and P.W) and save this user name and p.w to Config file.

In both cases i m having problem.

Two question here.

1) I am moving my .dtsx package from one drive to other drive where user can easily access. Only one .dtsx package not a whole solution + .config file. It will be a problem or not?

2) Do you think it will solve my problem if, I use two variables 1) @username 2)@Password for OLE DB DESTINATION and pass the value from C# CODE through web services?

Please advise, its urgent.

Thank You.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-11-18 : 09:37:45
1) I am moving my .dtsx package from one drive to other drive where user can easily access. Only one .dtsx package not a whole solution + .config file. It will be a problem or not?

Moving p[ackages is not good practice, you will need to update the XML file for that and also you may have permission issues on the shared drive hence the connection issue. You should create a new project on the shared drive and add the packages from the original project via the right click on package menu, add package from exisitng...

We are the creators of our own reality!
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-11-18 : 10:23:43
Thanks for your reply. Quick question.
If we have one SSIS Package in two different location and If I wanna modify package, I have to make same changes twice... (do you think its a best approach?) Just to try to understand If I only move .dtsx package to network drive not a solution, it will give permission issues ? I created config file to store OLE DB User/P.W information.
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-11-18 : 10:40:51
How do you authenticate windows authentication and SQL over the web for domain logons would be an issue and would probably require a VPN connection, you could use static username password but security wise storing it could be an issue. Are you coming in from web internally or externally?

We are the creators of our own reality!
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-11-18 : 11:12:33
Internally (through C# code I am calling .dtsx package). I am storing log on info in config file. To be honest I never try it before. This is first time I am trying. Any advise?
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-11-18 : 11:31:52
Internally is better but if you are trying to process thru web you might still be prompted for logon, you could pass the SQL logon to process in a config file but processing the package if you get past Im nnot sure if it will work, whats the main reason to execute via a web service rather than using a SQL Server job.

This may help:

http://msdn.microsoft.com/en-us/library/ms403355.aspx#service

We are the creators of our own reality!
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-11-19 : 16:54:18
Its work for me. I save OLE DB user and p.w in Config File. Works fine for me.

Thanks!
Go to Top of Page
   

- Advertisement -