an interesting bug for SSIS

I have a very simple SSIS package that imports data from ODBC source. There are two "Time" columns in source, but When this table is created in SQL2005, it give me "bigint"!!!

How can this happen?

I tried to import data to SQL2000, those two columns are created as "smalldatetime" properply.

Thanks.

Please provide more details about the package and the ODBCsource. When you say 'Time' does that mean the date is no part it?|||Thanks.
Right. "Time" means date is not part of it.

My package detail:
ODBC source: it is D3 data base. I am using .NET Providers\Odbc Data Provider to connect to the data source.

destination: SQL2005.

Actually, all "Time" field is imported as "bigint"

Cheers.|||I have no idea what a D3 database is; in my case I had to deal with an old PErvasive DB that had a similar case. It had separate columns/data type for the date and the time. I solved it by within the SQL query I use to extract the data. There was a native sql function that allowed me to 'concatenate' the date and time columns into a single column; then the data reader component could see it as a dateTime data type. Conclussion: try to resolve it right on the query.|||

Thanks.

I don't want to "concatenate" the date and time column and that table doesn't has date column at all!

The problem is table created by that package is with wrong data type. But DTS package in SQL 2000 created table properly. This is not about setting the query to select the data.

Can you show me how the query looks like, please?

|||

Qiuwei wrote:

Thanks.
Right. "Time" means date is not part of it.

My package detail:
ODBC source: it is D3 data base. I am using .NET Providers\Odbc Data Provider to connect to the data source.

destination: SQL2005.

Actually, all "Time" field is imported as "bigint"

Cheers.

This is most likely the provider stack. Are you using a different provider to what you used in DTS?

-Jamie

|||In my SQL2000 DTS package, I use "Other(ODBC Data Source)"

Thanks.|||

Which ODBC provider?

It could be that the dotnet provider for ODBC doesn't play very well with whatever ODBC provider you have. You may want to look into getting a different ODBC driver.

-Jamie

|||

Thanks.

Sorry for the late response (toob busy these days).

To get around this problem, we make the source data (time) as varchar type and imported as nvarchar.

Question is that why SQL server doesn't have "Time" data type which is quite useful?

Cheers.

|||

Qiuwei wrote:

Question is that why SQL server doesn't have "Time" data type which is quite useful?

many many people ask the same question. Which is why it (plus some other new things around dates and times) will be coming to a version of SQL Server near you very soon.

-Jamie

|||

That will be great!

Thanks.

Alternative to OSQL?

SQL2K SP4
Howdy all. I have a daily process that gets a .txt file full of Inserts/
Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
any alternatives to this from within SQL Server?
TIA, ChrisRHi Chris
You could write your own application to parse the file and run each command,
a different alternative would be to load the data into a table and use the
EXEC command to run them. As your statements are in a file, there is a
security risk. If you just provided a data file and loaded it into a staging
table you could then insert/update existing data quite easily on-mass withou
t
having to insert/update each row individually. You could use DTS, BULK INSER
T
or BCP to quickly load the data into a staging table.
John
"ChrisR" wrote:

> SQL2K SP4
> Howdy all. I have a daily process that gets a .txt file full of Inserts/
> Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
> Proc that calls a .bat which contains OSQL and runs the .txt file. Are the
re
> any alternatives to this from within SQL Server?
> TIA, ChrisR
>
>|||I appreciare your ideas, but Im confused.

> You could write your own application to parse the file and run each
command
I really need to do this from within SQL Server.

> If you just provided a data file and loaded it into a staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
How would this be any better than what I have? I still need to get it from
the file into a table. The way Im reading this, I need to go from file to
table, then from table to table?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> Hi Chris
> You could write your own application to parse the file and run each
command,
> a different alternative would be to load the data into a table and use the
> EXEC command to run them. As your statements are in a file, there is a
> security risk. If you just provided a data file and loaded it into a
staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT[vbcol=seagreen]
> or BCP to quickly load the data into a staging table.
> John
> "ChrisR" wrote:
>
Stored[vbcol=seagreen]
there[vbcol=seagreen]|||Hi Chris
How do you create the file that makes up these SQL Statements?
John
"ChrisR" wrote:

