How to use the multiple file connection and multiple flatfile connection?

Hi all,

I know that the File connection can be used in the File system task and Flatfile connection can be used in Flat File source or destination, but I don't know which Task or Source/Destination can use the Multiple File connection and Multiple Flatfile connection. Thanks

you have to have as many connection as the file

|||

The tasks/components that use the single version of connections should be able to use the "multi" ones if you run them in loops.

Thanks.

How to use SQLConnection in Script Task ?

Dear all,

I hava a Script Task to log an error if it occurs. I want to log the error into a table. I used sqlconnection object and sqlcommand object. This is my script inside the task :

Public Sub Main()
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim id As String = Dts.Variables("varUnitID").Value.ToString()
Dim file As String = Dts.Variables("varFile").Value.ToString()
Dim sqlIns As String
Try
conn = New SqlConnection(Dts.Variables("varConnStr").Value.ToString())
conn.Open()
sqlIns = "insert into APP_LOGFAILED values" + _
"(" + id + ",TARGET," + file + ", getdate())"
cmd = New SqlCommand(sqlIns, conn)
cmd.ExecuteNonQuery()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
conn.Close()
conn = Nothing
End Sub

I thought my script was correct, but when I run the package there's an error, saying like this :
'object reference not set to an instance of an object'.
Where's the problem anyway ?
Thanks in advance,

Best regards,

Hery

Hi, though the code seems free of errors other than - varConnStr not declared

Please check the following things

1) Try Individually Running the Script Task, if it runs successfully, Package might have faied due to other reasons.

2) Else There might be some problem establishing connection in the Script, please add a messagebox just below conn.open

3) You might also add one more messagebox just below cmd.ExecuteNonQuery() to check or see the table if the new row has been inserted.

Many Thanks

Subhash Subramanyam

|||Hi Subhash,
thanks for the reply...
I've just found out the error, there's a silly mistake that I made by myself, in previous script I didn't put the Dts.ExecutionValue.ToString(), actually I did :-(
When I remove that line, the script worked successfully...sorry for this silly question..

Public Sub Main()
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim id As String = Dts.Variables("varUnitID").Value.ToString()
Dim file As String = Dts.Variables("varFile").Value.ToString()
Dim sqlIns As String
Try
conn = New SqlConnection(Dts.Variables("varConnStr").Value.ToString())
conn.Open()
sqlIns = "insert into APP_LOGFAILED values" + _
"(" + id + ",TARGET," + file + ";" + Dts.ExecutionValue.ToString() + ", getdate())"
cmd = New SqlCommand(sqlIns, conn)
cmd.ExecuteNonQuery()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
conn.Close()
conn = Nothing
End Sub

Best regards,

Hery
|||

Hi,

Please mark your thread as answered.

Thanks

Subhash Subramanyam

How To Use Message Queue Task In Integration Services

How To Use Message Queue Task In Integration Services

VSB wrote:

How To Use Message Queue Task In Integration Services

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

How to use For Each loop to Read one record and insert the same into destination

Hi,

My procedure to implement a task is like this

I will be using execute SQL task to fetch the records from source,after this wanna use For each loop to access each record one at a time,perform some trnsformations and insert that record into destination.

Help me in accessing the data stored in the Variable(SQL task) in Dataflow task of foreach loop.

Why do you need to do this one record at a time - is the transformation different for each record ?

Can you explain the transformations you need to do?

|||

In the Exec SQL task Editor:

1. General Tab - Result should be of type Full Resultset.

2. ResultSet tab - Set Result Name =0 , Variable Name= your variable of type object (you can create a new variable from the Variable Name dropdown itself, or in the main Variables Pane). Variable type =Object.

http://technet.microsoft.com/en-us/library/ms141689.aspx has details.

3. Add a For Each Loop Container.

4. In the forEach Loop editor, Collection Tab, Set Enumerator=For Each ADO Enumerator

5. In the Variable Mapping, map your column variables to index 0,1,2 (assuming you have three columns in your resultset). You can create your own variables for each column. For each iteration i.e. for each row of the resultset, these variables will hold the value of the respective columns for the current row.

6. Perform your transformations

7. Use a OLEDB Command destination to insert/update your row to the table.

