Help on INI file

Hi all,
I'm building a DTS package that needs to take parameters from an INI
file. The SQL statement in the Transform task goes something like this:
select * from customer where division in ('D','I','2','3','C')
I need to pass the division 'D','I','2','3','C' part in because there
might be more divisions we need in the future or change of divisions,
we don't want to modifiy the package every time. So I set it in the INI
file like this and read it into a global variable:
[Customer]
Division = 'D','I','2','3','C'
But SQL doesn't recognize the SQL statement "select * from customer
where division in (?), where ? stands for the global variable. I tried
with setting Division = "'D','I','2','3','C'" in INI file, it still
doesn't work. The only way it works is to pass the 5 values as 5
seperate parameters and thus 5 global variables then the SQL statement
is like this:
select * from customer where division in (?,?,?,?,?)
But this defeats the purpose because I can't add another parameter in
the INI file without having to open the package and make changes.
Has anybody run into this before? Any suggestions is appreciated.
Thanks,http://www.sommarskog.se/arrays-in-sql.html

Simon

Help on global variable for insert statement

I am new to DTS, but really enjoy it and was wondering if someone could help me with the following small vb app.

I am using the following DTS insert statement to insert records into my table. I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls. How can I modify my code to insert those textboxes as well as run through the boxes and then check if they have nulls and NOT insert the ones that has nulls?

My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.

My question is how do I format the following line of code to do what I need?

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"

I think it is something like this, but I am Really not sure and some help would be greatly appretiated:

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"

I am really not sure.

Here is all the code:

Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
'Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.ExecuteSQLTask2
oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1.Description = "Execute SQL Task: undefined"
oCustomTask1.SQLStatement = "Insert into TestTable (Test1, Test2, Test3) " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
oCustomTask1.ConnectionID = 1
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False

goPackage.Tasks.Add(oTask)
oCustomTask1 = Nothing
oTask = Nothing

End Sub

This sentence is confusing: "I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls."

Do you mean that the table fields do not accept NULL values?

If I recall correctly, a textbox cannot contain a NULL value. At the minimum, it contains an 'empty string'.

In your code sniplet, you are inserting the value '1rowst' in the field [Test1], etc. There is no ambiguity about NULL values.

Perhaps I am missing something. Could you please expand upon your request so in order to clear up the confusion?

|||

I am sorry, lack of english.

My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.

My question is how do I format the following line of code do do what I need?

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"

I think it is something like this, but I am Really not sure and some help would be greatly appretiated:

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"

I am really not sure.

Thanks

|||

I'm still confused. Please help me clarify.

You wish to put all the contents of three textboxes into a single field in the table?

"Textbox1, 2, 3 Needs to insert values into the first column"

If that is the situation, why not just have one textbox -not three?

|||

No, sorry Arnie,

Textbox 1,4,7 go in column 1

TextBox 2, 5, 8 Go in column 2

TextBox 3, 6, 9 Go in column 3

I need my application to insert the values into the Database. If there is Nulls in the textbox go to the next record.

I think the code should go something like this:

If Textbox2 = '' then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')"

Elseif Textbox3 = '' then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')"

And

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text "', '" Textbox5.Text "', '" Textbox8.Text"')"

As you can see I am throwing a rock into the grass, because I am not % 100 sure if this is correct.

Thanks for the patience Arnie

|||

If I am understanding you correctly, it appears that you wish to load textbox values into the table depending upon the status of other textbox values.

Your use of the IF...ELSEIF structure may work just fine for your intentions. Something like this might work.

IF Textbox1.Text = '' Then

IF Textbox2.Text = '' Then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" + Textbox3.Text + "', '" + Textbox6.Text + "', '" + Textbox9.Text + "')"

ELSE

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text + "', '" + Textbox5.Text + "', '" + Textbox8.Text + "')"

END IF

ELSE

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" + Textbox1.Text + "', '" + Textbox4.Text + "', '" + Textbox7.Text + "')"

END IF

|||That was exactly what I needed. Thanks Arnie|||Of course you should be careful with this method since this is completely vulnerable to SQL injection attacks.

Just a friendly reminder.|||

Jon,

Thank you very much for that. I was not familiar with that at all. I just want to make sure. This is a big issue with web applications (wich mine is not). Is that correct?

Thanks

|||Yes it is, but it's completely possible from within windows forms applications as well. So it's definitely something to watch out for.

Help on DTS-Openquery timeout on linked server