> I appreciare your ideas, but Im confused.
>
> command
> I really need to do this from within SQL Server.
>
> without
> INSERT
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> command,
> staging
> without
> INSERT
> Stored
> there
>
>|||It is made up for me. A mainframe does some stuff, an ETL tool called Tree
House does some stuff, etc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...[vbcol=seagreen]
> Hi Chris
> How do you create the file that makes up these SQL Statements?
> John
> "ChrisR" wrote:
>
from[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
Inserts/[vbcol=seagreen]
Are[vbcol=seagreen]|||Hi
I guess you could get it changed to produce updategrams and use SQLXML, but
it would be far easier and quicker just to dump a datafile and load it
en-mass.
If you want to still with the SQL statements then you could look at loading
this into a table and then using a cursor and execute statement to run them
(see books online for both), this would rely that each statement was less
than 8000 characters and contained no carriage return or line feeds.
John
"ChrisR" wrote:

> It is made up for me. A mainframe does some stuff, an ETL tool called Tree
> House does some stuff, etc.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> from
> to
> the
> Inserts/
> Are
>
>|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%230AAwWdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> I appreciare your ideas, but Im confused.
>
> command
> I really need to do this from within SQL Server.
Why?
In any case, your best bet is probably BULK INSERT.

>
> without
> INSERT
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
Yes and no. You can go from file->table.
What Chris is suggesting is a staging table (presumably w/o indexes) because
this will make the actual load from the file faster.
It's not necessary, but sometimes can improve performance and overall
maintenance.

>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> command,
the[vbcol=seagreen]
> staging
> without
> INSERT
Inserts/[vbcol=seagreen]
> Stored
> there
>

Alternative to OSQL?

SQL2K SP4
Howdy all. I have a daily process that gets a .txt file full of Inserts/
Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
any alternatives to this from within SQL Server?
TIA, ChrisRHi Chris
You could write your own application to parse the file and run each command,
a different alternative would be to load the data into a table and use the
EXEC command to run them. As your statements are in a file, there is a
security risk. If you just provided a data file and loaded it into a staging
table you could then insert/update existing data quite easily on-mass without
having to insert/update each row individually. You could use DTS, BULK INSERT
or BCP to quickly load the data into a staging table.
John
"ChrisR" wrote:
> SQL2K SP4
> Howdy all. I have a daily process that gets a .txt file full of Inserts/
> Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
> Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
> any alternatives to this from within SQL Server?
> TIA, ChrisR
>
>|||I appreciare your ideas, but Im confused.
> You could write your own application to parse the file and run each
command
I really need to do this from within SQL Server.
> If you just provided a data file and loaded it into a staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
How would this be any better than what I have? I still need to get it from
the file into a table. The way Im reading this, I need to go from file to
table, then from table to table?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> Hi Chris
> You could write your own application to parse the file and run each
command,
> a different alternative would be to load the data into a table and use the
> EXEC command to run them. As your statements are in a file, there is a
> security risk. If you just provided a data file and loaded it into a
staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
> John
> "ChrisR" wrote:
> > SQL2K SP4
> >
> > Howdy all. I have a daily process that gets a .txt file full of Inserts/
> > Updates/ Deletes and imports them into a SQL DB every day. I have a
Stored
> > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
there
> > any alternatives to this from within SQL Server?
> >
> > TIA, ChrisR
> >
> >
> >|||Hi Chris
How do you create the file that makes up these SQL Statements?
John
"ChrisR" wrote:
> I appreciare your ideas, but Im confused.
> > You could write your own application to parse the file and run each
> command
> I really need to do this from within SQL Server.
> > If you just provided a data file and loaded it into a staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > Hi Chris
> >
> > You could write your own application to parse the file and run each
> command,
> > a different alternative would be to load the data into a table and use the
> > EXEC command to run them. As your statements are in a file, there is a
> > security risk. If you just provided a data file and loaded it into a
> staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > SQL2K SP4
> > >
> > > Howdy all. I have a daily process that gets a .txt file full of Inserts/
> > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> Stored
> > > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
> there
> > > any alternatives to this from within SQL Server?
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
>
>|||It is made up for me. A mainframe does some stuff, an ETL tool called Tree
House does some stuff, etc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> Hi Chris
> How do you create the file that makes up these SQL Statements?
> John
> "ChrisR" wrote:
> > I appreciare your ideas, but Im confused.
> >
> > > You could write your own application to parse the file and run each
> > command
> >
> > I really need to do this from within SQL Server.
> >
> > > If you just provided a data file and loaded it into a staging
> > > table you could then insert/update existing data quite easily on-mass
> > without
> > > having to insert/update each row individually. You could use DTS, BULK
> > INSERT
> > > or BCP to quickly load the data into a staging table.
> >
> > How would this be any better than what I have? I still need to get it
from
> > the file into a table. The way Im reading this, I need to go from file
to
> > table, then from table to table?
> >
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > > Hi Chris
> > >
> > > You could write your own application to parse the file and run each
> > command,
> > > a different alternative would be to load the data into a table and use
the
> > > EXEC command to run them. As your statements are in a file, there is a
> > > security risk. If you just provided a data file and loaded it into a
> > staging
> > > table you could then insert/update existing data quite easily on-mass
> > without
> > > having to insert/update each row individually. You could use DTS, BULK
> > INSERT
> > > or BCP to quickly load the data into a staging table.
> > >
> > > John
> > >
> > > "ChrisR" wrote:
> > >
> > > > SQL2K SP4
> > > >
> > > > Howdy all. I have a daily process that gets a .txt file full of
Inserts/
> > > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> > Stored
> > > > Proc that calls a .bat which contains OSQL and runs the .txt file.
Are
> > there
> > > > any alternatives to this from within SQL Server?
> > > >
> > > > TIA, ChrisR
> > > >
> > > >
> > > >
> >
> >
> >|||Hi
I guess you could get it changed to produce updategrams and use SQLXML, but
it would be far easier and quicker just to dump a datafile and load it
en-mass.
If you want to still with the SQL statements then you could look at loading
this into a table and then using a cursor and execute statement to run them
(see books online for both), this would rely that each statement was less
than 8000 characters and contained no carriage return or line feeds.
John
"ChrisR" wrote:
> It is made up for me. A mainframe does some stuff, an ETL tool called Tree
> House does some stuff, etc.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> > Hi Chris
> >
> > How do you create the file that makes up these SQL Statements?
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > I appreciare your ideas, but Im confused.
> > >
> > > > You could write your own application to parse the file and run each
> > > command
> > >
> > > I really need to do this from within SQL Server.
> > >
> > > > If you just provided a data file and loaded it into a staging
> > > > table you could then insert/update existing data quite easily on-mass
> > > without
> > > > having to insert/update each row individually. You could use DTS, BULK
> > > INSERT
> > > > or BCP to quickly load the data into a staging table.
> > >
> > > How would this be any better than what I have? I still need to get it
> from
> > > the file into a table. The way Im reading this, I need to go from file
> to
> > > table, then from table to table?
> > >
> > >
> > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > > > Hi Chris
> > > >
> > > > You could write your own application to parse the file and run each
> > > command,
> > > > a different alternative would be to load the data into a table and use
> the
> > > > EXEC command to run them. As your statements are in a file, there is a
> > > > security risk. If you just provided a data file and loaded it into a
> > > staging
> > > > table you could then insert/update existing data quite easily on-mass
> > > without
> > > > having to insert/update each row individually. You could use DTS, BULK
> > > INSERT
> > > > or BCP to quickly load the data into a staging table.
> > > >
> > > > John
> > > >
> > > > "ChrisR" wrote:
> > > >
> > > > > SQL2K SP4
> > > > >
> > > > > Howdy all. I have a daily process that gets a .txt file full of
> Inserts/
> > > > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> > > Stored
> > > > > Proc that calls a .bat which contains OSQL and runs the .txt file.
> Are
> > > there
> > > > > any alternatives to this from within SQL Server?
> > > > >
> > > > > TIA, ChrisR
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%230AAwWdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> I appreciare your ideas, but Im confused.
> > You could write your own application to parse the file and run each
> command
> I really need to do this from within SQL Server.
Why?
In any case, your best bet is probably BULK INSERT.
> > If you just provided a data file and loaded it into a staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
Yes and no. You can go from file->table.
What Chris is suggesting is a staging table (presumably w/o indexes) because
this will make the actual load from the file faster.
It's not necessary, but sometimes can improve performance and overall
maintenance.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > Hi Chris
> >
> > You could write your own application to parse the file and run each
> command,
> > a different alternative would be to load the data into a table and use
the
> > EXEC command to run them. As your statements are in a file, there is a
> > security risk. If you just provided a data file and loaded it into a
> staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > SQL2K SP4
> > >
> > > Howdy all. I have a daily process that gets a .txt file full of
Inserts/
> > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> Stored
> > > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
> there
> > > any alternatives to this from within SQL Server?
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
>

Alternative to Identity Help.

I have a SSIS package that imports an Excel file using Visual Basic 2005 into my SQL 2005 database. All the fields are the same in the DB and xls. The package runs with no problem but I need one of the fields to be autoincrement. I tried to set up the properties of one of my fields "ID" to be an Identity. This didn't seem to work at all. There are about 1300 records in the DB so far with the last "ID" number being 10001415. Before now, the numbers were inputed manually. I want the "ID" to be assigned when the SSIS package imports the xls file.

Any help is very appreciated.

See if this helps:
http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Not really. I opened the SQL Server Management Studio and tried to edit the DTSX package. I couldn't find any of those options you mention in the article. I am not that familiar with SSIS. I just used the wizard to create it.

|||

I think all you have to do is to set a column in the target table to be an Identity (Identity Specification = Yes).

BTW, to edit a ssis package you have to use Business Intelligence Studio.

|||

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column. I tried creating a new column whith the Is Identity set to Yes with the appropriate seed and increment, however, I have records that have been deleted and now the numbering is off from the original format. What I mean is I have records numbered 1, 2, 3, 5, 6, 9, 12, etc in the DB in that order. When I create the new column and set the ID, it is unaware that I have records missing and what I end up with is 1, 2, 3, 4, 5, 6, etc.

I also tried creating a new SSIS package in order to use surrogate ID's and cannot figure out how to implement that within my import from Excel.

Please help!

|||

ISSOA wrote:

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column.

If you want to insert explicit values in a identity colum you have to alter the table:

SET IDENTITY_INSERT <tableName> ON

|||

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

|||

ISSOA wrote:

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

Don't map that column to the destination column. Leave it out of the Import/Export Wizard and you should get the results you desire.

Alternative to Identity Help.

I have a SSIS package that imports an Excel file using Visual Basic 2005 into my SQL 2005 database. All the fields are the same in the DB and xls. The package runs with no problem but I need one of the fields to be autoincrement. I tried to set up the properties of one of my fields "ID" to be an Identity. This didn't seem to work at all. There are about 1300 records in the DB so far with the last "ID" number being 10001415. Before now, the numbers were inputed manually. I want the "ID" to be assigned when the SSIS package imports the xls file.

Any help is very appreciated.

See if this helps:
http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Not really. I opened the SQL Server Management Studio and tried to edit the DTSX package. I couldn't find any of those options you mention in the article. I am not that familiar with SSIS. I just used the wizard to create it.

|||

I think all you have to do is to set a column in the target table to be an Identity (Identity Specification = Yes).

BTW, to edit a ssis package you have to use Business Intelligence Studio.

|||

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column. I tried creating a new column whith the Is Identity set to Yes with the appropriate seed and increment, however, I have records that have been deleted and now the numbering is off from the original format. What I mean is I have records numbered 1, 2, 3, 5, 6, 9, 12, etc in the DB in that order. When I create the new column and set the ID, it is unaware that I have records missing and what I end up with is 1, 2, 3, 4, 5, 6, etc.

I also tried creating a new SSIS package in order to use surrogate ID's and cannot figure out how to implement that within my import from Excel.

Please help!

|||

ISSOA wrote:

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column.

If you want to insert explicit values in a identity colum you have to alter the table:

SET IDENTITY_INSERT <tableName> ON

|||

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

|||

ISSOA wrote:

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

Don't map that column to the destination column. Leave it out of the Import/Export Wizard and you should get the results you desire.