ask user to select excel

Dear friends, can anyone please tell me that how at the time when user executes the package i can ask a user to choose the datasource excel sheet and what are the constraints i have to take care of in the integration package desing.

thanks,

If you'd like to ask user something, then, I'm afraid, you shoud write your own client application, cause SQL Server does provide nothing to bild any interfaces.
|||

dear Sergey Gavrilenko,

is there any way that before running the integration service package i can ask the user to enter the file path of excel that he wants to use as the souce file.

if not easy way then please tell me the only possible way, even if i have to use visual studio .net to get the same.

thanks,

AS400/DB2 different column names in different configurations

Hello!

I am creating a package that will be executed against different AS400/DB2 databases. (I use a Foreach Loop). The AS400/DB2 is configured a little bit differently. This results in that the same column can have different name in different AS400/DB2. For example the column nameA4￡ADG, A4$ADG and A4?ADG is the same column.

In the Data Flow I use DataReader. Does anyone know how to get past this issue without creating one package for each column name?

Best regard

Tina

I'm not an AS400 person (or DB2), so I have no idea if this will work. But, it seems like you could use an expression to set the SQL Statement on the DataReader source, and alias the differently named columns to the same name. To set an expression on the data reader, you have to select the data flow task in the control flow, go to properties, and go to expressions.|||

Hello!

Thank you for your help!

But know I have solved it. I use different Data Flow for each set of column names. Depending on the company that is executed I defined the Precedence Constraint to "Expression and Constraint" and use the Expression:

@.[User::varCompanyName]=="CDHU"

In the Data Reader I have to set ValidateExternalMetadata to False

Best regards,

Tina

AS400 password request

Hi all,

I'm loading a working SSIS package in VB.NET and executing it programmatically. As the 'Persist Security Info' is set, it executes fine, presumably because I executed it previously with valid login info. But, when I update the connection info (programmatically) it prompts me for a password at runtime. The connection string will be provided by the user (DB Admin) and this is running as a Windows service, so what I want to do is, in the case of invalid login info, just receive a rejection, that I can respond to in the code. I'm using the IBMDA400 data provider to connect to an iSeries. Ideally, I need a generic solution that will apply to all connection strings and providers, if possible, but provider specific would be fine for now. Hope you can help....

Mazmo

i ran into this using 3 different AS400 connectors. Try making the Connection componments connection string a variable, and setting the connection string there. Fixed it for me, though security freaks would say this is bad. You could always encrypt your config file .|||

I'm pretty sure this won't solve my problem.

Regardless of the 'Persist Security Info' flag and whether it has connected previously, my customers will be creating the SSIS packages for my program to deal with. Wherever the connection string is stored, there will always be cases where it cannot authenticate. This is acceptable, as long as I can respond to the rejection by telling the user to edit their SSIS package and try again, instead of it automatically throwing up the login box.

So, basically, I want to supress the login box.

Any ideas?

|||We used a program called Macro Express that could track dialog boxes (amongst other things). This was our "Automation Process" before we got an open standards DMS. You could read the form title of the CLAE prompt, do a macro to email the person resonsible. Crummy workaround, but you are dealing with a 3rd party driver pop-up. So however else you do this in .net i don't know. Basically you will be screen scraping the environment, which menas you will also have to have the ssis running in a logged in user context, whcih goes over real well with sys admins (I hate it). Otherwise, maybe a windows programmer can shed some light on this using a pre-execute event or something.|||

maybe you could run a pre-emptive sql query against the 400 (such as SELECT 1+1) as a fake first step of you SSIS program. If it fails, send an email to somebody. Of course, it might time out and never trigger your catch clause.

(tried to edit but forums kept throwing errors...hopefully this gets through)

AS400 Data Pull Fails as Job

When I execute the package in Debug mode, it works.

I then import the package into SQL Server 2005 and it is showing under Stored Packages --> MSDB - AS400Package

I can right click and Run Package from there and it works fine.

When I set the package up to run as a job, it fails. What I have noticed is that the Sign On that I use in the package shows that there was in "invalid login" after the job fails.

I hope I have this post in the right place and hope I have left enough information behind to help. If not let know what else I need to post. I have only been using Business Intelligence Development for a couple days and SQL Server 2005 for about a week or so. All of the SQL DTS packages that I have created seem to be working fine with their jobs as well as my stored procedures.

Thanks for any help you can give me.