I have:
- One linked server
- One DTS with an OpenQuery on linked server
I set the Connection Timeout and General Timeout on the DTS Connection to 0
I set the Command Timeout on DTS Step to 0
I set the Connection Timeout and Query Timeout on the Linked server to 0
Always I receive a provider timeout after 10 minutes.
This is the query:
INSERT INTO openquery(history,'select * from
HistoryContact.dbo.AnomalContact')
SELECT Contact.*
FROM Contact INNER JOIN openquery(storico,'select * from
HistoryContact.dbo.HistoryContact) as CS
ON (Contact.ID = CS.ID) WHERE Contact.STATE<>'0'
Anyone know why?
Thank you very much.
RobertoRoberto
Do you have ping to remote/linked servers?
"Roberto Rasto" <rastoroberto@.hotmail.it> wrote in message
news:ds4pga$bpj$1@.nnrp.ngi.it...
>I have:
> - One linked server
> - One DTS with an OpenQuery on linked server
> I set the Connection Timeout and General Timeout on the DTS Connection to
> 0
> I set the Command Timeout on DTS Step to 0
> I set the Connection Timeout and Query Timeout on the Linked server to 0
> Always I receive a provider timeout after 10 minutes.
> This is the query:
> INSERT INTO openquery(history,'select * from
> HistoryContact.dbo.AnomalContact')
> SELECT Contact.*
> FROM Contact INNER JOIN openquery(storico,'select * from
> HistoryContact.dbo.HistoryContact) as CS
> ON (Contact.ID = CS.ID) WHERE Contact.STATE<>'0'
> Anyone know why?
> Thank you very much.
> Roberto
>|||Yes, a small query is executed.
The query below need approximately 30 minutes normally, so I need an
infinity timeout.
Ho can I force this timeout?
Thanks.
Roberto
"Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
news:%23KLCltlKGHA.344@.TK2MSFTNGP11.phx.gbl...
> Roberto
> Do you have ping to remote/linked servers?
>
> "Roberto Rasto" <rastoroberto@.hotmail.it> wrote in message
> news:ds4pga$bpj$1@.nnrp.ngi.it...
>sql

Help needed with this Error message while running a DTS package

Hi

I am trying to import a excel file into a table but when i run it i am getting this error and i am not sure what this eror is

- Copying to [ICCStatements].[dbo].[Sheet1$] (Error)

Messages

Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with input column "AdminShowInKit" (148) on input "Destination Input" (102). The column status returned was: "The value violated the integrity constraints for the column.".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (102)" failed because error code 0xC020907D occurred, and the error row disposition on "input "Destination Input" (102)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - Sheet1$" (89) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Any help will be appreciated.. RegardsKArenThe key error is this:

Error 0xc020901c: Data Flow Task: There was an error with input column "AdminShowInKit" (148) on input "Destination Input" (102). The column status returned was: "The value violated the integrity constraints for the column.".
(SQL Server Import and Export Wizard)

There seems to be an error in the AdminShowInKit column. Possibly a missing value.

Help needed with SSIS and DTS help

Hi Guys,

I'm trying to develop an application and I have been told right here in sql server getting started forum that SSIS or DTS are the best bits.

My old post has these questions:

Code Snippet

I'm making an application. I have 2 different sites running Sql Server 2005 workgroup and sql server express.

I have a 3rd main server and what I would need to do is to get some of the data from both of these sites, merge it and save it on Main server and then generate another file which should be placed in a directory on a network.

I don't have any idea of how I would do that. Its entirely a sql server app.

One person told me its an SSIS and the other one said to investigate replication.

I have checked a bit about both. I want to give it a try.

Where can i find tutorials on both of these. Complete tutorials to develop and design it.

Thanks

Gemma

books on line are a good starting point:

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

|||

Hi Rafael,

I was thinking more in terms apart of books online. I'm not that clever like you guys, you know.

Apart can you tell me if i put Sql Server 2000 as the main server Can i access the Sql Server 2005 and get some data as I tried it and it doesn't work using DTS.

Thanks

Gemma

|||

Gemma wrote:

Apart can you tell me if i put Sql Server 2000 as the main server Can i access the Sql Server 2005 and get some data as I tried it and it doesn't work using DTS.

Can somebody plz reply to this thread? plz. plz. plz.

Thanks

Gemma

|||

This is a quick set of tutorials on SSIS (included as part of Books Online): http://msdn2.microsoft.com/en-us/library/ms169917.aspx

I'd really recommend going through that first, before getting other books.

As a more general reply to your question, SSIS should be able to accomplish what you want to do.

Help needed in SSIS 2005 & dialup connections

In DTS 2000 there was situation where I had to connect source and destination through a dialup connection (56k), where the transferring of data took me ages to finish, so it was not successful.

In SSIS 2005 is there is a solution?

Thank you

Cheers,

Pradeep.

You can use a multicast component, immediately after your source adapter.

One output from the multicast should go through your usual transformations to the destination.

The other output should go to a Raw File destination. The Raw File destination is not transactional, so it will capture the records it received and will not roll them back even if there is a failure.

Donald

Help Me Please!! Im Having Dts Troubles!!

Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End FunctionSome things I noted:

1. Move the Function Main() statement to the top
2. Dim objConn
3. Instantiate objConn and rs1
4. Set the connection string for objConn
5. Open objConn

I did not see anything glaringly wrong with the SQL, but I did not focus on that.

The rewritten code would look like:

Function Main()

Dim rs1, strSQL, objConn

set objConn = CreateObject("ADODB.Connection")
set rs1 = CreateObject("ADODB.RecordSet")

objConn.ConnectionString = {insert your connection string here}
objConn.Open

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext

Loop

Main = DTSTaskExecResult_Success

End Function

HTH,

Hugh Scott

Originally posted by Bigced_21
Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End Function