running Insert Statements from flat file

[sds814]

A text doc has insert statements that need to be executed. How would I create a SSIS package to run these statements?

This is what I have so far: a Data Flow connected to an Execute SQL Task. The Data Flow has a flat file source (text doc) connected to a script task. The Flat file has only one column which is the insert statement. I've created a variable in the data flow task. In the script task I've assigned the column to the variable so now the insert statement is assigned to the variable. Now what I'd like to do is assign the variable to the execute sql task so the sql task will run the insert statement. I would have this in a for loop so all the insert statements will run.

The problem that I'm coming across is that the scope of the variable that I created in the data flow task isn't available in the package level. Do I have to create a global variable so the variable is also available in the control flow task?

Thanks.

 

[RiverGuy]

I wouldn't even use a DataFlow task. You're so far into the custom programming arena that I would just use a ScriptTask, loop through the lines of the file (using System.IO namepace), and execute OLEDB/SQLClient Commands.

 

[sds814]

Thanks for the reply, RiverGuy.

Do you have any examples of this?

 

[RiverGuy]

No, but off the top of my head, it would look something like the following:

Dim Con As New SqlClient.SqlConnection(SomeConnectionString)
Dim Cmd As New SqlClient.SqlCommand
Cmd.Connection = Con
Cmd.CommandType = CommandType.Text
Con.Open
For Each S As String In System.IO.File.ReadAllLines("SomePath")
  Cmd.CommandText = S
  Cmd.ExecuteNonQuery()
Next

Of course, add exception handling, disposing of objects, closing connections, etc.

 

[sds814]

Much simpler than I thought. Thanks RiverGuy.
I'll keep you posted.

 

[MDXer]

In Regards to Scope it is usually best to Scope your vriables naturally there are exceptions.