HTH

Kar

|||The requriment from is in that way,I have proposed for a bulk process.But m not sure if i will get the approval.

|||

The index value to be set are in tenh order of retreival from source.

How to access those variables inTansformations can u be specific using Derived column.

|||

You'll get much better performance if you use an OLE DB Source inside a data flow, rather than iterating through a resultset with a For Each. You might want to revisit your design, or post more specifics so we can help you a little more.

|||

Hi,

I will be having a SQL query to retreive data from source,incorporated in Execute SQL task.

Result will be stored in a Result Set.

After this inside a ForEach loop i have to access single record contained in the result set apply some transformations and send the data into destination.

So my requirement is to process one row of data at a time and whole data processing must take place in a loop.

My problem is,How do i process this data in Data Flow task inside For Each loop.

|||

Hi,

You can achive this by this method(i have tried it out)

Use source query and populate the Recordset destination(variable must be of type object)

For each loop(enumerte through adonet)

Based on number of columns declare those many variables and access them in a script componet(source)

and output those variables as columns,

Do the transformations and insert into destination finally.

Any queries post it here.I will help u out.

|||Sorry, but I'm still not understanding why you have to loop through the recordset rather than using an OLEDB Source. However, if it works for you, stick with it.

how to use CASE or IF in SSIS

All,

what's data flow task works with CASE to IF ?

CASE WHEN .ACTION = 'TER' THEN COL 1 ELSE COL 1 end

Thanks

The Derived Column transformation allows you to do this. This is the operator that you're interested in: http://msdn2.microsoft.com/en-us/library/ms141680.aspx

-Jamie

How to use already created Dimensions in SSAS project in new Cube

I have an task in which I have to use already created Analysis Services project for building up new cube. now for building up new cube i need to use some of the dimensions that are already in place. and some i have to create.. Till Data Source View I have almost done. But while cube designing from Data Source View I am facing problem. I am not able to use already present dimensions.

I am using wizard to construct CUBE. now what happens when i create the cube using wizard it automatically creates already present dimensions under new name by appending 1 or 2 etc. I tried the other way by unchecking the already present dimensions but this generates other problem after finishing this way i couldn't see relationships presnt in fact and dimension table as expected by me.

I am new to Analysis Services being my first project i am unable to come out of situation. Somebody has told me by constructing CUBE by not using wizard instead doing it manually will sort out the things... Can anybody provide me any link in which there is explanation how to design cube manually OR solution to my problem

Please mail me at mandipjutla@.hotmail.com

Thanks
Mandip

Hi Mandip,

If you have the dimension already defined in the project all you will need to do is add them to the cube by taking the following steps.

1. Edit the cube that you want to add the dimension to. This OK even if you have created the cube via the wizard.

2. Remove the existing dimension created by the wizard from the cube, by right clicking and choosing delete.

3. Add in the existing dimension that you want by right clicking and choose add.

4. Even if it puts a different name, you can always rename it.

I hope this helps,

David Botzenhart

|||

Hi David,

Thanks for the help.

I have applied the same process as explained by you. But still i can't see the relationship between fact and newly added dimension on Cube's Data Source View.

I have also set up the relation betwwen fact and dimension by going into Dimension Usage tab.

Thanks,

Mandip

How to use a Variable from the For Loop Container as a input parameter to a SP

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

MA

MA2005 wrote:

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

MA

The ForEach loop allows you to store the currently iterated values from the collection that you are iterating over in variables. You can then use those variables in any way that you would normally use variables in a parameterised Execute SQL Task.

I haven't gone into much detail here because I don't want to talk about stuff you already understand. Tell me what isn't clear about what I have said and I'll be glad to fill you in.

-Jamie

How to use a script to break or continue a For Each loop task?

Okay, I have a For each loop container that has an enumeration of folder locations. I run a script to filter the files in those folders and prepare them to the next task to be copy. I want the script to "continue" the loop if none of the files meet the criteria. Can someone point me into the right direction? I need some way to advance through the loop when this happens.

Thanks!

Not quite understanding your requirement but I think conditional precedence constraints will help you here: http://www.sqlis.com/default.aspx?306

-Jamie