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 BCP from C# code

Aloha to all,
I have thrown together some code that generates a file with a lot of
data (something like 200,000 rows). I would like to load all this data
in a SQL Server table, but my attempt to execute BCP from C# doesn't
want to behave.
I do as follows (more of less stealing everything from
http://dotnetjunkies.com/WebLog/ste...8/19/22566.aspx
):
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.FileName = "bcp";
proc.StartInfo.Arguments = @."DATABASE_NAME.dbo.TABLE_NAME in
c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t','
-Sservername";
proc.EnableRaisingEvents = true;
proc.Start();
string s1 = proc.StandardOutput.ReadLine();
string s2 = proc.StandardOutput.ReadLine();
All I get from this code is:
s1 = "SQLState = 28000, NativeError = 18456";
s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed
for user 'username'."
I have off course checked the username/password a thousand times and it
is correct. If I copy my Arguments string to the command prompt, I can
execute BCP without any problem
Thank you and roger over, MadsYou could try creating a batch file and then run that. It seems like it's
having trouble putting the arguments in correctly. I don't have the time
right now to try compiling and running your code... but I'm sure you could
easily throw the whole line into a file and running it.
Security-wise though, I'd consider using a trusted connection and
impersonating a particular user.
Unfortunately my timezone means I'm about to go offline for a while, so I
can't post a follow-up. Someone else will though I'm sure (and they'll
probably tell you to ignore me!)
Rob
"Mads.phi@.gmail.com" wrote:

> Aloha to all,
> I have thrown together some code that generates a file with a lot of
> data (something like 200,000 rows). I would like to load all this data
> in a SQL Server table, but my attempt to execute BCP from C# doesn't
> want to behave.
> I do as follows (more of less stealing everything from
> http://dotnetjunkies.com/WebLog/ste...8/19/22566.aspx
> ):
> System.Diagnostics.Process proc = new System.Diagnostics.Process();
> proc.EnableRaisingEvents = false;
> proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
> proc.StartInfo.UseShellExecute = false;
> proc.StartInfo.RedirectStandardOutput = true;
> proc.StartInfo.RedirectStandardError = true;
> proc.StartInfo.FileName = "bcp";
> proc.StartInfo.Arguments = @."DATABASE_NAME.dbo.TABLE_NAME in
> c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t','
> -Sservername";
> proc.EnableRaisingEvents = true;
> proc.Start();
> string s1 = proc.StandardOutput.ReadLine();
> string s2 = proc.StandardOutput.ReadLine();
> All I get from this code is:
> s1 = "SQLState = 28000, NativeError = 18456";
> s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed
> for user 'username'."
> I have off course checked the username/password a thousand times and it
> is correct. If I copy my Arguments string to the command prompt, I can
> execute BCP without any problem
> Thank you and roger over, Mads
>|||Excellent! That fixed my problems, so the only thing left is that I'm
ashamed of not having thought of a .bat file myself.
Thank you, Mads|||About the C# code, in general I'd change
proc.StandardOutput.ReadLine();
to
proc.StandardOutput.ReadToEnd();
to grab all the error message for troubleshooting purposes. Of course, this
doesn't address your particular problem.
Linchi
"Mads.phi@.gmail.com" wrote:

> Excellent! That fixed my problems, so the only thing left is that I'm
> ashamed of not having thought of a .bat file myself.
> Thank you, Mads
>

Execute DTS 2000 Package Task. Mischievous task?

Hi everyone,

For first time I'm testing this task and surprisingly, when I try "Edit Package" option:

1)The DTS host failed to load or save the package properly

2)The selected package cannot be opened

3)Error HRESULT E_FAIL has been returned from a call to a COM component

But after these messages you can see all the tasks but they haven't name!!

It seem as if RCW mechanism has failed between managed and unmanaged coded-partially.

I don't dare to follow doing more stuff, I don't know if that package is well-loaded or not from there. ??

Any guidance or idea about this?

Jamie? Darren?|||Do you mean that you are trying to open the DTS package from Management Studio via the Management->Legacy folder?

If so, I had the same problem opening DTS packages even though I had the SQL2005_DTS.msi components installed.

When you open the package, you will get the 3 error messages and then the DTS Designer window will display the DTS package's tasks without names or correct icons. I've found that saving the opened package under a new DTS package : and then opening the new package will remove the error messages. You will be able to open the tasks and modify them, I still have to test if the new saved package works correctly. I will give you an update on this soon.|||

Enric,

This is already answered here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124252&SiteID=1. We have to try this fix: http://support.microsoft.com/kb/917406/en-us

In my case, it's an x64 machine, the above fix doesnot work.. Anybody who has fixed this on x64 ?

Thanks

Subhash Subramanyam

|||

Hi Friends,

I got it working on the Method 2 posted here, but I wonder if it is a must to Install Enterprise Manager Client tools for an X64 to get rid of these errors.

Thanks

Subhash Subramanyam