I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?
I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegr oups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegr oups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David
|||Any recomended reading?
|||?
|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegr oups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Serv.../dp/0672327813
Professional SQL Server 2005 Integration Services
http://www.amazon.com/Professional-S...dp/0764584359/
David
2012年3月27日星期二
BCP vs SSIS
I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David|||Any recomended reading?|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegroups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Server-2005-Integration-Services/dp/0672327813
Professional SQL Server 2005 Integration Services
http://www.amazon.com/Professional-Server-Integration-Services-Programmer/dp/0764584359/
David
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David|||Any recomended reading?|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegroups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Server-2005-Integration-Services/dp/0672327813
Professional SQL Server 2005 Integration Services
http://www.amazon.com/Professional-Server-Integration-Services-Programmer/dp/0764584359/
David
BCP vs SSIS
I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David|||Any recomended reading?|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegroups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Ser...5
84359/
David
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David|||Any recomended reading?|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegroups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Ser...5
84359/
David
2012年3月22日星期四
BCP Question.
Am using bcp to upload data files to sql server. The data file contains some
duplicate values. So I used something like ">bcp pubs.dbo.stores in
"stores.txt" -m 50..." assuming, the bcp stops only after encountering more
than 50 duplicate. But BCP stops whenever it encounters the first duplicate
value!!
How to continue with BCP, ignoring the duplicate values?
Thanks.
I think it would be best to bcp into a staging table and then do an insert
with a not in subquery keying off the pk.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Viga" <Viga@.discussions.microsoft.com> wrote in message
news:89E94705-F691-4DCD-8E9C-BAEEDDD5C722@.microsoft.com...
> Am using bcp to upload data files to sql server. The data file contains
some
> duplicate values. So I used something like ">bcp pubs.dbo.stores in
> "stores.txt" -m 50..." assuming, the bcp stops only after encountering
more
> than 50 duplicate. But BCP stops whenever it encounters the first
duplicate
> value!!
> How to continue with BCP, ignoring the duplicate values?
> Thanks.
duplicate values. So I used something like ">bcp pubs.dbo.stores in
"stores.txt" -m 50..." assuming, the bcp stops only after encountering more
than 50 duplicate. But BCP stops whenever it encounters the first duplicate
value!!
How to continue with BCP, ignoring the duplicate values?
Thanks.
I think it would be best to bcp into a staging table and then do an insert
with a not in subquery keying off the pk.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Viga" <Viga@.discussions.microsoft.com> wrote in message
news:89E94705-F691-4DCD-8E9C-BAEEDDD5C722@.microsoft.com...
> Am using bcp to upload data files to sql server. The data file contains
some
> duplicate values. So I used something like ">bcp pubs.dbo.stores in
> "stores.txt" -m 50..." assuming, the bcp stops only after encountering
more
> than 50 duplicate. But BCP stops whenever it encounters the first
duplicate
> value!!
> How to continue with BCP, ignoring the duplicate values?
> Thanks.
订阅：
博文 (Atom)