I have covered how to run your SSIS packages as a Job in the thread below the error means SQL Server Agent does not have the permissions to run the package because when you run it manually it runs in the context of your account when you run it as a Job it runs in the context of the Agent. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1905695&SiteID=1

|||

Thank you for the response. I did some more research off your link and found some more links that will help those undestand the proxy portion of the post.

Create Credential

https://msdn2.microsoft.com/en-us/library/ms189522.aspx

Add Proxy

https://msdn2.microsoft.com/en-us/library/ms188763.aspx

Grant Login to Proxy

https://msdn2.microsoft.com/en-us/library/ms187338.aspx

Grant Subsystem to Proxy

http://msdn2.microsoft.com/en-us/library/ms186760.aspx

A couple more questions

Code Snippet

CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'RettigB',
SECRET = 'sdrlk8$40-dksli87nNN8';

The IDENTITY is supposed to be the account that is executing the Job and Secret is the password for that account?

I will not be able to test this out for a couple days, but there is something I don't understand. How is creating a proxy and logins going to ensure that the password for my AS400 account gets passed to the AS400 system correctly. Judging from the message in Client Access after logging in with the account that the job is sending across the password as either blank or incorrectly.

Thanks for the help and I look forward to trying this out. I hope this works.

|||

The proxy is for SQL Server Agent to use a clone of your account to run the package nothing more anything more is complication where none exist and it works. All the stored procedures are created by Microsoft for you in the links I gave you. The links you found comes with confusions ignore them use the stored procedure from the MSDN links with the instructions in code project is it very simple but it works.

|||

Using the Code Project sample with a few minor changes

Code Snippet

USE master;

CREATE CREDENTIAL Vicksburg400 WITH IDENTITY = 'myDomain\myAccount', secret = 'WindowsLoginPassword';

USE msdb;

EXEC dbo.sp_add_proxy @.proxy_name = 'VBurg400', @.credential_name = 'Vicksburg400' ;

EXEC dbo.sp_grant_login_to_proxy @.login_name = N'djvmsql', @.proxy_name = N'VBurg400';

EXEC dbo.sp_grant_proxy_to_subsystem @.proxy_name = 'VBurg400', @.subsystem_name = N'SSIS package execution';

I get the following the message.

'djvmsql' is a member of sysadmin server role and cannot be granted to or revoked from the proxy. Members of sysadmin server role are allowed to use any proxy.

I then tried to remove the sysadmin role from the account and run just the "sp_grant_login_to_proxy" again but it gave the message "Permission to proxy already granted". Meanwhile, the job is still failing. I am absolutely done for tonight. Thanks for the help so far. I appreciate your time.

|||

My mistake a few things have changed check to verify the proxy account runs then you need to enable xp_cmdshell with the surface area configuration tool, Microsoft finally documented it but also disabled it by default. Another thing is make sure the Agent is running with the correct account not the local systems account in configuration manager. Running DTS/SSIS packages to move data from AS400 with the Agent and xp_cmdshell have worked for more than 8 years, if yours is not running troubleshoot your environment. I would not add the credential, new complications Microsoft added to a very simple task and remember not to define password for your package it add complication this process needs to be very simple. I know it works because this process works in high security companies like banks and Pharmaceutical manufacture, the key is the Agent needs Admin level permissions which you create with the proxy.

http://msdn2.microsoft.com/en-us/library/ms187901.aspx

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

|||

Caddre wrote:

check to verify the proxy account runs

Done

Caddre wrote:

then you need to enable xp_cmdshell with the surface area configuration tool,

Done

Caddre wrote:

Another thing is make sure the Agent is running with the correct account not the local systems account in configuration manager.

Agent is running with our domain administrator account.

Caddre wrote:

I would not add the credential, new complications Microsoft added to a very simple task

It would not allow me to create the proxy without adding a Credential to it.

Caddre wrote:

and remember not to define password for your package it add complication this process needs to be very simple. I know it works because this process works in high security companies like banks and Pharmaceutical manufacture, the key is the Agent needs Admin level permissions which you create with the proxy.

If you are referring to the dtsx package I am not sure how I can execute the package without specifying a password in the connection manager. The Connection Manager Type in the properties reads "ADO.NET: System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral,"

