Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help

Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help

Executing Package From Web Service

Hi,

I'm having another go at attempting to call an SSIS package from a web service. The Web Service is set up on the same server as the sql server and SSIS package deployment. When i attempt to run the pacage from the web service it starts and then inmediately fails. I can see this in both the Integration Service Logs and the event viewer.

If i click on the package itself and run it using the DTExecUI, it runs without a problem. No logs are output by the package when called by the web service.

Can anyone suggest anything that i could to see if i can diagnose where the problem stems from.

Many thanks in advance,

Grant

Are any exceptions being thrown within the WS code?

Are you passing IDTSEvents or an implementation of (DefaultEvenst for example) into the load and execute methods?

Do you get any error events?

You really need to have decent error handling and make good use of the events from those methods to find out what is going on.

Without an error message we are in the dark out here.

|||hi, i had the same problem, The reason for this execution error is that you are using the NT AUTHORITY\NETWORK SERVICE user, this is one who executes the package.

I suggest you to verify the permissions given to this user into the DB tables and also read this articles:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=607891&SiteID=17
http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

regards|||Hi Darren,

You are indeed correct i do need to add error tracking of some description included in the WS, apologies for not having this information at first.

The WS does not trigger any exceptions, i have since added exception handling but nothing was highlighted. It returns the enumerator 1 for "Package Failed".

If possible, I'd like some further help on how to use the IDTSEvents, could you point me to any good URL's or help me yourself.

In the mean time , i have logged on to the PC as the account under which the WS is running. Upon trying to execute the package, it fails and looks as though it may be to do with access denied errors to the file system of a remote machine where files that require processing are stored and processed to. These directories have full control set for the account running the web service and as a result i believe they should be able to get access, but unfortunately don't.

I'll keep plugging away at it, but setting up the IDTSEvents may help if you can provide further info on this subject.

Thanks,

Grant|||Further to my last post. I've logged into the PC where the SSIS package resides with the same account that calls the web service. It seems to definitely be a permissions issue with the file system access. I have mapped a link to the UNC path of the folders to be processed and when i ran the DTExecUI for a second time it ran with no problems.

What i don't understand now is that despite the web service account having full control over the directories on the remote PC it still fails to see them. Any ideas why this is the case?

Cheers,

Grant|||

Handling Package Events Programmatically
(http://msdn2.microsoft.com/es-es/library/ms135967.aspx)

Loading and Running a Local Package Programmatically
(http://msdn2.microsoft.com/en-us/library/ms136090.aspx)

I woudl expect you to get an error event with a similar message to what you have seen running interactively. That is a good test method by the way.

Are you referencing UNC paths or trying to use a mapped network drive from the package normally? Mapped drives are generally a bad idea when used unattended, just use a UNC. Obviously permissions need to be correct, but I cannot suggest anything other than standard Windows permissions troubleshooting.

|||hi Darren,

Thanks for the links, i'll be sure to read through them. I think i have my problem resolved. It seems that i had to share the top level folder (where all sub folders relate to the SSIS files etc) so that it could be seen via the network. I was initially trying to use the C$ default admin share which wouldn't allow access via the web service account.

It seems to be working with the network share. I've also had to set up anonymous access on the web service to stop the constant prompting for user name and password. Is there a better way to secure the web service when it is called from another ASPNET page?

Many thanks for your help,

Grant

Execute SQL task to update variables

Hi there

I'm attempting to update a variable using the Execute SQL task, I've read a lot of posts on this and seems reasonably simple but obviously not. The first time I ran it the variable was updated correctly, I then manually changed the variable value and since then it doesn't work.

I have a task with the following properties;

Resultset: SingleRow

SQLStatement: SELECT MAX(Player_Daily_Data_Pull_Date) as 'PlayerDaily' From Job_Control

On the resultset tab I have the resultsetname = PlayerDailyand the variable I want to update.

The variable has a type of datatime and it's scope is the container that I'm running the sql task within.

Any help would be appreciated.

Derek

What are the symptoms of this not working? Are there errors or warnings?

Donald

|||

Hi Donald

No symptoms or problems, the package executes successfully. Apart from the fact that the variables aren't updated at the end of the package when I look at them in the variable screen.

Derek

|||

Hi Derek,

The Variable Screen will always show the Initial Value set for that variable.

To check whether your program works fine -

1) Change the DateTime variable to 'Package' Scope.
2) Place a breakpoint for the PreExecute Event for the following task
3) Execute the Package and When the program stops for the BreakPoint - Select Debug Option -> Windows -> Locals
4) Scroll through the list and Locate the DateTime Variable.
5) You will find that the DatTime Variable is updated with the value returned by the SQL Task.

Alternatively -

1) Change the DateTime variable to 'Package' Scope.
2) On Success of the SQL Task - Have a Script Task
3) Put that DateTimeVariable in ReadOnlyVariables list in Script Task
4) Have the following lines of code in the Public Sub Main() method in the script task

Windows.Forms.MessageBox.Show(Dts.Variables("<DateTimeVariable>").Value.ToString())
Dts.TaskResult = Dts.Results.Success

5) You will observe that the datetime value returned by the SQL Statement in SQL Task will be poped up in a small popup window.

After the successful execution of the package - if you go to the Variables window - you will observe that it will only show the initial value. The runtime value assigned to the DateTimeVaribale isn't stored permanently and the runtime value will only be available during execution.

Thanks,
Loonysan

|||

Thanks Loonysan

you were right, it was working just couldn't see it.

Cheers,

Derek

Execute Process Task arguments

We are attempting to use SSIS execute process task.

In a cmd.exe session the executable runs with normal arguments:

mycmd < myscript.txt > foo.txt 2>error.txt

We have attempted to several permutations of this without success. We declared variables and assigned them to stdin, stdout, stderr. However, mycmd opens in the SSIS session as if no arguments have been supplied.

Is there some expression that is required to connect the stdin/out/err variables in the Arguments within Execute Process Task. The help/examples are terse.

Redirections using < and > are not arguments, they are special symbols treated by cmd.exe.

To get the same behavior from Execute Process Task, you'll need to read content of myscript.txt into a variable and redirect program input to this variable; redirect output and errors to two other variables, then after program ends save these variables into foo.txt and error.txt.

But the easiest way to solve it is to use the 'cmd.exe' as the task executable, and supply your command (/C mycmd < myscript.txt > foo.txt 2>error.txt) as agruments for cmd.exe (/C added to instruct cmd.exe to executed specified command).|||

Thanks! We somehow missed the /C switch and that solved the problem.

Loading myscript.txt into a variable for use by stdin seems a bit problematic in our situation since it could be several hundred lines long and contain a wide spectrum of characters related to regular expressions that just seem like the perfect opportunity to tip it over.

We may look at the use of variables in the argument and see if we can move the scrum ahead.

|||Hello.

I'm trying to use the "Execute Process" task to compress several ASCII files in a folder in one ZIP archive. The command line for it looks as follows:

c:\programme\7zip\7z.exe a -bd U:\Projekte\TFG\Software\Log_storage\SCD_Demo_isHASH_34.zip SCD_Demo_isHASH_*.log

The result file has size of 2 KB.

When I put this command in the "Arguments" property of the "Execute Process" component (with "/C" switch and cmd.exe as executable), I get an empty ZIP file. What could be a reason for it? Has anyone already experienced such behaviour?

Regards,
Andrey