I have changed some items from other post to coincide with what you posted. One example is adding diamondjacksvm\Administrator to the proxy and etc... Here is the log from the failed job.

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
08/05/2007 18:51:00,ImpotHISLogon,Error,0,DJVMWEB01,ImpotHISLogon,(Job outcome),,The job failed. The Job was invoked by Schedule 3 (Every Hour). The last step to run was step 1 (Execute ImportHIS.dtsx).,00:00:01,0,0,,,,0
08/05/2007 18:51:00,ImpotHISLogon,Error,1,DJVMWEB01,ImpotHISLogon,Execute ImportHIS.dtsx,,Executed as user: diamondjacksvm\Administrator. The package execution failed. The step failed.,00:00:01,0,0,,,,0

One thing I still don't understand is how come I am getting Failed Login messages when I login into the AS400 with the account being used to import the data in the package. I run the job 3 times (3 fails) it locks out my AS400 account.

Thanks for your continued patience and help on this issue. If it will help, I am more than willing to send screen shots.

|||

Hi,
Sorry I took so long the first two links below deals with other issues like driver version and permissions because you said the connection to AS400 is closing that is not good. Please check out the DB2 driver link in the article see if you need it.

The last link comes with creating a configuration file if you don't have one because I read in another forum configuration file fixes the problem with IBM Iseries. Hope this helps.

http://msdn2.microsoft.com/en-us/library/bb332055.aspx

http://support.microsoft.com/kb/918760/
http://technet.microsoft.com/en-us/library/ms141747.aspx

|||

Just to clarify, it was the 2nd link that helped me out. What I ended up doing was logging on as administrator to the local server and building the package. I have my personal login already setup as a SQL Server login with rights to run Jobs but it was still failing. I did not change the way the package itself was built. The only difference I can see between the way I was running it before and now is that the 2nd package was built with Administrator. Let me see if I can clarify.

1rst Package - Job Failed

SQL Server Agent running as <domainname>\Administrator

Package Built with <domainname>\mmanuel

Proxy AS400 has both logins above setup as principals

Job run as AS400 Proxy

2nd Package - Job Succeeded

SQL Server Agent running as <domainname>\Administrator

Package Built with <domainname>\Administrator

Proxy AS400 has both logins above setup as principals

Job run as AS400 Proxy

Caddre - Thank you so much for your help, assistance, knowledge and patience. I very much appreciate it and look forward to reading more post from you.

|||I am glad I could help and thanks for posting the final solution it will help others.

ArrayList -> System.Object-Variable

Hi,

I have a custom task to execute a package. This task sets dynamically the values of the child package at runtime before execution of the child.

Everything works fine, as long as Im not trying to use an Object Variable in the child package, trying to fill with an ArrayList. Then, during package validation at runtime, the Properties of the custom task have NULL-Values. I don't know why all Properties got NULL or 0 (int), only adding an ArrayList-Property to the Task-Code.

The ArrayList gets initialized in the constructor of the Task. Values to add in execute(). But the process never gets to the execute()-Method, due to my check in validate() for null Values. So the List is initialized but empty.

Anyone an idea, maybe I'm doing something wrong at all. Anybody experiences with Object-Variables in Packages? A HowTo?

Thanks a lot

Thorsten

When you say you have an System.Object variable, are you referring to an SSIS variable of type Object, or a .NET variable/property of type System.Object?

Also, to get the picture correct. It sounds like this custom task is similar to an Execute Package Task, except that it sets the "values" of the child package at run-time. I assume you're familar with parent package configurations for non-object variables. Anyway, when you say "dynamically sets the values " are you referring literally to properties, as in pkg.SomeAttribute, or variables, as in pkg.Variables...?|||

Hi,

to get some light into the deep darkness I provided. Maybe, man should read over and over and over a post to provide all information :-). Sorry.

I refer to a SSIS Variable of Type Object. The task is similar to a execute package task, except of the UI, which provides a user friendly (non-technics :-)) GUI to set some properties of the task. These properties control the behavior of the package the task executes at runtime.

Yes I'm familiar with package configurations, but they need variables, one can change without the ability to know they link to a special task. Not so with properties, cos you have to explicitit mark the task to change them.

At runtime, I'm setting the variables of the package I wanna execute, by

Variables variables = null;
variableDispenser.LockOneForWrite(name, ref variables);
variables[0].Value = value;
variables.Unlock();

Everything works as expected, as long as I'm not trying to set a SSIS-Object Variable with an ArrayList. Then "NONE" of the vars gets setted.